CODEWITHSUNDEEP
excelvba
Jayra
Jayra

Reputation: 3

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

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!

Upvotes: 0

Views: 478

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

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"))

Upvotes: 1

Related Questions