How do you identify if a worksheet is in the last position in a Macro?

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

Natasha

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