how do you loop through each worksheet?

  • Thread starter Thread starter dundonald
  • Start date Start date
D

dundonald

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.

--
 
Don Guillett said:
for each ws in worksheets
your code here
next ws
Click to expand...

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.

--
 
Either will work. You will find that " there is more than one way to skin a
cat"
With the worksheet loop, what are you trying to accomplish or is this a
homework assignment?
 
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.
 
Don Guillett said:
Either will work. You will find that " there is more than one way to skin a
cat"
Click to expand...

ok fair enough.
With the worksheet loop, what are you trying to accomplish or is this a
homework assignment?
Click to expand...

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




--
 
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.
 
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
 
J.E. McGimpsey said:
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.
Click to expand...

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>

--
 
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.
 
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>

--
 
Don Guillett said:
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.
Click to expand...

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.



--
 
Don Guillett said:
How many should it have copied? Didn't your OP say to the last row?
Click to expand...

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.

--
 
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
 
Back
Top