PDA

View Full Version : Solved: Copy/paste from all worksheets, except sheet1



RV6555
09-06-2007, 03:21 AM
I need to do several activities on all worksheets in a workbook, except for sheet1.

The code so far is as follows:

Sub PerformActivities ()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

?I deleted the copy/pasting code here in order to decrease the length of this post

Next ws

End Sub


What happens is that information from several worksheets is collected and copied to sheet1. Therefore the process should not collect information from sheet1 in order not to mess up that sheet. Now I am trying to figure out how to make it look through all worksheets except sheet1 (this is also the name of the sheet). Any suggestions?

rory
09-06-2007, 04:28 AM
You can use:
If Not (ws Is Sheet1) Then

RV6555
09-06-2007, 06:11 AM
Thanks for your reply Rory, the code is:

Sub PerformActivities()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not (ws Is Sheet1) Then
Range("A1") = 1000 'Replaced my code with this line
End If
Next ws
End Sub

For some reason the macro does not go to the next worksheet (even though it says next ws). Any idea why is that?

rory
09-06-2007, 06:22 AM
You need to specify that the Range belongs to ws:
Sub PerformActivities()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not (ws Is Sheet1) Then
ws.Range("A1") = 1000 'Replaced my code with this line
End If
Next ws
End Sub

RV6555
09-06-2007, 06:27 AM
it works, thanks so much. I see that I still need to learn a lot..

rory
09-06-2007, 06:33 AM
No problem. Unless you specify otherwise, the Range and Cells properties refer to the active sheet (or, if they are in the module behind a worksheet, they default to that worksheet).