Copy data range to a dynamic target worksheet and range

AccountantHarry

New Member
Joined
Oct 13, 2019
Messages
18
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.

Code:
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
 
Upvote 0
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top