How to combine two VBA codes

D

Dokat

Active Member
Joined
Jan 19, 2015
Messages
304
Office Version
  1. 365
Hi,

I'd like to combine below two codes together. Can someone help me with how to combine them.

VBA Code: 
Sub MoveRangeWBC()
Worksheets("WBC").Range("E25:E60").Copy Destination:=Worksheets("Summary").Range("C4")
Worksheets("WBC").Range("W25:W60").Copy Destination:=Worksheets("Summary").Range("D4")
Worksheets("WBC").Range("E25:E60").Copy Destination:=Worksheets("Summary").Range("C39")
Worksheets("WBC").Range("AB25:AB60").Copy Destination:=Worksheets("Summary").Range("D39")

Dim iCntr
Dim rng As Range
Set rng = Worksheets("Summary").Range("B4:F1111")

For iCntr = rng.Row + rng.Rows.Count - 1 To rng.Row Step -1

If Application.WorksheetFunction.CountA(Rows(iCntr)) = 0 Then Rows(iCntr).EntireRow.Delete

Next
With Worksheets("WBC")
.Range("W22").Copy Destination:=Worksheets("Summary").Range("B4:B33")
.Range("AB22").Copy Destination:=Worksheets("Summary").Range("B34:B64")


End With
End Sub


Code: 
Sub ChangeHeaders()
With Worksheets("WBC")
.Range("W22").Copy Destination:=Worksheets("ST").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("ST").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("WBA").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("WBA").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("WRO").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("WRO").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("WGI").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("WGI").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("WFP").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("WFP").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("WGP").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("WGP").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("WMP").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("WMP").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("MPM").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("MPM").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("MTM").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("MTM").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("MHI").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("MHI").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("MDW").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("MDW").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("CFP").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("CFP").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("CPE").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("CPE").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("CWC").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("CWC").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("RBO").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("RBO").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("RPE").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("RPE").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("RTA").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("RTA").Range("AB22")
.Range("W22").Copy Destination:=Worksheets("OSA").Range("W22")
.Range("AB22").Copy Destination:=Worksheets("OSA").Range("AB22")

End With
End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Wherever you want ChangeHeaders to run in the first code put the line below in

VBA Code: 
Call ChangeHeaders
 
Upvote 0
AccelUser said:
Wherever you want ChangeHeaders to run in the first code put the line below in

VBA Code: 
Call ChangeHeaders
Click to expand...
Thinking of it, might be better to run macros in order rather than combining them to keep it simple. Thanks
 
Upvote 0
The following should run much quicker than the original:

VBA Code: 
Sub ChangeHeaders()
'
    Dim WBC_W22 As Variant
    Dim WBC_AB22 As Variant
'
    WBC_W22 = Worksheets("WBC").Range("W22")
    WBC_AB22 = Worksheets("WBC").Range("AB22")
'
    Worksheets("ST").Range("W22") = WBC_W22
    Worksheets("ST").Range("AB22") = WBC_AB22
'
    Worksheets("WBA").Range("W22") = WBC_W22
    Worksheets("WBA").Range("AB22") = WBC_AB22
'
    Worksheets("WRO").Range("W22") = WBC_W22
    Worksheets("WRO").Range("AB22") = WBC_AB22
'
    Worksheets("WGI").Range("W22") = WBC_W22
    Worksheets("WGI").Range("AB22") = WBC_AB22
'
    Worksheets("WFP").Range("W22") = WBC_W22
    Worksheets("WFP").Range("AB22") = WBC_AB22
'
    Worksheets("WGP").Range("W22") = WBC_W22
    Worksheets("WGP").Range("AB22") = WBC_AB22
'
    Worksheets("WMP").Range("W22") = WBC_W22
    Worksheets("WMP").Range("AB22") = WBC_AB22
'
    Worksheets("MPM").Range("W22") = WBC_W22
    Worksheets("MPM").Range("AB22") = WBC_AB22
'
    Worksheets("MTM").Range("W22") = WBC_W22
    Worksheets("MTM").Range("AB22") = WBC_AB22
'
    Worksheets("MHI").Range("W22") = WBC_W22
    Worksheets("MHI").Range("AB22") = WBC_AB22
