Copy data range to a dynamic target worksheet and range

AccountantHarry

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,960
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
 

AccountantHarry

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,960
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
 

AccountantHarry

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

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

Forum statistics

Threads
1,078,541
Messages
5,341,061
Members
399,414
Latest member
EMW2159

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top