nesting problem
 

I'm having problems with the nesting. I want to open one file - pull
all of the information from the 45 sheets into a master file then close
the file and move on to the next file where I will pull the information
from the next file and put it into the appropriate sheet in the master file.

The files are listed on one sheet. The accounts are listed on another
sheet. My problem is that I seem to be nesting incorrectly. I can get
the first file to open, it then pulls all of the account information
into my master file and then I want it to close the file and move on to
the next file but instead it sees that the account information is empty
and I get an error.

I am not sure how to get around this. Can anybody help me with this? I
can get the same result using an array for the rollups but then it opens
1 file pulls the information for 1 account and then closes the file and
opens the next file - looping through the files and populating by account.

Below is the code - I know that it is convoluted but I was hoping
somebody could help me with the nasty loop issue so that I could clean
things up.

Thanks in advance for any help that you can give.

Regards,
anita

Sub engTest()

Dim bookList
Dim i

tablerow1 = 1
tablerow = 1
i = 1

Workbooks("New Sales Attempt.xls").Worksheets("summary").Activate
Workbooks("New Sales Attempt.xls").Worksheets("summary").Unprotect
password:="nope"


Cells.Select
Selection.Clear
If theRolluplevel = "ttleuropesale" Then
myCC = Workbooks("New Sales
Attempt.xls").Sheets("ttls").Cells(tablerow, z)




ElseIf theRolluplevel = "ttlasiansale" Then
myCC = Workbooks("New Sales
Attempt.xls").Sheets("ttls").Cells(tablerow, z)




ElseIf theRolluplevel = "ttljapansale" Then
myCC = Workbooks("New Sales
Attempt.xls").Sheets("ttls").Cells(tablerow, z)

ElseIf theRolluplevel = "ttlsalesadmin" Then
myCC = Workbooks("New Sales
Attempt.xls").Sheets("ttls").Cells(tablerow, z)



Else: theRolluplevel = "ttlslseng"
myCC = Workbooks("New Sales
Attempt.xls").Sheets("ttls").Cells(tablerow, z)
End If
Do Until myCC = ""
Workbooks.Open myCC, updatelinks:=False
theSelectedNotePad = Workbooks("New Sales
Attempt.xls").Sheets("accounts").Cells(tablerow1, 1)
Do Until theSelectedNotePad = ""

theSelectedNotePad = Workbooks("New Sales
Attempt.xls").Sheets("accounts").Cells(tablerow1, 1)
Set rng = Workbooks("New Sales
Attempt.xls").Worksheets("Summary").Range("A1")
Set rng = Workbooks("New Sales Attempt.xls") _
..Worksheets("summary").Range("A1")

theRollupLevel1 = theRolluplevel & ".xls"

rng.Parent.Parent.Activate
rng.Parent.Activate
'Resets the workbook
rng.Select
ActiveSheet.Unprotect
ActiveSheet.PageSetup.PrintArea = ""
Application.ScreenUpdating = False

Columns("A:t").Select
Range("U1").Activate
Selection.Clear
Selection.EntireRow.Hidden = False
'initializes the workbook in the array

'Opens the source book in the array

Application.StatusBar = "processing " & myCC & " " & theSelectedNotePad
Workbooks(myCC).Activate

Workbooks(myCC).Activate
Sheets(theSelectedNotePad).Select
ActiveSheet.Unprotect ("nope")
'hardcodes linked cells
Columns("a:b").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
'Sets the range that will be copied to the summary sheet
Set rng1 = Workbooks(myCC).Worksheets(theSelectedNotePad).Ran ge("A1:t59")
rng1.Select
'Copies the information from the source book to the summary file
i = 1
rng1.Copy Destination:=rng((i - 1) * 59 + 1).Offset(0, 1)
'determines the number of rows that will be used in the next part of the
procedure
j = i * 60
k = 0 - j
l = 5 + j
'increments number of workbooks to multiply by number of rows in notepad
'ActiveWorkbook.Close SaveChanges:=False




Workbooks(myNotePadSummary).Sheets(theSelectedNote Pad).Delete
Workbooks("New Sales Attempt.xls").Sheets("Summary").Copy
Befo=Workbooks(myNotePadSummary).Sheets("templa te")
ActiveSheet.DrawingObjects.Select
Selection.Delete


Application.CutCopyMode = False
'selects the first cell in the range that will determine whether the row
should be hidden

Sheets("Summary").Name = theSelectedNotePad
tablerow1 = tablerow1 + 1

Loop

Workbooks(myCC).Close savechanges:=False
tablerow = tablerow + 1
myCC = Workbooks("New Sales
Attempt.xls").Sheets("ttls").Cells(tablerow, z)

Loop
end sub

nesting problem
 

If I understand your problem correctly



In both Do Loops you have the same code
tablerow1 = tablerow1 + 1
It does not appear that you reset this back to row 1 at any time



I have changed your IF statements to Select Case statement .
You also set rng twice. I deleted one of these lines of code.
I was not sure if this should have been included inside your 1st D
loop

You also appear to have an error with
rNg.Parent.Parent.Activate
rNg.Parent.Activate
which I have left in


Also please note that you can combine some of your lines of code int
one action
eg

Columns("a:b").Select
Selection.Copy

can be changed to

Columns("a:b").Copy



Sub engTest()

Dim bookList
Dim i

tablerow1 = 1
tablerow = 1
i = 1

Workbooks("New Sales Attempt.xls").Worksheets("summary").Activate
Workbooks("New Sales Attempt.xls").Worksheets("summary").Unprotec
password:="nope"

Cells.Clear
Select Case theRolluplevel
Case "ttleuropesale"
myCC = Workbooks("New Sale
Attempt.xls").Sheets("ttls").Cells(tablerow, z)
Case "ttlasiansale"
myCC = Workbooks("New Sale
Attempt.xls").Sheets("ttls").Cells(tablerow, z)
Case "ttljapansale"
myCC = Workbooks("New Sale
Attempt.xls").Sheets("ttls").Cells(tablerow, z)
Case "ttlsalesadmin"
myCC = Workbooks("New Sale
Attempt.xls").Sheets("ttls").Cells(tablerow, z)
Case Else
theRolluplevel = "ttlslseng"
myCC = Workbooks("New Sale
Attempt.xls").Sheets("ttls").Cells(tablerow, z)
End Select
Do Until myCC = ""

Workbooks.Open myCC, updatelinks:=False
theSelectedNotePad = Workbooks("Ne
SalesAttempt.xls").Sheets("accounts").Cells(tabler ow1, 1)
Do Until theSelectedNotePad = ""

theSelectedNotePad = Workbooks("New Sale
Attempt.xls").Sheets("accounts").Cells(tablerow1, 1)

Set rng = Workbooks("New Sale
Attempt.xls").Worksheets("summary").Range("A1")
theRollupLevel1 = theRolluplevel & ".xls"

rng.Parent.Parent.Activate
rng.Parent.Activate
'Resets the workbook
rng.Select
ActiveSheet.Unprotect
ActiveSheet.PageSetup.PrintArea = ""
Application.ScreenUpdating = False

Columns("A:t").Select
Range("U1").Activate
Selection.Clear
Selection.EntireRow.Hidden = False
'initializes the workbook in the array

'Opens the source book in the array

Application.StatusBar = "processing " & myCC & " "
theSelectedNotePad
Workbooks(myCC).Activate
Sheets(theSelectedNotePad).Select
ActiveSheet.Unprotect ("nope")
'hardcodes linked cells
Columns("a:b").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:
_
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
'Sets the range that will be copied to the summary sheet
Set rng1
Workbooks(myCC).Worksheets(theSelectedNotePad).Ran ge("A1:t59")
rng1.Select
'Copies the information from the source book to the summary file
i = 1
rng1.Copy Destination:=rng((i - 1) * 59 + 1).Offset(0, 1)
'determines the number of rows that will be used in the next part o
the
procedure
j = i * 60
k = 0 - j
l = 5 + j
'increments number of workbooks to multiply by number of rows i
notepad
'ActiveWorkbook.Close SaveChanges:=False
Workbooks(myNotePadSummary).Sheets(theSelectedNote Pad).Delete
Workbooks("New Sales Attempt.xls").Sheets("Summary").Cop
Befo=Workbooks(myNotePadSummary).Sheets("templa te")
ActiveSheet.DrawingObjects.Select
Selection.Delete

Application.CutCopyMode = False
'selects the first cell in the range that will determine whether th
row should be hidden

Sheets("Summary").Name = theSelectedNotePad
tablerow1 = tablerow1 + 1

Loop

Workbooks(myCC).Close savechanges:=False
tablerow = tablerow + 1
myCC = Workbooks("New SalesAttempt.xls").Sheets("ttls").Cells(tablerow
z)

Loop
End Su

--
Message posted from http://www.ExcelForum.com

nesting problem
 

Thank you Mudraker!

I think that the main culprit was not setting the tablerow1 back to 1.
I just did a trial run and the resetting did get me past the part where
I have been getting hung up.

Also - thank you for your suggestions on cleaning up the code. I'm a
major recycler of code and so I try to get old code to do new things and
I'm always left with vestiges of the past until I can ensure myself that
the newest will work.

Thanks so much for catching the reset! I just couldn't see it.

Best Regards,
Anita

mudraker < wrote:
If I understand your problem correctly



In both Do Loops you have the same code
tablerow1 = tablerow1 + 1
It does not appear that you reset this back to row 1 at any time



I have changed your IF statements to Select Case statement .
You also set rng twice. I deleted one of these lines of code.
I was not sure if this should have been included inside your 1st Do
loop

You also appear to have an error with
rNg.Parent.Parent.Activate
rNg.Parent.Activate
which I have left in


Also please note that you can combine some of your lines of code into
one action
eg

Columns("a:b").Select
Selection.Copy

can be changed to

Columns("a:b").Copy



Sub engTest()

Dim bookList
Dim i

tablerow1 = 1
tablerow = 1
i = 1

Workbooks("New Sales Attempt.xls").Worksheets("summary").Activate
Workbooks("New Sales Attempt.xls").Worksheets("summary").Unprotect
password:="nope"

Cells.Clear
Select Case theRolluplevel
Case "ttleuropesale"
myCC = Workbooks("New Sales
Attempt.xls").Sheets("ttls").Cells(tablerow, z)
Case "ttlasiansale"
myCC = Workbooks("New Sales
Attempt.xls").Sheets("ttls").Cells(tablerow, z)
Case "ttljapansale"
myCC = Workbooks("New Sales
Attempt.xls").Sheets("ttls").Cells(tablerow, z)
Case "ttlsalesadmin"
myCC = Workbooks("New Sales
Attempt.xls").Sheets("ttls").Cells(tablerow, z)
Case Else
theRolluplevel = "ttlslseng"
myCC = Workbooks("New Sales
Attempt.xls").Sheets("ttls").Cells(tablerow, z)
End Select
Do Until myCC = ""

Workbooks.Open myCC, updatelinks:=False
theSelectedNotePad = Workbooks("New
SalesAttempt.xls").Sheets("accounts").Cells(tabler ow1, 1)
Do Until theSelectedNotePad = ""

theSelectedNotePad = Workbooks("New Sales
Attempt.xls").Sheets("accounts").Cells(tablerow1, 1)

Set rng = Workbooks("New Sales
Attempt.xls").Worksheets("summary").Range("A1")
theRollupLevel1 = theRolluplevel & ".xls"

rng.Parent.Parent.Activate
rng.Parent.Activate
'Resets the workbook
rng.Select
ActiveSheet.Unprotect
ActiveSheet.PageSetup.PrintArea = ""
Application.ScreenUpdating = False

Columns("A:t").Select
Range("U1").Activate
Selection.Clear
Selection.EntireRow.Hidden = False
'initializes the workbook in the array

'Opens the source book in the array

Application.StatusBar = "processing " & myCC & " " &
theSelectedNotePad
Workbooks(myCC).Activate
Sheets(theSelectedNotePad).Select
ActiveSheet.Unprotect ("nope")
'hardcodes linked cells
Columns("a:b").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
'Sets the range that will be copied to the summary sheet
Set rng1 =
Workbooks(myCC).Worksheets(theSelectedNotePad).Ran ge("A1:t59")
rng1.Select
'Copies the information from the source book to the summary file
i = 1
rng1.Copy Destination:=rng((i - 1) * 59 + 1).Offset(0, 1)
'determines the number of rows that will be used in the next part of
the
procedure
j = i * 60
k = 0 - j
l = 5 + j
'increments number of workbooks to multiply by number of rows in
notepad
'ActiveWorkbook.Close SaveChanges:=False
Workbooks(myNotePadSummary).Sheets(theSelectedNote Pad).Delete
Workbooks("New Sales Attempt.xls").Sheets("Summary").Copy
Befo=Workbooks(myNotePadSummary).Sheets("templa te")
ActiveSheet.DrawingObjects.Select
Selection.Delete

Application.CutCopyMode = False
'selects the first cell in the range that will determine whether the
row should be hidden

Sheets("Summary").Name = theSelectedNotePad
tablerow1 = tablerow1 + 1

Loop

Workbooks(myCC).Close savechanges:=False
tablerow = tablerow + 1
myCC = Workbooks("New SalesAttempt.xls").Sheets("ttls").Cells(tablerow,
z)

Loop
End Sub


---
Message posted from http://www.ExcelForum.com/