Macro for copying data from one workbook to another under certain conditions

[kitkat12]

I have two workbooks, Workbook A and Workbook B. Workbook A has data that I need to input into a specific worksheet (let's call it "Data") in Workbook B. The data I need from Workbook A is in A18:AM18. I need it copied into Workbook B in the "Data" worksheet. However I need to do this each month, and the location where the data needs to go changes because it is updated each month. I would like to post the data to the first empty row starting at D and going to AP. In the same row, I would like to input "APAC" into column C, and pull down the existing formulas from the cells above in columns A and B. I also need this to be created into a button so I can just push it each month and the report updates.

Thank you very much for your help in advance!

 

[Declamatory]

Hi, Try this. It assumes that workbook B is closed when the macro is run.

   Workbooks.Open Filename:="I:\_desktop\Workbook B.xlsx"
    Sheets("Data").Select
    ActiveWindow.SmallScroll Down:=-15
    Range("D1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Windows("Workbook A.xlsx").Activate
    Range("A18:AM18").Select
    Selection.Copy
    Windows("Workbook B.xlsx").Activate
    ActiveSheet.Paste

 

[kitkat12]

I'm getting an "Run-time error '1004'". It's telling me that the file in the first line of code cannot be found. I made sure to put the file on my desktop and change the name.

 

[Declamatory]

it doesn't need to be on your desktop. The filepath in the code just needs to match the filepath where your spreadsheet was saved. My desktop might be a different filepath to your desktop. E.g. my desktop is on my I: drive but it may be C: drive on your desktop.

 

[kitkat12]

Ok thank you!

 

[kitkat12]

How would I rewrite this code so that it is sourced in Workbook B?