Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
I'm an excel newbie. I've figured out how to cycle through each row in a
given worksheet and I can extract data from whatever column in that worksheet. I've found how to do this by searching this group's archive. I haven't been so fortunate, despite my searches, for with my next question though: What I would now like to know is how do I cycle through each worksheet in a file (do you call it workbook?)? Obviously when I know how to do this, it would mean I can cycle through each worksheet in a workbook, and locate any cell. Thanks. -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
for each ws in worksheets
your code here next ws -- Don Guillett SalesAid Software "dundonald" wrote in message ... I'm an excel newbie. I've figured out how to cycle through each row in a given worksheet and I can extract data from whatever column in that worksheet. I've found how to do this by searching this group's archive. I haven't been so fortunate, despite my searches, for with my next question though: What I would now like to know is how do I cycle through each worksheet in a file (do you call it workbook?)? Obviously when I know how to do this, it would mean I can cycle through each worksheet in a workbook, and locate any cell. Thanks. -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
"Don Guillett" thoughtfully wrote:
for each ws in worksheets your code here next ws Thanks Don. I did some more searching and came up with the following: For Each sht In ActiveWorkbook.Worksheets MsgBox sht.Name Next What's the difference? Also, I'm having trouble starting to cycle through rows for each worksheet within the loop iteration. This is the code I ususally use to cycle through rows for a given worksheet at the moment: Set rng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) rwStart = rng.Rows(1).Row rwEnd = rng.Rows(rng.Rows.Count).Row For i = rwEnd To rwStart Step -1 Set rng1 = Cells(i, "A") 'do something with rng1 Set rng2 = Cells(i, "B") 'do something with rng2 Set rng3 = Cells(i, "C") 'do something with rng3 etc ... Next but I'm not sure how to include this in the loop iterating through worksheets. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
dim wks as worksheet
for each wks in activeworkbook.worksheets wks.select Set rng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) rwStart = rng.Rows(1).Row rwEnd = rng.Rows(rng.Rows.Count).Row For i = rwEnd To rwStart Step -1 Set rng1 = Cells(i, "A") 'do something with rng1 Set rng2 = Cells(i, "B") 'do something with rng2 Set rng3 = Cells(i, "C") 'do something with rng3 etc ... Next i next wks would be one way. just select the worksheet first and do everything against the active sheet. Another way: for each wks in activeworkbook.worksheets with wks Set rng = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp)) rwStart = rng.Rows(1).Row rwEnd = rng.Rows(rng.Rows.Count).Row For i = rwEnd To rwStart Step -1 Set rng1 = .Cells(i, "A") 'do something with rng1 Set rng2 = .Cells(i, "B") 'do something with rng2 Set rng3 = .Cells(i, "C") 'do something with rng3 etc ... Next i end with next wks This one uses with/end with structure. Notice all the dots in front of the ..range's and .cells. This means that those references belong to the previous "with" line. And you don't have to select the sheet first. dundonald wrote: "Don Guillett" thoughtfully wrote: for each ws in worksheets your code here next ws Thanks Don. I did some more searching and came up with the following: For Each sht In ActiveWorkbook.Worksheets MsgBox sht.Name Next What's the difference? Also, I'm having trouble starting to cycle through rows for each worksheet within the loop iteration. This is the code I ususally use to cycle through rows for a given worksheet at the moment: Set rng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) rwStart = rng.Rows(1).Row rwEnd = rng.Rows(rng.Rows.Count).Row For i = rwEnd To rwStart Step -1 Set rng1 = Cells(i, "A") 'do something with rng1 Set rng2 = Cells(i, "B") 'do something with rng2 Set rng3 = Cells(i, "C") 'do something with rng3 etc ... Next but I'm not sure how to include this in the loop iterating through worksheets. -- -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
"Don Guillett" thoughtfully wrote:
Either will work. You will find that " there is more than one way to skin a cat" ok fair enough. With the worksheet loop, what are you trying to accomplish or is this a homework assignment? lol no not a homework assignment. I'm trying to teach myself excel programming whilst trying to create a spreadsheet to personally use. First of all many thanks for your help. Basically what I want to do is, with the click of a button (no problem creating the button and assigning a function), write a function that will: 1. copy columns A, B and C from row 7 onward to the last populated row from the 1st worksheet 2. past these cells into all remaining worksheets (12 - one for each month) into the same area, i.e., columns A, B and C from row 7 onward. So in effect the function will access worksheet 1, copy the data, then cycle through the next 12 worksheets and paste the data in the same range as it was collected from worksheet 1. I just can't figure out how to then cycle through each row inside the cycle of each worksheet. i.e. here's some pseudo code for each ws in worksheets 'if this worksheet the first one 'copy data (cycle through each populated row starting from row 7 copying columns A, B and C) 'else must be one of the remaining 12 worksheets so 'paste data next ws -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
One way:
Dim rSource As Range Dim i As Long Set rSource = Worksheets(1).Range("A7:C" & _ Range("A" & Rows.Count).End(xlUp).Row) For i = 2 To Worksheets.Count rSource.Copy Worksheets(i).Range("A7") Next i no need to cycle through the range - Copy can take it all at once. In article , dundonald wrote: "Don Guillett" thoughtfully wrote: Either will work. You will find that " there is more than one way to skin a cat" ok fair enough. With the worksheet loop, what are you trying to accomplish or is this a homework assignment? lol no not a homework assignment. I'm trying to teach myself excel programming whilst trying to create a spreadsheet to personally use. First of all many thanks for your help. Basically what I want to do is, with the click of a button (no problem creating the button and assigning a function), write a function that will: 1. copy columns A, B and C from row 7 onward to the last populated row from the 1st worksheet 2. past these cells into all remaining worksheets (12 - one for each month) into the same area, i.e., columns A, B and C from row 7 onward. So in effect the function will access worksheet 1, copy the data, then cycle through the next 12 worksheets and paste the data in the same range as it was collected from worksheet 1. I just can't figure out how to then cycle through each row inside the cycle of each worksheet. i.e. here's some pseudo code for each ws in worksheets 'if this worksheet the first one 'copy data (cycle through each populated row starting from row 7 copying columns A, B and C) 'else must be one of the remaining 12 worksheets so 'paste data next ws -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
try this. Modify sheet name and ranges to suit
try this. It will determine the last data in col a from the bottom up. If you want to go down to the next use cells(activecell.row,"a").end(xldown).row+1 It will then copy from sheet1 to all other sheets except sheet1. I suppose, in THIS case it wouldn't be necessary to have that one excluded but. Sub copy7on() x = Worksheets("sheet1").Cells(Rows.Count, "a").End(xlUp).Row + 1 MsgBox x For Each ws In Worksheets If ws.Name < "Sheet1" Then _ Worksheets("sheet1").Cells(7, 1). _ Resize(x - 7, 3).Copy ws.Cells(7, 1) Next End Sub -- Don Guillett SalesAid Software "dundonald" wrote in message ... "Don Guillett" thoughtfully wrote: Either will work. You will find that " there is more than one way to skin a cat" ok fair enough. With the worksheet loop, what are you trying to accomplish or is this a homework assignment? lol no not a homework assignment. I'm trying to teach myself excel programming whilst trying to create a spreadsheet to personally use. First of all many thanks for your help. Basically what I want to do is, with the click of a button (no problem creating the button and assigning a function), write a function that will: 1. copy columns A, B and C from row 7 onward to the last populated row from the 1st worksheet 2. past these cells into all remaining worksheets (12 - one for each month) into the same area, i.e., columns A, B and C from row 7 onward. So in effect the function will access worksheet 1, copy the data, then cycle through the next 12 worksheets and paste the data in the same range as it was collected from worksheet 1. I just can't figure out how to then cycle through each row inside the cycle of each worksheet. i.e. here's some pseudo code for each ws in worksheets 'if this worksheet the first one 'copy data (cycle through each populated row starting from row 7 copying columns A, B and C) 'else must be one of the remaining 12 worksheets so 'paste data next ws -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
"J.E. McGimpsey" thoughtfully wrote:
One way: Dim rSource As Range Dim i As Long Set rSource = Worksheets(1).Range("A7:C" & _ Range("A" & Rows.Count).End(xlUp).Row) For i = 2 To Worksheets.Count rSource.Copy Worksheets(i).Range("A7") Next i no need to cycle through the range - Copy can take it all at once. Many thanks. As you say that seems a more efficient way to make the copy than to cycle through each worksheet. I have a question though, what is the underscore for in the Set rSource statement above? I get a compile error when I attempt to concatonate onto one line with a space after the underscore and before Range("A" ..) Thanks. <snip -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
The _ is a continuation character so that you can break up the line length.
If you then make into one line, it should be removed. -- Don Guillett SalesAid Software "dundonald" wrote in message ... "J.E. McGimpsey" thoughtfully wrote: One way: Dim rSource As Range Dim i As Long Set rSource = Worksheets(1).Range("A7:C" & _ Range("A" & Rows.Count).End(xlUp).Row) For i = 2 To Worksheets.Count rSource.Copy Worksheets(i).Range("A7") Next i no need to cycle through the range - Copy can take it all at once. Many thanks. As you say that seems a more efficient way to make the copy than to cycle through each worksheet. I have a question though, what is the underscore for in the Set rSource statement above? I get a compile error when I attempt to concatonate onto one line with a space after the underscore and before Range("A" ..) Thanks. <snip -- |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
Dave Peterson thoughtfully wrote:
Thanks Dave. That will help me! Didn't know that about the . That is why I couldn't get my code to work inside the worksheet for next loop. <snip -- |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
"Don Guillett" thoughtfully wrote:
The _ is a continuation character so that you can break up the line length. If you then make into one line, it should be removed. I see thanks Don. Only prob with the code is it only copies the first two rows, i.e. 7 and 8. In fact the first time I click the button it copies rows 6 and 7. Then when I click the button again and thereafter it copies rows 7 and 8. Appreciate all the help I've received here. Thanks. -- |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
How many should it have copied? Didn't your OP say to the last row?
-- Don Guillett SalesAid Software "dundonald" wrote in message ... "Don Guillett" thoughtfully wrote: The _ is a continuation character so that you can break up the line length. If you then make into one line, it should be removed. I see thanks Don. Only prob with the code is it only copies the first two rows, i.e. 7 and 8. In fact the first time I click the button it copies rows 6 and 7. Then when I click the button again and thereafter it copies rows 7 and 8. Appreciate all the help I've received here. Thanks. -- |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
"Don Guillett" thoughtfully wrote:
How many should it have copied? Didn't your OP say to the last row? that's correct. There are about 150 rows. I've checked the range in the code that J.E gave and it looks fine. Strange. -- |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
It might be that JE's doesn't count the last row unless you are on sheet1
try Set rSource = Worksheets(1).Range("A7:C" & _ worksheets(1). Range("A" & Rows.Count).End(xlUp).Row) or mine -- Don Guillett SalesAid Software "dundonald" wrote in message ... "Don Guillett" thoughtfully wrote: How many should it have copied? Didn't your OP say to the last row? that's correct. There are about 150 rows. I've checked the range in the code that J.E gave and it looks fine. Strange. -- |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you loop through each worksheet?
True. That's what the OP specified:
1. copy columns A, B and C from row 7 onward to the last populated row from the 1st worksheet In article , "Don Guillett" wrote: It might be that JE's doesn't count the last row unless you are on sheet1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to loop through all ranges in a worksheet | Excel Programming | |||
Loop through checkBox on worksheet | Excel Programming | |||
worksheet loop | Excel Programming | |||
How? Macro to copy range to new worksheet, name new worksheet, loop | Excel Programming | |||
If... Then Loop problems in Worksheet Event | Excel Programming |