VBA Sheet name referenced from a cell

[sacred_hoops]

Hi,

After much use of the forum for answers I seemed to have reached a point where I need some help!

I have a document with a macro that references a sheet titled 'April 2013'. The trouble I have is that every month I need to change the code to the next month's sheet, i.e. 'May 2013'

This is the line of code:

Workbooks("Adaptive MS Schedule.xlsx").Sheets("April"2013").Range("A1:CD313")


Is there a way I can amend the above, so that it references a cell (i.e A1) for the month's name?

I imagine this is an easy thing, but is beyond my knowledge.
Thanks in advance for any help.

Tom

 

[VoG]

Perhaps like this

Workbooks("Adaptive MS Schedule.xlsx").Sheets(Range("A1").Value).Range("A1:CD313")

 

[sacred_hoops]

Thank you VoG for your quick response! That has done the trick. much appreciated.

 

[Jonmo1]

Would it be helpful to just use 'The current month and year' instead of a hard coded month/year in a cell?

Workbooks("Adaptive MS Schedule.xlsx").Sheets(Format(Date,"mmmm yyyy")).Range("A1:CD313")

 

[sacred_hoops]

That would be more ideal but it seems to fail with "Run time error '9' Subscript out of range". It also insist on automatically adding a space after 'date,'.

If you can think of why that may be it would be useful.

I should add that this is part of an advanced filter function (not sure if it makes a difference).

Workbooks("Adaptive MS Schedule.xlsx").Sheets("April2013").Range("A1:CD313"). _        AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("B1:B3"), _
        CopyToRange:=Range("E1:Y1"), Unique:=False

 

[Jonmo1]

In this code, you're looking for a sheet named April2013 <-No spaces

That would be more ideal but it seems to fail with "Run time error '9' Subscript out of range". It also insist on automatically adding a space after 'date,'.

Workbooks("Adaptive MS Schedule.xlsx").Sheets("[COLOR=#ff0000]April2013[/COLOR]").Range("A1:CD313"). _        AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("B1:B3"), _
        CopyToRange:=Range("E1:Y1"), Unique:=False

But in your original post you were looking for a sheet named April 2013 <- With a space
this is a HUGE difference.

I have a document with a macro that references a sheet titled 'April 2013'.

You just need to adjust the format in this code to reflect what your actual sheet names look like.
Workbooks("Adaptive MS Schedule.xlsx").Sheets(Format(Date,"mmmm yyyy")).Range("A1:CD313")
or should it be
Workbooks("Adaptive MS Schedule.xlsx").Sheets(Format(Date,"mmmmyyyy")).Range("A1:CD313")

 

[sacred_hoops]

Ah! You have a very keen eye! I have to refer to two separate documents, one with a space, one without (bit daft I know but out of my hands) - I have mixed the two above.

Thanks for that, it's sorted it out.

 

[Jonmo1]

Such is life with inconsistent data structure.

Glad to help anyway though..