Copy data range to a dynamic target worksheet and range

[AccountantHarry]

Hi everyone,

I am a VBA beginner, I have written a macro using codes from different threads here and watching youtube videos, it sounds easy to copy and paste data, however, there are some tricky requirements as the source workbook, the target worksheet and target range change based on the month of the year. For example in Sep:-

The source workbook is C:\Sales data\Sept 2019.xls. The name of the file will be Oct.xls next month. I have managed to make the file name changed based on a cell in a different workbook.

The source worksheet in this workbook is called "Actual", the name and the range "C4:C52", they don't change.

The target worksheet in this workbook is "Sep", but it will be "Oct" next month, so forth and so on.
The target range in this workbook is "J4:J52". but it will be "K4:K52" in Oct, so for and so on.

I have the following codes:
​

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub CopyPasge()
Application.DisplayAlerts = False[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
'Open Sales data workbook
Sheets("Macro set up").Select
Workbooks.Open(Filename:= _
Range("B6").Value _
, UpdateLinks:=0).RunAutoMacros Which:=xlAutoOpen[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
'Go to worksheets Actual, select data range and paste as value in current month's tab.
Worksheets("Actual").Range("C3:C52").Copy[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
' change the month and range each month, eg worksheets ("Oct").Range("K4")
Worksheets("Sep").Range("J4").PasteSpecial xlPasteValues[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Application.CutCopyMode = False

End sub
[/FONT]

I change the macro codes for the target sheet name and range each month. I have tried to make them to base on a cell in the workbook I used for the file name, but without success. Any help and suggestions would be greatly appreciated.

Regards

 

[John_w]

The source workbook is C:\Sales data\Sept 2019.xls. The name of the file will be Oct.xls next month.

These file names are not consistent - the first has 4 letters for the month and the year is specified, whereas the second has 3 letters for the month and no year.

With the file names as Sep 2019.xls and Oct 2019.xls, this macro copies values from the source workbook depending on the actual date you run the macro. So if you run the macro any time in October 2019 it will copy from workbook "Oct 2019.xls", sheet "Actual", cells C4:C52 to sheet "Oct" cells "K4:K52" in the macro workbook.

Public Sub Copy_Month_Data()

    Dim sourceWb As Workbook
    
    Set sourceWb = Workbooks.Open("C:\Sales data\" & Format(Date, "Mmm yyyy") & ".xls")
    ThisWorkbook.Worksheets(Format(Date, "Mmm")).Cells(4, 1 + Month(Date)).Resize(49, 1).Value = sourceWb.Worksheets("Actual").Range("C4:C52").Value
    sourceWb.Close False
    
End Sub

 

[AccountantHarry]

Hi

Thanks for your reply. The macro is not always run in the month when the monthend is, eg for Oct, it can be run on the last couple of days in Oct and the first 10 working days of Nov.

Regards

 

[John_w]

In that case we can use your idea of reading the required month from a cell. In this code, cell A2 in sheet "Macro set up" in the macro workbook is expected to contain any date in the required month and year.

Public Sub Copy_Month_Data1A()

    Dim sourceDate As Date
    Dim sourceWb As Workbook
    
    sourceDate = ThisWorkbook.Worksheets("Macro set up").Range("A2").Value
    
    Set sourceWb = Workbooks.Open("C:\Sales data\" & Format(sourceDate, "Mmm yyyy") & ".xls")
    ThisWorkbook.Worksheets(Format(sourceDate, "Mmm")).Cells(4, 1 + Month(sourceDate)).Resize(49, 1).Value = sourceWb.Worksheets("Actual").Range("C4:C52").Value
    sourceWb.Close False
    
End Sub

 

[AccountantHarry]

Hi

Sorry for the late respond, I have been away.

It opens the workbook find but can get passed this line:
ThisWorkbook.Worksheets(Format(sourceDate, "Mmm")).Cells(4, 1 + Month(sourceDate)).Resize(49, 1).Value = sourceWb.Worksheets("Actual").Range("C4:C52").Value

Run-time error 9 Subscript out of range. Also, what does this line mean? I can't see copy and paste as value in the codes.

Regards

 

[AccountantHarry]

Hi

I have amended the codes as follows and it works now. Thanks so much for your help

Worksheets("Actual").Range("C4:C52").Copy
Worksheets(Format(sourceDate, "Mmm")).Cells(4, 1 + Month(sourceDate)).PasteSpecial xlPasteValues