Define sheet names VBA

[erlisocial]

Hi,

I have over 100 sheets with different names. (ABC, CDE, ENT etc). I have a VBA which requires the sheet selection many times throughout the code. I need to repeat the same code for the next sheet. Rather than me having to rename all the Sheets on my VBA code, is there a way were I can define that name and just change it once at the beginning of my statement and the Sheets to pick up that name and update my VBA code?

Many thanks in advance

Sub Sheet1 ()


''
Sheets("ABC").Select
Sheets("ABC").Select
Sheets("ABC").Select
Sheets("ABC").Select

''
End Sub

 

[alansidman]

If I am understanding correctly, you need to loop through each sheet and perform the same action on each sheet. If this is correct then the following should work

Sub Test()
Dim ws As Worksheet
For Each ws In Worksheets
'...Your action code here
Next ws
End Sub

 

[erlisocial]

If I am understanding correctly, you need to loop through each sheet and perform the same action on each sheet. If this is correct then the following should work

Sub Test()
Dim ws As Worksheet
For Each ws In Worksheets
'...Your action code here
Next ws
End Sub

Thank you for the prompt response. Part of the action code is to select the named sheet.
For example, the first one will have some filters specifically for ABC sheet to paste the data there. The next code, CDE will have different filters and paste the data to CDE etc.

 

[erlisocial]

If I am understanding correctly, you need to loop through each sheet and perform the same action on each sheet. If this is correct then the following should work

Sub Test()
Dim ws As Worksheet
For Each ws In Worksheets
'...Your action code here
Next ws
End Sub

Is not a loop. Let me try to re-explain it.

Sheet Name : Blue
Sheet Name: Red
Sheet Name: Green
Sheet Name: Data

I have a vba that looks in the SheetName Data, filters for some data with the string BLUE, copies it, goes to sheet BLUE and pastes it.
I need to do the same for the RED, filter for RED, go to Sheet Named RED and paste the data.

Many thanks in advance

 

[alansidman]

I'm out of answers for this issue.

 

[Fluff]

Is this what you mean

Dim wsRed As Worksheet, wsBlue As Worksheet, wsGreen As Worksheet

Set wsRed = Sheets("Red")
Set wsBlue = Sheets("Blue")
Set wsGreen = Sheets("Green")

 

[erlisocial]

Is this what you mean

Dim wsRed As Worksheet, wsBlue As Worksheet, wsGreen As Worksheet

Set wsRed = Sheets("Red")
Set wsBlue = Sheets("Blue")
Set wsGreen = Sheets("Green")

Hi

Thank you for your reply. In my code I have 3 time "Sheets("Red"). Select" . If I want to copy and paste the same code to a new Module, I will change the red to a different name say Blue. I will need to manually change all the "Sheets("Red"). Select" to "Sheets("Blue"). Select" . Rather than doing that is it possible to do the following:

Set X as Name
X= "RED" (The only value I should have to change)

And where I have "Sheets("Red").Select" to be replaced with "Sheets("X").Select"

Sub TestCopy_20()
Dim RedFilter As String

''
''
RedFilter = "Red"

' Unprotect destination sheet
Sheets("Red").Select
    ActiveSheet.Unprotect

Sheets("Data").Select
''
ActiveSheet.Range("B1:AD1").AutoFilter
ActiveSheet.Range("B1:AD100").AutoFilter Field:=3, Criteria1:=RedFilter
''
'Copy filtered table and paste it in Destination cell.
ActiveSheet.Range("B2:AD100").SpecialCells(xlCellTypeVisible).Copy
Sheets("Red").Select
ActiveSheet.Range("B3").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
''
'Remove filter that was applied.
ActiveSheet.AutoFilterMode = False
Range("B2").Select
Sheets("Red").Select
    ActiveSheet.Protect

''
End Sub

 

[alansidman]

How about an input box that changes the sheet name?

Sub Test()
Dim ws As Worksheet
Set ws = InputBox("What Sheet to Name?")
'use ws to identify your sheet with your actions
'ie.  with ws
' Your code for the worksheet

End Sub

 

[Fluff]

Just change Sheets("Red"). to Sheets(RedFilter)

 

[erlisocial]

Just change Sheets("Red"). to Sheets(RedFilter)

Legend!!! Thank you thought could only use ut on the filtering lol