Repost: How do you identify a worksheet as last?
 

Hello,
My original question is as follows with the two responses
I received below, however, I'm pretty sure neither of the
responses work because the code copies and pastes multiple
cell references (i.e. it activates one worksheet and then
activates another over and over before it goes on to the
next worksheet. Maybe I'm wrong, but in any case it's not
working! When I moved the sheet after the one it had just
copied it worked but would end up in an error message.
When I applied the responses it doesn't paste anything at
all.
Any ideas? (and thanks for the responses)
Cheers, Natasha.

Natasha wrote in message
...
I have this macro I'm working on. It basically cuts ands
pastes certain ranges from one worksheet into another. I
wanted it to do the same ranges for all the other
worksheets (using 'For Each Worksheet in Worksheets') too
so when it goes to select the range in the worksheet to
be
copied I wrote 'ActiveWorksheet.Next.Activate'.
It wasn't moving onto the worksheet after when it got
to 'Next Worksheet', so I moved moved the worksheet I
needed to copy into before the next worksheet I wanted to
copy. This works great except when this worksheets ends
up
at the end of the workbook I get an error message. I
wanted to write an If worksheet ("blah")is at the end of
the workbook (or has the last index no. or
something) 'End
If' Does anyone have any ideas? I didn't want to specify
a
particular index number because I want to use the same
Macro for multiple workbooks and they each have a
different number of worksheets.

Not sure I understand you description, but if you want to
write data from
each sheet (except the summary sheet) to the summary
sheet, then this would
work.
for each sh in thisworkbook.worksheets
if sh.name < worksheets("Master").Name then
sh.Range("A1:A25").copy _
Destination:=worksheets("Master"). _
Cells(rows.count,1).End(xlup)(2)
end if
Next
--
Regards,
Tom Ogilvy

Hi Natasha,
Try the below. See if that helps.
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
----put code in here ----
Next ws
HTH's

Repost: How do you identify a worksheet as last?
 

for each sh in thisworkbook.worksheets
if sh.name < worksheets("Master").Name then
sh.Range("A1:A25").copy

worksheets("Master").Range("A65000").End(xlup).Off set(1,0).Pastespecial
xlallvalues
end if
Next

it loops through every sheet - as you requested. with each sheet it copies a
range ( example is A1:A25) to the next empty rows on the sheet called
'Master'. It skips the sheet called 'Master' in the IF statement.No sheets
are activated - there's no reason to in good code..


--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"Natasha" wrote in message
...
Hello,
My original question is as follows with the two responses
I received below, however, I'm pretty sure neither of the
responses work because the code copies and pastes multiple
cell references (i.e. it activates one worksheet and then
activates another over and over before it goes on to the
next worksheet. Maybe I'm wrong, but in any case it's not
working! When I moved the sheet after the one it had just
copied it worked but would end up in an error message.
When I applied the responses it doesn't paste anything at
all.
Any ideas? (and thanks for the responses)
Cheers, Natasha.

Natasha wrote in message
...
I have this macro I'm working on. It basically cuts ands
pastes certain ranges from one worksheet into another. I
wanted it to do the same ranges for all the other
worksheets (using 'For Each Worksheet in Worksheets') too
so when it goes to select the range in the worksheet to
be
copied I wrote 'ActiveWorksheet.Next.Activate'.
It wasn't moving onto the worksheet after when it got
to 'Next Worksheet', so I moved moved the worksheet I
needed to copy into before the next worksheet I wanted to
copy. This works great except when this worksheets ends
up
at the end of the workbook I get an error message. I
wanted to write an If worksheet ("blah")is at the end of
the workbook (or has the last index no. or
something) 'End
If' Does anyone have any ideas? I didn't want to specify
a
particular index number because I want to use the same
Macro for multiple workbooks and they each have a
different number of worksheets.

Not sure I understand you description, but if you want to
write data from
each sheet (except the summary sheet) to the summary
sheet, then this would
work.
for each sh in thisworkbook.worksheets
if sh.name < worksheets("Master").Name then
sh.Range("A1:A25").copy _
Destination:=worksheets("Master"). _
Cells(rows.count,1).End(xlup)(2)
end if
Next
--
Regards,
Tom Ogilvy

Hi Natasha,
Try the below. See if that helps.
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
----put code in here ----
Next ws
HTH's

Repost: How do you identify a worksheet as last?
 

slowly. every other sheet? do you mean eevry sheet?
Also. if you copy C16:J21 to the same range on the target
sheet, all you'll get is the last copied sheet's data as
each time you do this, you'll overwrite what was there.
that's why both Tom Ogilvy and I placed the data in the
next available row.


The for..next loop can easily work for multiple
workbooks. lets sort out the workbook level first. Try to
expalin a little more clearly please.
1) copy the data ( A5,C22,C16:J21) from every sheet to
the sheet called 'data for access'
2) the dat afor each sheet is to go where on 'data for
access'? remember, we think we need to avoid over-
writing data.


Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
I'm sorry but I'm really confused. I've tried this and
it
didn't work either. Maybe I'm supposed to be entering
things into this and I'm not sure where to put them.
I basically need to copy the ranges A5,C22,C16:J16,
C17:J17, C18:J18, C19:J19, C20:J20, C21:J21 from every
other sheet in the workbook into a sheet called "data
for
access". I want all these ranges in the same row, and
each
sheet to be on the next row. And I want to be able to do
multiple workbooks at the same time.
My code is as follows (without the moving aspect that I
mentioned previously):

