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.

--

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.

--

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.

--

how do you loop through each worksheet?
 

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?

--
Don Guillett
SalesAid Software

"dundonald" wrote in message
...
"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.

--

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

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




--

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




--

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




--

how do you loop through each worksheet?
 

Neater than mine. I temporarily forgot the set source.

--
Don Guillett
SalesAid Software

"J.E. McGimpsey" wrote in message
...
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




--

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

--

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

--

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

--

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.



--

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.



--

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.

--

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.

--

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