multiple for each loops within vba

[pburke11]

I hope someone can help me.
I have a vba script that combines equipment from multiple excel files. The problem I have is trying to compare 2 files for similar worksheet names. If names are similar then I need to add a worksheet in the new workbook after the matching sheeet.

I have attached what I have so far. The first part of the for each sht works great. I tried making it sht2 but that didn't help. What am I missing?
For Each sht In Application.Worksheets
If sht.Name <> "templates" Then
If Range("l23,l115,l207") <> True Then
sht.Range("uio_equip").Copy
newbook.Activate
Set sht = Worksheets.Add(After:=Worksheets(Worksheets.Count))
Range("b2").Select
Selection.PasteSpecial Paste:=xlPasteValues
sht.Name = "uio_" & Range("f2")
newbook.Save
ElseIf Range("l23,l115,l207") = True Then
uioname = Range("f2")
sht.Range("uio_equip").Copy
newbook.Activate
IF SHT.NAME LIKE UIONAME THEN 'this is where I trying to get a 2nd loop of worksheets within the newbook.
wksh.Activate
Set sht = Worksheets.Add(After:=Worksheets(Worksheets.Count))
Range("b2").Select
Selection.PasteSpecial Paste:=xlPasteValues
sht.Name = "uio_" & Range("f2")
newbook.Save
End If
End If
'clears clipboard
Application.CutCopyMode = False
End If
Next sht

Any help would be greatly appreciated.

 

[SkipVought]

Hi,

What is the puropse of this statement and what are the VALUES in these three cells?

   If Range("l23,l115,l207") <> True Then

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pburke11]

Hi Skip,
I am using the cell references to identify if the equipment on that spread sheet is being incorporated into another cabinet or has it's own.

The 1st half works ok. If cells are not true, I copy a range name from the user defined spreadsheet into a work book where I add sheets through each step.

the user defined spreadsheet could have 1 to ?? sheets. If any of these cells are true, I am trying to compare worksheet names with user defined spreadsheet with the new workbook I initiate in the program. the sheet names are actually room numbers/names. If the user defined spreadsheet sheet name is like the new work book, I am trying to add a new sheet to the new work book.

The line with upper case is where I initially put a second for each loop, figuring I could loop through all sheets in new workbook for like type room names.

I hope I explained it well enough for you to see what I am trying to accomplish.
I appreciate your time and response.

Thank you

 

[SkipVought]

Please clearly define what you are trying to do with a second loop. Please define your logic for the entire process, using WbA and wbB.

Loop wsA thru wbA sheets

  if wsA <> "templates" then

     if not something then

        copy from wsA
        addSheet in wbB???
           .Paste into newsheet
           .Name newsheet
           save wbB

     elseif something then

        ????

     end if

  end if
next wsA sheet

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pburke11]

Hi Skip,
Thanks for assisting in this.

For Each sht In Application.Worksheets
'user selected workbook (wbA)
If sht.Name <> "templates" Then
'unused worksheet in user selected workbook
If Range("l23,l115,l207") <> True Then
'if not true then gets added in workbook as
next sheet in wbB
sht.Range("uio_equip").Copy
'range name to copy
newbook.Activate (wbB)
'used to focus on new workbook
previously started
Set sht = Worksheets.Add(After:=Worksheets(Worksheets.Count)) 'adds new worksheet in wbB
Range("b2").Select
Selection.PasteSpecial Paste:=xlPasteValues
'copies range name to cell b2
sht.Name = "uio_" & Range("f2")
'names tab with equip type (UIO) and
room number (cell f2) in wbB
newbook.Save

I added comments to the above code which is everything above elseif statement.

What I am trying to set up is this. If the elseif statement is true then I carry the same commands out as above, but first I need to compare room numbers (worksheet names) in wbA with wbB. With a match focus on wbB, add new worksheet then copy range name as above.

My thought was to loop through the sheets in wbB to find like room numbers as current sheet in wbA.

I may be completely off base with the second portion of the program at this point.

Thanks again for your assistance and time.

 

[xlbo]

For future ref, please post VBA questions in the VBA forum: Forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kjv1611]

This may not give you a specific answer at this point, but have you considered moving this to Access? I mean, if someone is needing it in Excel eventually, you can still shoot it back out to Excel. Generally, if you've got lots of different sheets to compare, which it sounds like, I'd think you'd be better off working within Access...

Then you can (in my opinion) use SQL and recordsets within VBA to do the job much easier than working within Excel.

Of course, getting your data into Access could be another excercise itself if you're wanting to automate that part - depends on the original data.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

We just have some blind logic that you have conceived, that may or may not be correct. Suppose you give us an example, not more than 3 or 4 sheets (with sheet names), of what's in wbA & wbB, that would represent the conditions that you're trying to address. Also include what you expect the final result to be.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}