VBA Copy Paste to New Sheet based on Month

Hello,

I have data that I would like to paste into a different Workbook's Sheet. The Sheets are categorized by Month ("Jan", "Feb", "Mar"..., "Dec")

My below code prompts me to open the relevant Workbook i.e. 'Sales 2020'. I was wondering I could create a drop down list (showing months) in the original workbook that I can somehow reference in the last line of below code.

HTML Code: 

Does anyone suggest a different method/code to paste data into a different sheet each month without updating Sheet reference in code?
Thank you

I tried the below code to refer to drop down list which is in Worksheet("Month").Range("D2")

But it get the Debug error on line two of code at dd = Workbooks("...filepath….".Worksheet('Month").Range("D2")

Any advise

HTML Code: 

Just explain in a concise manner what you want to do.
Don't leave any steps out.

I want to copy data from one workbook sheet and paste the data in another workbook's sheet. The destination (Sheet) to paste the data changes every month.
I want to have a code that paste's the data in the relevant worksheet without updating my code every month; by creating a drop down list in excel that will reference the sheet name and have my vba code refer to the value selected in drop down list so the data paste
s in relevant Sheet.

Q1: Did the file name is fixed?

if yes, then you may code it in VBA.

Example:
Workbooks.open("\\fnpcplm02\home\243577\VBA practise\Copy 2020-02 TAR Volumes.xlsm")

Q2: Did the Month Change is fixed stlye?

Example:
1. Daily, 29 Feb > Feb, and 1 Mar > Mar
2. Prevous Month, 29 Feb > Jan, 1 Mar > Feb

then you may use below VBA as Month, *Date mean Today
1. Mon = Format(Date, "MMM")
2 EDate = DateSerial(Year(Date), Month(Date) - 1, 1)
Mon = Format(EDate, "MMM")

Dropdown is in Cell E1 in Sheet1 where you copy the data FROM.

Please Login or Register  to view this content.