Would like to run this macro for 2 sheets on the references i gave. Any help would be great thank you.

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,076
Office Version
  1. 365
Platform
  1. Windows
Code:
Sub Macro2()
'
' Macro2 Macro

    Columns("P:Q").Select 'Im inserting 2 columns in P and Q because OT was the last in column O4
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("P4").Select
    ActiveCell.FormulaR1C1 = "7/3/2016" ' Entered next sunday date
    Range("Q4").Select
    ActiveCell.FormulaR1C1 = "OT" 'Added OT in next column
    Range("N6:O12").Select ' copied this so i can pastes the formulas and formats i need
    Selection.Copy
    Range("P6").Select
    Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False ' Paste Special cause i need formulas
    Range("N6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False   ' Paste values back
    Application.CutCopyMode = False
    Range("Y6").Select
    ActiveCell.FormulaR1C1 = _
        "=+RC[-22]+RC[-20]+RC[-18]+RC[-16]+RC[-14]+RC[-12]+RC[-10]+RC[-8]" ' has to add formula on just this +Q6
    Range("Y6").Select
    Selection.AutoFill Destination:=Range("Y6:Y12"), Type:=xlFillDefault ' Drag formulas down
    Range("Y6:Y12").Select
    Range("Q4").Select
End Sub
' Reference' i would like to run this macro cause some sheets can be different. Date and OT will always be
on line 4 i need it added after the letters OT in Number line 4 wheni run macro.
'Copy and paste will always start from Line 6 and end i row before the word Subtotal "Column A Reference
'Will always need to add forumla for under "OT Hours Subtotal". Because when i insert 2 columns at the beggining that column down to the right
doesnt add the formula. So example OT thats in Q6 need to add a formula in OT Hours Subtotal like +Q6 then drag it down like you can
see in my code. I would have to do the same if i ran this macro again. So it needs to know to add and drag formula down under heading "OT Hours Subtotal"

Thanks for any Help..

https://www.dropbox.com/s/b37mfdiuxpn6prf/Test Image.xlsm?dl=0
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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