For Each Workbook In Workbooks
For Each Worksheet In Worksheets
ActiveWorksheet.Next.Activate
Range("A5").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Next.Select
ActiveWorksheet.Next.Activate
Range("C22").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Next.Select
ActiveWorksheet.Next.Activate
Range("C16:J16").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("data for access").Activate
ActiveCell.Offset
(rowOffset:=0,columnOffset:=8).Activate
ActiveWorksheet.Next.Activate
Range("C17:J17").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("data for access").Activate
ActiveCell.Offset(rowOffset:=0,
columnOffset:=8).Activate
ActiveWorksheet.Next.Activate
Range("C18:J18").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("data for access").Activate
ActiveCell.Offset(rowOffset:=0,
columnOffset:=8).Activate
ActiveWorksheet.Next.Activate
Range("C19:J19").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("data for access").Activate
ActiveCell.Offset(rowOffset:=0,
columnOffset:=8).Activate
ActiveWorksheet.Next.Activate
Range("C20:J20").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("data for access").Activate
ActiveCell.Offset(rowOffset:=0,
columnOffset:=8).Activate
ActiveWorksheet.Next.Activate
Range("C21:J21").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("data for access").Activate
ActiveCell.Offset(rowOffset:=1, columnOffset:=-
42).Activate
Next Worksheet
Next Workbook

-----Original Message-----
for each sh in thisworkbook.worksheets
if sh.name < worksheets("Master").Name then
sh.Range("A1:A25").copy

worksheets("Master").Range("A65000").End(xlup).O ffset
(1,0).Pastespecial
xlallvalues
end if
Next

it loops through every sheet - as you requested. with
each sheet it copies a
range ( example is A1:A25) to the next empty rows on
the
sheet called
'Master'. It skips the sheet called 'Master' in the IF
statement.No sheets
are activated - there's no reason to in good code..


