macro sheet select
 

I am relatively new to writing macros in VB, so I think the answer to this is
probably real simple. I need to set up a macro that deletes last month's
information from a specific worksheet, run an Advanced Filter (in a different
worksheet) based on information in the first sheet, copy the results, and
then paste them back in the first worksheet. The only problem I have is that
I need the sheet selection (in this case, Sheet1) to be a variable because I
will be running the same macro from multiple sheets. Here is my code:

Selection.AutoFilter Field:=1
Sheets("New Loans").Select
Columns("AD:BE").Select
Selection.delete Shift:=xlToLeft
ActiveWindow.LargeScroll ToRight:=-2
Range("newloans").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets("Sheet1").Range("AD30:AD31"), CopyToRange:=Range("BG2"),
Unique:= _
False
ActiveWindow.SmallScroll ToRight:=6
Range("AE3:AK24").Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
Sheets("Sheet1").Select
Range("A32").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A32").Select
Application.Run "Master2005.xls!hide"
Application.Run "Master2005.xls!formatting"
End Sub

Is there a way I can reference a cell value in the place of Sheet1? Hope
that all made sense. Thanks for your help.

macro sheet select
 

You could create a variable to read the appropriate name from a known
location, then activate the sheet using the variable.
Dim MonthlySheet as String
....
worksheets("Settings").activate
MonthlySheet = range("A1").value
....
worksheets(MonthlySheet).activate
....
HTH. --Bruce

"Dave K" wrote:

I am relatively new to writing macros in VB, so I think the answer to this is
probably real simple. I need to set up a macro that deletes last month's
information from a specific worksheet, run an Advanced Filter (in a different
worksheet) based on information in the first sheet, copy the results, and
then paste them back in the first worksheet. The only problem I have is that
I need the sheet selection (in this case, Sheet1) to be a variable because I
will be running the same macro from multiple sheets. Here is my code:

Selection.AutoFilter Field:=1
Sheets("New Loans").Select
Columns("AD:BE").Select
Selection.delete Shift:=xlToLeft
ActiveWindow.LargeScroll ToRight:=-2
Range("newloans").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets("Sheet1").Range("AD30:AD31"), CopyToRange:=Range("BG2"),
Unique:= _
False
ActiveWindow.SmallScroll ToRight:=6
Range("AE3:AK24").Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
Sheets("Sheet1").Select
Range("A32").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A32").Select
Application.Run "Master2005.xls!hide"
Application.Run "Master2005.xls!formatting"
End Sub

Is there a way I can reference a cell value in the place of Sheet1? Hope
that all made sense. Thanks for your help.

macro sheet select
 

Thanks for your help Bruce. I actually figured it out just before I saw your
post. Here is what worked for me...

Dim dataSheet As Worksheet
Set dataSheet = ActiveSheet

Thanks again.

"bpeltzer" wrote:

You could create a variable to read the appropriate name from a known
location, then activate the sheet using the variable.
Dim MonthlySheet as String
...
worksheets("Settings").activate
MonthlySheet = range("A1").value
...
worksheets(MonthlySheet).activate
...
HTH. --Bruce

"Dave K" wrote:

I am relatively new to writing macros in VB, so I think the answer to this is
probably real simple. I need to set up a macro that deletes last month's
information from a specific worksheet, run an Advanced Filter (in a different
worksheet) based on information in the first sheet, copy the results, and
then paste them back in the first worksheet. The only problem I have is that
I need the sheet selection (in this case, Sheet1) to be a variable because I
will be running the same macro from multiple sheets. Here is my code:

Selection.AutoFilter Field:=1
Sheets("New Loans").Select
Columns("AD:BE").Select
Selection.delete Shift:=xlToLeft
ActiveWindow.LargeScroll ToRight:=-2
Range("newloans").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets("Sheet1").Range("AD30:AD31"), CopyToRange:=Range("BG2"),
Unique:= _
False
ActiveWindow.SmallScroll ToRight:=6
Range("AE3:AK24").Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
Sheets("Sheet1").Select
Range("A32").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A32").Select
Application.Run "Master2005.xls!hide"
Application.Run "Master2005.xls!formatting"
End Sub

Is there a way I can reference a cell value in the place of Sheet1? Hope
that all made sense. Thanks for your help.