Changing the Active sheet in a macro

[bearcub]

Hi,

I asked a question last week about filtering out a number of items in a list. However, the sheet that is used for this prois set to the Activesheet:

/

    'master sheet
    Set ws1Master = ActiveSheet  ' --> [B]Can this be changed to a new created sheet?[/B]
[code]/

I am running a series of macros where I am making a copy of the data that was just extracted from out system and removing blanks rows and certain leadership positions. This macro is run when I only have one sheet in the file called Extract. This new sheet, in this case, would be January 2018 since the data is from January.

The next macro I'm running should take the data from the January 2018 sheet and then creates several more sheets - one sheet for each item in a particular column (in this instance it would be all our local offices).

However, this macro, for now, is attached to a button on the Extract sheet. In the code snippet above, is there a way to dynamically change Activesheet to the January 2018. It has to be dynamic because the Extract copy in Feb will be called Feb 2018, etc. 

Is there a way to SetMaster sheet from Activesheet to the newly created copy (e.g. Jan 2018, January 2018, 1/2018, etc) so Excel will know to use this sheet as the source instead of the Extract sheet)?

I was thinking of recording a macro to create a button and attach the macro but I was wondering if there was a more efficient way of doing this.

Thank you for your help.

Michael

 

[Max1616]

I'm a bit confused at what you are asking, but I think what you want is this:

Dim SheetName As String
SheetName = Format(Now, "MMMM") & " " & Format(Now, "YYYY")

Set ws1Master = Sheets(SheetName)

This will set the Sheet to "January 2018" if the current month is Jan, "February 2018" if the month is Feb, etc.

 

[bearcub]

I forgot to mention that this second macro uses an input box to specify the column to filter:

Set objRange = Application.InputBox("Select Field Name To Filter", "Range Input", , , , , , 8)

Is there a way of mapping the objRange to Set wslMaster. Could I add a new line right below this to set the objRange to wsMaster?
Set wsMaster = objRange

 

[bearcub]

Is there a way of linking the input box to wsl variable?

 

[Max1616]

You can try this:

Dim SheetName As String
Dim ws1Master As Worksheet
Dim objRange As Range

SheetName = Format(Now, "MMMM") & " " & Format(Now, "YYYY")
Set ws1Master = Sheets(SheetName)
Set objRange = ws1Master.Range(Application.InputBox("Select Field Name To Filter", "Range Input", , , , , , 8).Address)

 

[bearcub]

thank you Max, that might work. I'll try it and let you know.