Questions on VBA

Z

zerofeng

New Member
Joined
Apr 6, 2024
Messages
3
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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

B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
197
bh24524
B
P
  • Question
Edit VBA code to add new attachment on Email
Replies
3
Views
283
Persl
P
F
  • Solved
Copying and pasting onto mastersheet but files have different formats (need if statement help)
2
Replies
12
Views
917
NateSC
NateSC
M
  • Question
Create button with update function for already present sheet
Replies
1
Views
474
SeniorNewbie
SeniorNewbie
S
  • Question
Create a Recursive VBA to call itself and create mass files
Replies
1
Views
501
seigen
S

Forum statistics

Threads
1,215,214
Messages
6,123,664
Members
449,114
Latest member
aides

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