macro sheet select

[Dave K]

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.

[bpeltzer]

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.

[Dave K]

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.