Copying data from Variable Worksheet Names

[KathrynJ]

Each month I need to copy a report that uses the same workbook each month, but with a different worksheet each month (i.e., 1-16 to 2-15-17; 2-16 to 3-15-17; etc.). On the workbook where I want to paste the data, I've added a field with an LOV to select the correct worksheet name each month. But I don't know how to write the code to find that value. Here's what I have so far:

Dim MyPath2 As String
Dim MyFile2 As String
Dim LatestFile2 As String
Dim LatestDate2 As Date
Dim LMD2 As Date
Dim Workbook_Name As String
Dim Worksheet_Name As String
Workbook_Name = ThisWorkbook.Name

(in between, I've defined the values for the variables)

Workbooks.Open MyPath2 & LatestFile2

Sheets(" ").Select - this is where I'm stuck

Range("A2:H" & Range("C" & Rows.Count).End(xlUp).Row).Copy
Windows(Workbook_Name).Activate
Sheets("Pass Plus").Range("D8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

 

[SirJB7]

Hi, KathrynJ!
Does this help? Replace the string variables with proper values.

    sMonthlyBook=Range("LOVWB").Value
    sMonthlySheet=Range("LOVWS").Value
    Workbooks(sMonthlyBook).Activate
    Worksheets(sMonthlySheet).Activate
    Range(sMonthlyRange).Select
    Selection.Copy
    Workbooks(sReportBook).Activate
    Worksheets(sReportSheet).Activate
    Range(sReportrange).Select
    ActiveSheet.Paste

Regards!

 

[KathrynJ]

I've been trying, but still don't quite get this. I forgot to mention that the path to the file also includes an LOV to select the correct folder (which also changes each month). So I'm confused on how to use LOV to select the new folder, and another LOV to select the new worksheet. Both of the LOVs are on the master workbook/worksheet into which I paste the new data.

 

[SirJB7]

Hi, KathrynJ!
Consider uploading a sample file. It'd be easier to understand for people who might be able to help you.
Include a manually written output of the requested solution. Thanks.
Regards!