Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you identify if a worksheet is in the last position in a Macro?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you identify if a worksheet is in the last position in a Macro?
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 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you identify if a worksheet is in the last position in a Macro?
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 -----Original 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add worksheet at end or last position | Excel Discussion (Misc queries) | |||
Worksheet Tas position | Excel Discussion (Misc queries) | |||
Need to identify active worksheet in macro | Excel Worksheet Functions | |||
Position Picture with macro | Setting up and Configuration of Excel | |||
Set position into a worksheet using vba | Charts and Charting in Excel |