Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all worksheets in a workbook
I have some code which automatically copies and pastes data off all workbooks
(1 sheet each) in a folder. How about if I want to make it cycle through multiple worksheets within a workbook before closing it? I made a few modifications to this loop, but it is not working. Anyone know why? Do While sFil < "" Workbooks.Open sPath & sFil For Each w In ActiveWorkbook.Worksheets <---added this With ThisWorkbook.Worksheets("Bulk") <---and this k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).R ow n = k + 4 Set oWbk = Workbooks.Open(sPath & "\" & sFil) Range("B4:Z1000").Copy Set w = ThisWorkbook.Sheets(1) ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial End With <---added this Next w <---and this oWbk.Close True <--should this be False I appreciate your help as always. Arlen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all worksheets in a workbook
I am not sure what you are doing with this line
Set w = ThisWorkbook.Sheets(1) since it is inside of this loop For Each w In ActiveWorkbook.Worksheets ' ' next w Genearlly speaking your loop is going to move through all of the worksheets in the activeworkbook but not if you change the reference to w within the loop. -- HTH... Jim Thomlinson "Arlen" wrote: I have some code which automatically copies and pastes data off all workbooks (1 sheet each) in a folder. How about if I want to make it cycle through multiple worksheets within a workbook before closing it? I made a few modifications to this loop, but it is not working. Anyone know why? Do While sFil < "" Workbooks.Open sPath & sFil For Each w In ActiveWorkbook.Worksheets <---added this With ThisWorkbook.Worksheets("Bulk") <---and this k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).R ow n = k + 4 Set oWbk = Workbooks.Open(sPath & "\" & sFil) Range("B4:Z1000").Copy Set w = ThisWorkbook.Sheets(1) ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial End With <---added this Next w <---and this oWbk.Close True <--should this be False I appreciate your help as always. Arlen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all worksheets in a workbook
Hi,
Several ways, here's one Dim x As Long For x = 1 To Worksheets.Count MsgBox Worksheets(x).Name 'do something Next Mike "Arlen" wrote: I have some code which automatically copies and pastes data off all workbooks (1 sheet each) in a folder. How about if I want to make it cycle through multiple worksheets within a workbook before closing it? I made a few modifications to this loop, but it is not working. Anyone know why? Do While sFil < "" Workbooks.Open sPath & sFil For Each w In ActiveWorkbook.Worksheets <---added this With ThisWorkbook.Worksheets("Bulk") <---and this k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).R ow n = k + 4 Set oWbk = Workbooks.Open(sPath & "\" & sFil) Range("B4:Z1000").Copy Set w = ThisWorkbook.Sheets(1) ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial End With <---added this Next w <---and this oWbk.Close True <--should this be False I appreciate your help as always. Arlen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all worksheets in a workbook
Jim,
I apologize for the confusion. I found the good cycling code from one macro and combined it with the good copy/pasting of another. Here is the whole thing, and again, it will go through a single sheet and close the book just fine, but it won't cycle through multiple worksheets before closing the book and moving on. Sub Cycler() Dim oWbk As Workbook Dim w As Worksheet Dim sFil As String Dim sPath As String Dim k As Long, n As Long sPath = "C:\Documents and Settings\gl1b\Desktop\ExcelStuff\2008\Tacoma\Bulk" ChDir sPath sFil = Dir("*.xls") 'change or add formats Application.DisplayAlerts = False k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).R ow n = k + 1 Do While sFil < "" For Each w In ActiveWorkbook.Worksheets k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).R ow n = k + 1 Set oWbk = Workbooks.Open(sPath & "\" & sFil) Range("B4:Z1000").Copy ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial Next w oWbk.Close True sFil = Dir Loop Application.DisplayAlerts = True End Sub I know not what is wrong. I only understand people's explanations. Thank you for your effort on my behalf. Arlen "Jim Thomlinson" wrote: I am not sure what you are doing with this line Set w = ThisWorkbook.Sheets(1) since it is inside of this loop For Each w In ActiveWorkbook.Worksheets ' ' next w Genearlly speaking your loop is going to move through all of the worksheets in the activeworkbook but not if you change the reference to w within the loop. -- HTH... Jim Thomlinson "Arlen" wrote: I have some code which automatically copies and pastes data off all workbooks (1 sheet each) in a folder. How about if I want to make it cycle through multiple worksheets within a workbook before closing it? I made a few modifications to this loop, but it is not working. Anyone know why? Do While sFil < "" Workbooks.Open sPath & sFil For Each w In ActiveWorkbook.Worksheets <---added this With ThisWorkbook.Worksheets("Bulk") <---and this k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).R ow n = k + 4 Set oWbk = Workbooks.Open(sPath & "\" & sFil) Range("B4:Z1000").Copy Set w = ThisWorkbook.Sheets(1) ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial End With <---added this Next w <---and this oWbk.Close True <--should this be False I appreciate your help as always. Arlen |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all worksheets in a workbook
Mike,
Thanks for helping. I tried this code and I still get only one sheet at a time. I'll keep playing with it. Arlen "Mike H" wrote: Hi, Several ways, here's one Dim x As Long For x = 1 To Worksheets.Count MsgBox Worksheets(x).Name 'do something Next Mike "Arlen" wrote: I have some code which automatically copies and pastes data off all workbooks (1 sheet each) in a folder. How about if I want to make it cycle through multiple worksheets within a workbook before closing it? I made a few modifications to this loop, but it is not working. Anyone know why? Do While sFil < "" Workbooks.Open sPath & sFil For Each w In ActiveWorkbook.Worksheets <---added this With ThisWorkbook.Worksheets("Bulk") <---and this k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).R ow n = k + 4 Set oWbk = Workbooks.Open(sPath & "\" & sFil) Range("B4:Z1000").Copy Set w = ThisWorkbook.Sheets(1) ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial End With <---added this Next w <---and this oWbk.Close True <--should this be False I appreciate your help as always. Arlen |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all worksheets in a workbook
You have a copule of problems.
1 - Each time you go from 1 sheet to the next you try to open a new workbook. 2 - You do not explicitly reference the workbook you are dealing with which is very important when accessing multiple books... Sub Cycler() Dim oWbk As Workbook Dim w As Worksheet Dim sFil As String Dim sPath As String sPath = "C:\Documents and Settings\gl1b\Desktop\ExcelStuff\2008\Tacoma\Bulk" ChDir sPath sFil = Dir("*.xls") 'change or add formats Application.DisplayAlerts = False Do While sFil < "" Set oWbk = Workbooks.Open(sPath & "\" & sFil) For Each w In oWbk .Worksheets w.Range("B4:Z1000").Copy Destination:= _ ThisWorkbook.Sheets(1).cells(rows.count, "A").end(xlup).offset(1,0) Next w oWbk.Close False 'don't save sFil = Dir Loop Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson "Arlen" wrote: Jim, I apologize for the confusion. I found the good cycling code from one macro and combined it with the good copy/pasting of another. Here is the whole thing, and again, it will go through a single sheet and close the book just fine, but it won't cycle through multiple worksheets before closing the book and moving on. Sub Cycler() Dim oWbk As Workbook Dim w As Worksheet Dim sFil As String Dim sPath As String Dim k As Long, n As Long sPath = "C:\Documents and Settings\gl1b\Desktop\ExcelStuff\2008\Tacoma\Bulk" ChDir sPath sFil = Dir("*.xls") 'change or add formats Application.DisplayAlerts = False k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).R ow n = k + 1 Do While sFil < "" For Each w In ActiveWorkbook.Worksheets k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).R ow n = k + 1 Set oWbk = Workbooks.Open(sPath & "\" & sFil) Range("B4:Z1000").Copy ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial Next w oWbk.Close True sFil = Dir Loop Application.DisplayAlerts = True End Sub I know not what is wrong. I only understand people's explanations. Thank you for your effort on my behalf. Arlen "Jim Thomlinson" wrote: I am not sure what you are doing with this line Set w = ThisWorkbook.Sheets(1) since it is inside of this loop For Each w In ActiveWorkbook.Worksheets ' ' next w Genearlly speaking your loop is going to move through all of the worksheets in the activeworkbook but not if you change the reference to w within the loop. -- HTH... Jim Thomlinson "Arlen" wrote: I have some code which automatically copies and pastes data off all workbooks (1 sheet each) in a folder. How about if I want to make it cycle through multiple worksheets within a workbook before closing it? I made a few modifications to this loop, but it is not working. Anyone know why? Do While sFil < "" Workbooks.Open sPath & sFil For Each w In ActiveWorkbook.Worksheets <---added this With ThisWorkbook.Worksheets("Bulk") <---and this k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).R ow n = k + 4 Set oWbk = Workbooks.Open(sPath & "\" & sFil) Range("B4:Z1000").Copy Set w = ThisWorkbook.Sheets(1) ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial End With <---added this Next w <---and this oWbk.Close True <--should this be False I appreciate your help as always. Arlen |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all worksheets in a workbook
Hi Arlen
See http://www.rondebruin.nl/fso.htm Or use the add-in http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Arlen" wrote in message ... I have some code which automatically copies and pastes data off all workbooks (1 sheet each) in a folder. How about if I want to make it cycle through multiple worksheets within a workbook before closing it? I made a few modifications to this loop, but it is not working. Anyone know why? Do While sFil < "" Workbooks.Open sPath & sFil For Each w In ActiveWorkbook.Worksheets <---added this With ThisWorkbook.Worksheets("Bulk") <---and this k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).R ow n = k + 4 Set oWbk = Workbooks.Open(sPath & "\" & sFil) Range("B4:Z1000").Copy Set w = ThisWorkbook.Sheets(1) ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial End With <---added this Next w <---and this oWbk.Close True <--should this be False I appreciate your help as always. Arlen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shortcut key to cycle through worksheets in the same workbook | Excel Discussion (Misc queries) | |||
Cycle through worksheets and sum | Excel Programming | |||
How do I cycle through all of the worksheets | Excel Discussion (Misc queries) | |||
Cycle through all worksheets | Excel Programming | |||
Cycle thru worksheets in a workbook | Excel Programming |