How to reference to a specific sheet?

[aadeshgandhi]

Hello,
I know the title is not all that descriptive, I hope the explanation will help. I am trying to edit a macro that I have recorded earlier. This is first bit which I assume will work fine.

Dim WS As Worksheet
    Application.ScreenUpdating = False
    Sheets("SWES").Activate
    For Each WS In Worksheets
    If WS.Name <> "SWES" Then
    Range("A8:U8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("SWES").Select
    Sheets("Sheet2").Visible = True
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste

Now below this there are number of steps that clean the data in Sheet 2, once the data is cleaned I would like to paste it back into the sheet from where it was picked up and I do not know what that command would be. Can anybody please help me?

Thanks,
Aadesh

[BigBas]

Aadesh,

The question is a bit vague. You can set up variables for each sheet, and use those as the reference. For example:

Sub Testies
Dim ws1 as Worksheet
Dim ws2 as Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

ws2.Range("A1:A10").Copy ws1.Range("A1")
End sub

DOes this help?

[aadeshgandhi]

Not really BigBas, I will try to explain it further.
Right now what I have this One sheet named SWES and then multiple sheets names SWIS 1, SWIS 2 and so on... In the present sheet, I have a macro that works fine when I run it on SWIS 1, SWIS 2 etc. So if there are 50 sheets the user had to run the macro 50 times. I am trying to minimize that to one by creating a macro that will loop through the workbook.

The code below is what I have now and it works fine

Dim WSName As String
WSName = ActiveSheet.Name
    Range("A8:U8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets(WSName).Select
    Sheets("Sheet2").Visible = True
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
CODE TO EDIT AND CLEAN THE DATA
Sheets(WSName).Select
Range("A12:B12").Select

This is how I would like the macro to work
Macro should run from SWES sheet, it will then go to SWIS 1 copy the relevant data, paste and edit in Sheet 2 and copy back the edited data in SWIS 1. then move to next sheet.

This is code below that I have started to work on but do not know what should go in those ?????

Dim WS As Worksheet
    Application.ScreenUpdating = False
    Sheets("SWES").Activate
    For Each WS In Worksheets
    If WS.Name <> "SWES" Then
    Range("A8:U8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("SWES").Select
    Sheets("Sheet2").Visible = True
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
CODE TO EDIT AND CLEAN THE DATA
Sheets(?????).Select
    Range("A12:B12").Select

I hope this helps.

Aadesh.

[Pichingualas]

Try with:

Dim WS As Worksheet
    Application.ScreenUpdating = False
    Sheets("SWES").Activate
    For Each WS In Worksheets
    If WS.Name <> "SWES" Then
    Range("A8:U8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("SWES").Select
    Sheets("Sheet2").Visible = True
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
CODE TO EDIT AND CLEAN THE DATA
Sheets(WS.Name).Select
    Range("A12:B12").Select

[aadeshgandhi]

Hi Pichingualas,
That did not work and I also observed that instead of copying the data from other sheets it macro copied the data from SWES sheet which should not have happened. Any other ideas.


Thanks,
Aadesh.

[Pichingualas]

Try this:

Dim WS As Worksheet
    Application.ScreenUpdating = False
    For Each WS In Worksheets
    Sheets(WS.Name).Activate
    If WS.Name <> "Sheet2" Then
    Range("A8:U8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet2").Visible = True
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
CODE TO EDIT AND CLEAN THE DATA
Sheets(WS.Name).Select
    Range("A12:B12").Select