VBA code to copy from a variable sheet name based on cell value

Question

Hoping I might get some help as I'm a bit lost!

I have a Master template and a user template. Multiple (10+) users will be submitting their worksheets.

The users have a "Submission" tab that they complete. When they submit their worksheet, it creates a new tab in their file, and pastes the values of their submission in the new tab (so that formulae aren't retained) and names the new tab based on the monthly submission it corresponds to i.e. 'Jan-19'

I am then looking to copy a specific column of their submission into my master file using a macro, however in January I will copy from the Jan-19 tab, and in February I will copy from the Feb-19 tab. Therefore, in my macro, I am struggling to come up with the right code where the Sheet name will vary based on the month name.

Currently I have the following code:

Dim OpenFileName As String
Dim wb As Workbook
'Select and Open workbook
OpenFileName = Application.GetOpenFilename(",*.xlsm")
If OpenFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(OpenFileName)


ThisWorkbook.Sheets("User 1").Range("B19:B36").Value = wb.Sheets("***Variable month name***").Range("B19:B36").Value
ThisWorkbook.Sheets("Control").Range("D15") = Now

wb.Close

Any help you can provide would be much appreciated!

Siddharth Rout · Accepted Answer · 2020-02-03 07:00:08Z

1

For current month use

Format(Now,"mmm-yy")

This will give you Feb-20. So incorporating it in your code

wb.Sheets(Format(Now,"mmm-yy"))

For a specific month/year use

Dim dt As Date
Dim m As Long, y As Long

m = 2 '<~~ Feb (Month)
y = 2019 '<~~ Year

dt = DateSerial(y, m, 1)

Usage would be

wb.Sheets(Format(dt,"mmm-yy"))

answered Feb 3, 2020 at 7:00

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA code to copy from a variable sheet name based on cell value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related