'
    Worksheets("MDW").Range("W22") = WBC_W22
    Worksheets("MDW").Range("AB22") = WBC_AB22
'
    Worksheets("CFP").Range("W22") = WBC_W22
    Worksheets("CFP").Range("AB22") = WBC_AB22
'
    Worksheets("CPE").Range("W22") = WBC_W22
    Worksheets("CPE").Range("AB22") = WBC_AB22
'
    Worksheets("CWC").Range("W22") = WBC_W22
    Worksheets("CWC").Range("AB22") = WBC_AB22
'
    Worksheets("RBO").Range("W22") = WBC_W22
    Worksheets("RBO").Range("AB22") = WBC_AB22
'
    Worksheets("RPE").Range("W22") = WBC_W22
    Worksheets("RPE").Range("AB22") = WBC_AB22
'
    Worksheets("RTA").Range("W22") = WBC_W22
    Worksheets("RTA").Range("AB22") = WBC_AB22
'
    Worksheets("OSA").Range("W22") = WBC_W22
    Worksheets("OSA").Range("AB22") = WBC_AB22
End Sub
 
Upvote 0
johnnyL said:
The following should run much quicker than the original:

VBA Code: 
Sub ChangeHeaders()
'
    Dim WBC_W22 As Variant
    Dim WBC_AB22 As Variant
'
    WBC_W22 = Worksheets("WBC").Range("W22")
    WBC_AB22 = Worksheets("WBC").Range("AB22")
'
    Worksheets("ST").Range("W22") = WBC_W22
    Worksheets("ST").Range("AB22") = WBC_AB22
'
    Worksheets("WBA").Range("W22") = WBC_W22
    Worksheets("WBA").Range("AB22") = WBC_AB22
'
    Worksheets("WRO").Range("W22") = WBC_W22
    Worksheets("WRO").Range("AB22") = WBC_AB22
'
    Worksheets("WGI").Range("W22") = WBC_W22
    Worksheets("WGI").Range("AB22") = WBC_AB22
'
    Worksheets("WFP").Range("W22") = WBC_W22
    Worksheets("WFP").Range("AB22") = WBC_AB22
'
    Worksheets("WGP").Range("W22") = WBC_W22
    Worksheets("WGP").Range("AB22") = WBC_AB22
'
    Worksheets("WMP").Range("W22") = WBC_W22
    Worksheets("WMP").Range("AB22") = WBC_AB22
'
    Worksheets("MPM").Range("W22") = WBC_W22
    Worksheets("MPM").Range("AB22") = WBC_AB22
'
    Worksheets("MTM").Range("W22") = WBC_W22
    Worksheets("MTM").Range("AB22") = WBC_AB22
'
    Worksheets("MHI").Range("W22") = WBC_W22
    Worksheets("MHI").Range("AB22") = WBC_AB22
'
    Worksheets("MDW").Range("W22") = WBC_W22
    Worksheets("MDW").Range("AB22") = WBC_AB22
'
    Worksheets("CFP").Range("W22") = WBC_W22
    Worksheets("CFP").Range("AB22") = WBC_AB22
'
    Worksheets("CPE").Range("W22") = WBC_W22
    Worksheets("CPE").Range("AB22") = WBC_AB22
'
    Worksheets("CWC").Range("W22") = WBC_W22
    Worksheets("CWC").Range("AB22") = WBC_AB22
'
    Worksheets("RBO").Range("W22") = WBC_W22
    Worksheets("RBO").Range("AB22") = WBC_AB22
'
    Worksheets("RPE").Range("W22") = WBC_W22
    Worksheets("RPE").Range("AB22") = WBC_AB22
'
    Worksheets("RTA").Range("W22") = WBC_W22
    Worksheets("RTA").Range("AB22") = WBC_AB22
'
    Worksheets("OSA").Range("W22") = WBC_W22
    Worksheets("OSA").Range("AB22") = WBC_AB22
End Sub
Click to expand...
Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Inefficient code taking too long to run
2 3 4
Replies
31
Views
2K
mumps
mumps
D
  • Question
Run multiple macros in order
Replies
6
Views
1K
Joe4
Joe4

Forum statistics

Threads
1,215,261
Messages
6,123,939
Members
449,134
Latest member
NickWBA

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