Questions on VBA

[zerofeng]

hi all, I've created VBA for my monthly reporting below.
i would like to seek help if anyone could help to simplify the VBA code below.
i have 2 workbooks (master & report). Master is running pivot query everyday from other sources and report will be using below VBA code to captured the data from master.
thanks

Sub COPY_EXAMPLE()

Dim master As Workbook
Dim report As Workbook
Set master = Workbooks("Daily Report 2024.xlxm")
se report = Workbooks("Reporting 2024.xlsx")


    Application.DisplayAlerts = False
    
    'summary sheet
    
    master.Worksheets("summary").Range("D17:O29").Copy
    report.Activate
    report.workshets("2024").Range("C15:N27").PasteSpecial Paste:=xlPasteValues
    
    master.Worksheets("summary").Range("D39:O51").Copy
    report.Activate
    report.workshets("2024").Range("C34:N46").PasteSpecial Paste:=xlPasteValues
    
    
    'Jan 24
    
    master.Worksheets("JAN24").Range("B5:AH17").Copy
    report.Activate
    report.Worksheets("JAN").Range("D5:AH17").PasteSpecial Paste:=xlPasteValues
    
    master.Worksheets("JAN24").Range("C24:028").Copy
    report.Activate
    report.Worksheets("JAN").Range("W24:AI28").PasteSpecial Paste:=xlPasteValues
    
    
    'Feb 24
    
    master.Worksheets("FEB24").Range("B5:AH17").Copy
    report.Activate
    report.Worksheets("FEB").Range("D5:AH17").PasteSpecial Paste:=xlPasteValues
    
    master.Worksheets("FEB24").Range("C24:028").Copy
    report.Activate
    report.Worksheets("FEB").Range("W24:AI28").PasteSpecial Paste:=xlPasteValues
    
    'Mar 24
    
    master.Worksheets("MAR24").Range("B5:AH17").Copy
    report.Activate
    report.Worksheets("MAR").Range("D5:AH17").PasteSpecial Paste:=xlPasteValues
    
    master.Worksheets("MAR24").Range("C24:028").Copy
    report.Activate
    report.Worksheets("MAR").Range("W24:AI28").PasteSpecial Paste:=xlPasteValues
    
    
    'Apr 24
    
    master.Worksheets("APR24").Range("B5:AH17").Copy
    report.Activate
    report.Worksheets("APR").Range("D5:AH17").PasteSpecial Paste:=xlPasteValues
    
    master.Worksheets("APR24").Range("C24:028").Copy
    report.Activate
    report.Worksheets("APR").Range("W24:AI28").PasteSpecial Paste:=xlPasteValues
    
    
    Application.DisplayAlerts = True
    
End Sub

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this:

Sub COPY_EXAMPLE()
  Dim master As Workbook, report As Workbook
  Dim sMonth As String
  Dim i As Long
  Application.DisplayAlerts = False
 
  Set master = Workbooks("Daily Report 2024.xlsm")
  Set report = Workbooks("Reporting 2024.xlsx")
   
  'summary sheet
  master.Worksheets("summary").Range("D17:O29").Copy
  report.Worksheets("2024").Range("C15:N27").PasteSpecial Paste:=xlPasteValues
  master.Worksheets("summary").Range("D39:O51").Copy
  report.Worksheets("2024").Range("C34:N46").PasteSpecial Paste:=xlPasteValues
 
  For i = 1 To 4    'Change to month number, 5 to may, 6 to jun, etc...
    sMonth = Format(DateSerial(Year(Date), i, 1), "mmm")
    master.Sheets(sMonth & Year(Date)).Range("B5:AH17").Copy
    report.Worksheets(sMonth).Range("D5:AH17").PasteSpecial Paste:=xlPasteValues
    master.Worksheets(sMonth & Year(Date)).Range("C24:O28").Copy
    report.Worksheets(sMonth).Range("W24:AI28").PasteSpecial Paste:=xlPasteValues
  Next
 
  Application.DisplayAlerts = True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[zerofeng]

Hi Dante,

Thanks for the coding. No issue on the summary. However, RunTime error 9 happened from coding below.
I was unable to run the monthly data.
Seek for your further assistances.
Thanks.


Master.sheets(sMonth & Year(Date)).range("B5:AH17").copy.

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this:

Sub COPY_EXAMPLE()
  Dim master As Workbook, report As Workbook
  Dim sMonth As String
  Dim i As Long
  Application.DisplayAlerts = False
 
  Set master = Workbooks("Daily Report 2024.xlsm")
  Set report = Workbooks("Reporting 2024.xlsx")
  
  'summary sheet
  master.Worksheets("summary").Range("D17:O29").Copy
  report.Worksheets("2024").Range("C15:N27").PasteSpecial Paste:=xlPasteValues
  master.Worksheets("summary").Range("D39:O51").Copy
  report.Worksheets("2024").Range("C34:N46").PasteSpecial Paste:=xlPasteValues
 
  For i = 1 To 4    'Change to month number, 5 to may, 6 to jun, etc...
    sMonth = Format(DateSerial(Year(Date), i, 1), "mmm")
    master.Sheets(sMonth & Year(Date)).Range("B5:AH17").Copy
    report.Worksheets(sMonth).Range("D5:AH17").PasteSpecial Paste:=xlPasteValues
    master.Worksheets(sMonth & Year(Date)).Range("C24:O28").Copy
    report.Worksheets(sMonth).Range("W24:AI28").PasteSpecial Paste:=xlPasteValues
  Next
 
  Application.DisplayAlerts = True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

,

 

[DanteAmor]

Run the macro again, when the error occurs, press the debug button, then bring the mouse pointer closer to the sMonth variable, a small window should appear with the value that the variable has at the time of the error. Then you tell me what value appears in the window.

You must have a sheet with that name.

 

[zerofeng]

Run the macro again, when the error occurs, press the debug button, then bring the mouse pointer closer to the sMonth variable, a small window should appear with the value that the variable has at the time of the error. Then you tell me what value appears in the window.

You must have a sheet with that name.

I change the coding below and issue resolved.
The sheet title become "mmm24".
Thanks.

Master.sheets(sMonth & 24).range("B5:AH17").copy.

 

[DanteAmor]

The sheet title become "mmm24".

That's correct, the year is 2 digits and I was trying with 4 digits.

So:

Sub COPY_EXAMPLE()
  Dim master As Workbook, report As Workbook
  Dim sMonth As String
  Dim i As Long
  Application.DisplayAlerts = False
 
  Set master = Workbooks("Daily Report 2024.xlsm")
  Set report = Workbooks("Reporting 2024.xlsx")
   
  'summary sheet
  master.Worksheets("summary").Range("D17:O29").Copy
  report.Worksheets("2024").Range("C15:N27").PasteSpecial Paste:=xlPasteValues
  master.Worksheets("summary").Range("D39:O51").Copy
  report.Worksheets("2024").Range("C34:N46").PasteSpecial Paste:=xlPasteValues
 
  For i = 1 To 4    'Change to month number, 5 to may, 6 to jun, etc...
    sMonth = Format(DateSerial(Year(Date), i, 1), "mmm")
    master.Sheets(sMonth & "24").Range("B5:AH17").Copy
    report.Worksheets(sMonth).Range("D5:AH17").PasteSpecial Paste:=xlPasteValues
    master.Worksheets(sMonth & "24").Range("C24:O28").Copy
    report.Worksheets(sMonth).Range("W24:AI28").PasteSpecial Paste:=xlPasteValues
  Next
 
  Application.DisplayAlerts = True
End Sub