Questions on VBA

Z

zerofeng

New Member
Joined
Apr 6, 2024
Messages
10
Office Version
  1. 2021
Platform
  1. Windows
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

VBA Code: 
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this:

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

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

Try this:

VBA Code: 
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
----- --
Click to expand...
,
 
Upvote 0
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.
 
Upvote 0
DanteAmor said:
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.
Click to expand...
I change the coding below and issue resolved.
The sheet title become "mmm24".
Thanks.

Master.sheets(sMonth & 24).range("B5:AH17").copy.
 
Upvote 0
zerofeng said:
The sheet title become "mmm24".
Click to expand...
That's correct, the year is 2 digits and I was trying with 4 digits.

So:
VBA Code: 
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

😅
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Vba - create new sheet and copy date into it.
Replies
1
Views
104
zerofeng
Z
Z
  • Solved
Run-time error 9
2
Replies
12
Views
213
zerofeng
Z
N
  • Question Question
Excel VBA copy data to a worksheet in list form
Replies
0
Views
132
NewbieExcel123
N
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
702
bh24524
B
P
  • Question Question
Edit VBA code to add new attachment on Email
Replies
3
Views
472
Persl
P

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

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