Re: nesting problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: a (graylatt_at_earthlink.net)
Date: 02/04/04 

Date: Wed, 04 Feb 2004 14:29:02 GMT

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(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")
> 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).Range("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(theSelectedNotePad).Delete
> Workbooks("New Sales Attempt.xls").Sheets("Summary").Copy
> Before:=Workbooks(myNotePadSummary).Sheets("template")
> 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/
>

Relevant Pages

  • exclude hidden worksheets while merging.
    ... I want to exclude hidden worksheets in a workbook from a merge along ... This is the code for excluding the summary sheet. ... 'If worksheet in loop is the last one, ...
    (microsoft.public.excel.programming)
  • Re: Cannot Move, Copy, Insert, Rename, or Delete a Worksheet in An
    ... Maybe you can reset that toolbar manually: ... they can just open that other workbook and run those macros. ... Professional Excel Development: The Definitive Guide to Developing Applications ... The second resets all the options on the worksheet tab menu. ...
    (microsoft.public.excel.misc)
  • Please Help!!! How Do I Specify A particualr work book to execute code on
    ... Public wb As Workbook ... Public TheFile As String ... Loop ... Now the problem is everytime i open a work book i want the Do Until ...
    (microsoft.public.excel.programming)
  • Re: one signal set from two processes .....
    ... main: loop ... Isn't the "exit main when reset = '1'" a synchronous reset? ... defense line "the smaller the better" usually drives designers to write ... multi-threaded) computing that can be performed in parallel by an ...
    (comp.lang.vhdl)
  • Re: addition of apostrophe in data in excel after access export
    ... You would do it after the loop below, at which point the workbooks have ... 'Create instance of Excel ... The RemoveAllApostrophes() and RemoveApostrophesFromSheetprocedures ... >are still Access Tables and Excel workbook are not formed yet? ...
    (microsoft.public.access.externaldata)