--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"Natasha" wrote in
message
...
Hello,
My original question is as follows with the two
responses
I received below, however, I'm pretty sure neither of
the
responses work because the code copies and pastes
multiple
cell references (i.e. it activates one worksheet and
then
activates another over and over before it goes on to
the
next worksheet. Maybe I'm wrong, but in any case it's
not
working! When I moved the sheet after the one it had
just
copied it worked but would end up in an error message.
When I applied the responses it doesn't paste
anything
at
all.
Any ideas? (and thanks for the responses)
Cheers, Natasha.

Natasha wrote in
message
...
I have this macro I'm working on. It basically cuts
ands
pastes certain ranges from one worksheet into
another. I
wanted it to do the same ranges for all the other
worksheets (using 'For Each Worksheet in
Worksheets')
too
so when it goes to select the range in the
worksheet
to
be
copied I wrote 'ActiveWorksheet.Next.Activate'.
It wasn't moving onto the worksheet after when it
got
to 'Next Worksheet', so I moved moved the worksheet
I
needed to copy into before the next worksheet I
wanted to
copy. This works great except when this worksheets
ends
up
at the end of the workbook I get an error message. I
wanted to write an If worksheet ("blah")is at the
end
of
the workbook (or has the last index no. or
something) 'End
If' Does anyone have any ideas? I didn't want to
specify
a
particular index number because I want to use the
same
Macro for multiple workbooks and they each have a
different number of worksheets.

Not sure I understand you description, but if you
want
to
write data from
each sheet (except the summary sheet) to the summary
sheet, then this would
work.
for each sh in thisworkbook.worksheets
if sh.name < worksheets("Master").Name then
sh.Range("A1:A25").copy _
Destination:=worksheets("Master"). _
Cells(rows.count,1).End(xlup)(2)
end if
Next
--
Regards,
Tom Ogilvy

Hi Natasha,
Try the below. See if that helps.
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
----put code in here ----
Next ws
HTH's



.

.

Repost: How do you identify a worksheet as last?
 

Sub Tester1()
Dim bk As Workbook
Dim sh As Worksheet
Dim masSh As Worksheet
Dim icol As Long
Dim rng1 As Range
Dim cell As Range
For Each bk In Application.Workbooks
On Error Resume Next
Set masSh = bk.Worksheets("data for access")
On Error GoTo 0
If Not masSh Is Nothing Then Exit For
Next
If masSh Is Nothing Then
MsgBox "data for access not found"
Exit Sub
End If
For Each bk In Application.Workbooks
For Each sh In bk.Worksheets
If sh.Name < masSh.Name Then
icol = 2
Set rng1 = masSh.Cells(Rows.Count, 1).End(xlUp)(2)
Cells(rng1.Row, 1).Value = bk.Name & " " & sh.Name
For Each cell In sh.Range( _
"A5,C22,C16:J21")
cell.Copy masSh.Cells(rng1.Row, icol)
icol = icol + 1
Next
End If
Next sh
Next bk

End Sub

Does what you describe - hard to tell if that is what you want.

--
Regards,
Tom Ogilvy



Natasha wrote in message
...
I'm sorry but I'm really confused. I've tried this and it
didn't work either. Maybe I'm supposed to be entering
things into this and I'm not sure where to put them.
I basically need to copy the ranges A5,C22,C16:J16,
C17:J17, C18:J18, C19:J19, C20:J20, C21:J21 from every
other sheet in the workbook into a sheet called "data for
access". I want all these ranges in the same row, and each
sheet to be on the next row. And I want to be able to do
multiple workbooks at the same time.
My code is as follows (without the moving aspect that I
mentioned previously):

For Each Workbook In Workbooks
For Each Worksheet In Worksheets
ActiveWorksheet.Next.Activate
Range("A5").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Next.Select
ActiveWorksheet.Next.Activate
Range("C22").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Next.Select
ActiveWorksheet.Next.Activate
Range("C16:J16").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("data for access").Activate
ActiveCell.Offset
(rowOffset:=0,columnOffset:=8).Activate
ActiveWorksheet.Next.Activate
Range("C17:J17").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("data for access").Activate
ActiveCell.Offset(rowOffset:=0,
columnOffset:=8).Activate
ActiveWorksheet.Next.Activate
Range("C18:J18").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("data for access").Activate
ActiveCell.Offset(rowOffset:=0,
columnOffset:=8).Activate
ActiveWorksheet.Next.Activate
Range("C19:J19").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("data for access").Activate
ActiveCell.Offset(rowOffset:=0,
columnOffset:=8).Activate
ActiveWorksheet.Next.Activate
Range("C20:J20").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("data for access").Activate
ActiveCell.Offset(rowOffset:=0,
columnOffset:=8).Activate
ActiveWorksheet.Next.Activate
Range("C21:J21").Select
Selection.Copy
Sheets("data for access").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("data for access").Activate
ActiveCell.Offset(rowOffset:=1, columnOffset:=-
42).Activate
Next Worksheet
Next Workbook

-----Original Message-----
for each sh in thisworkbook.worksheets
if sh.name < worksheets("Master").Name then
sh.Range("A1:A25").copy

worksheets("Master").Range("A65000").End(xlup).Of fset
(1,0).Pastespecial
xlallvalues
end if
Next

it loops through every sheet - as you requested. with
each sheet it copies a
range ( example is A1:A25) to the next empty rows on the
sheet called
'Master'. It skips the sheet called 'Master' in the IF
statement.No sheets
are activated - there's no reason to in good code..


--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"Natasha" wrote in message
...
Hello,
My original question is as follows with the two
responses
I received below, however, I'm pretty sure neither of
the
responses work because the code copies and pastes
multiple
cell references (i.e. it activates one worksheet and
then
activates another over and over before it goes on to the
next worksheet. Maybe I'm wrong, but in any case it's
not
working! When I moved the sheet after the one it had
just
copied it worked but would end up in an error message.
When I applied the responses it doesn't paste anything
at
all.
Any ideas? (and thanks for the responses)
Cheers, Natasha.

Natasha wrote in message
...
I have this macro I'm working on. It basically cuts
ands
pastes certain ranges from one worksheet into
another. I
wanted it to do the same ranges for all the other
worksheets (using 'For Each Worksheet in Worksheets')
too
so when it goes to select the range in the worksheet
to
be
copied I wrote 'ActiveWorksheet.Next.Activate'.
It wasn't moving onto the worksheet after when it got
to 'Next Worksheet', so I moved moved the worksheet I
needed to copy into before the next worksheet I
wanted to
copy. This works great except when this worksheets
ends
up
at the end of the workbook I get an error message. I
wanted to write an If worksheet ("blah")is at the end
of
the workbook (or has the last index no. or
something) 'End
If' Does anyone have any ideas? I didn't want to
specify
a
particular index number because I want to use the same
Macro for multiple workbooks and they each have a
different number of worksheets.

Not sure I understand you description, but if you want
to
write data from
each sheet (except the summary sheet) to the summary
sheet, then this would
work.
for each sh in thisworkbook.worksheets
if sh.name < worksheets("Master").Name then
sh.Range("A1:A25").copy _
Destination:=worksheets("Master"). _
Cells(rows.count,1).End(xlup)(2)
end if
Next
--
Regards,
Tom Ogilvy

Hi Natasha,
Try the below. See if that helps.
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
----put code in here ----
Next ws
HTH's



.

Repost: How do you identify a worksheet as last?
 

have you tried using worksheets.count to return the number of sheets
and then call the last sheet by index number...?

Sub SelectLast()
Dim n As Integer
n = ActiveWorkbook.Sheets.Count
Sheets(n).Activate
End Su

--
Message posted from http://www.ExcelForum.com