Repost: How do you identify a worksheet as last?

  • Thread starter Thread starter Natasha
  • Start date Start date
N

Natasha

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

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
 
for each sh in thisworkbook.worksheets
if sh.name <> worksheets("Master").Name then
sh.Range("A1:A25").copy

worksheets("Master").Range("A65000").End(xlup).Offset(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..
 
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
 
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.
 
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
 
Back
Top