0

I created this macro to format a specific kind of document that I pull out of a database. It was successful when applied to the original spreadsheet, however, when I try to apply it to other workbooks, it still only works on the original one.

Just to give you some background information on the code, I am supposed to sum up every four 15-minute intervals to calculate (24) hourly intervals for each day, then delete the original 15-minute intervals and delete/shift some columns around.

This is my first time working with VBA so the code looks kind of stupid, but please let me know how I can fix this:

Sub MoveCopyRowsColumns()
Columns("CQ").Cut
Columns("K").Insert
End Sub

Sub DeleteExtraColumns()
Columns("DG:DJ").Delete
Columns("BK:BL").Delete
Columns("AH").Delete
End Sub

Sub InsertColumns()
Range("P:P").EntireColumn.Insert
Range("U:U").EntireColumn.Insert
Range("Z:Z").EntireColumn.Insert
Range("AE:AE").EntireColumn.Insert
Range("AJ:AJ").EntireColumn.Insert
Range("AO:AO").EntireColumn.Insert
Range("AT:AT").EntireColumn.Insert
Range("AY:AY").EntireColumn.Insert
Range("BD:BD").EntireColumn.Insert
Range("BI:BI").EntireColumn.Insert
Range("BN:BN").EntireColumn.Insert
Range("BS:BS").EntireColumn.Insert
Range("BX:BX").EntireColumn.Insert
Range("CC:CC").EntireColumn.Insert
Range("CH:CH").EntireColumn.Insert
Range("CM:CM").EntireColumn.Insert
Range("CR:CR").EntireColumn.Insert
Range("CW:CW").EntireColumn.Insert
Range("DB:DB").EntireColumn.Insert
Range("DG:DG").EntireColumn.Insert
Range("DL:DL").EntireColumn.Insert
Range("DQ:DQ").EntireColumn.Insert
Range("DV:DV").EntireColumn.Insert
End Sub

Sub SumDragCopyPaste()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("P2").Formula = "=L2+M2+N2+O2"
Range(Cells(2, "P"), Cells(LastRow, "P")).FillDown
Columns("P").Copy
Columns("U").PasteSpecial Paste:=xlPasteFormulas
Columns("Z").PasteSpecial Paste:=xlPasteFormulas
Columns("AE").PasteSpecial Paste:=xlPasteFormulas
Columns("AJ").PasteSpecial Paste:=xlPasteFormulas
Columns("AO").PasteSpecial Paste:=xlPasteFormulas
Columns("AT").PasteSpecial Paste:=xlPasteFormulas
Columns("AY").PasteSpecial Paste:=xlPasteFormulas
Columns("BD").PasteSpecial Paste:=xlPasteFormulas
Columns("BI").PasteSpecial Paste:=xlPasteFormulas
Columns("BN").PasteSpecial Paste:=xlPasteFormulas
Columns("BS").PasteSpecial Paste:=xlPasteFormulas
Columns("BX").PasteSpecial Paste:=xlPasteFormulas
Columns("CC").PasteSpecial Paste:=xlPasteFormulas
Columns("CH").PasteSpecial Paste:=xlPasteFormulas
Columns("CM").PasteSpecial Paste:=xlPasteFormulas
Columns("CR").PasteSpecial Paste:=xlPasteFormulas
Columns("CW").PasteSpecial Paste:=xlPasteFormulas
Columns("DB").PasteSpecial Paste:=xlPasteFormulas
Columns("DG").PasteSpecial Paste:=xlPasteFormulas
Columns("DL").PasteSpecial Paste:=xlPasteFormulas
Columns("DQ").PasteSpecial Paste:=xlPasteFormulas
Columns("DV").PasteSpecial Paste:=xlPasteFormulas
Columns("EA").PasteSpecial Paste:=xlPasteFormulas
Columns("A:EA").Copy
Columns("A:EA").PasteSpecial Paste:=xlPasteValues
End Sub

Sub DeleteColumns():
Columns("DW:DZ").Delete
Columns("DR:DU").Delete
Columns("DM:DP").Delete
Columns("DH:DK").Delete
Columns("DC:DF").Delete
Columns("CX:DA").Delete
Columns("CS:CV").Delete
Columns("CN:CQ").Delete
Columns("CI:CL").Delete
Columns("CD:CG").Delete
Columns("BY:CB").Delete
Columns("BT:BW").Delete
Columns("BO:BR").Delete
Columns("BJ:BM").Delete
Columns("BE:BH").Delete
Columns("AZ:BC").Delete
Columns("AU:AX").Delete
Columns("AP:AS").Delete
Columns("AK:AN").Delete
Columns("AF:AI").Delete
Columns("AA:AD").Delete
Columns("V:Y").Delete
Columns("Q:T").Delete
Columns("L:O").Delete
End Sub
----
Sub HourlyInterval():
Call MoveCopyRowsColumns
Call DeleteExtraColumns
Call InsertColumns
Call SumDragCopyPaste
Call DeleteColumns
End Sub

Whenever I tried to call HourlyInterval() on new spreadsheets, it was only applied to the original spreadsheet.

BigBen
  • 46,229
  • 7
  • 24
  • 40
sybil
  • 1
  • 1
  • 3
    If your code is in a worksheet code module, it will only work on the corresponding worksheet; if it's in a regular module it should work on whatever sheet is active https://stackoverflow.com/a/28439984/478884 – Tim Williams May 04 '23 at 15:51

1 Answers1

3

Be explicit about your target workbook and worksheet in each macro. This example works with "Sheet1" in the active workbook. @BigBen is right, using parameters is a little more complicated, but it's the right way to go.

One way is to define every sub to accept a Worksheet parameter, like this:

Sub MoveCopyRowsColumns(wkshData As Worksheet)
    wkshData.Columns("CQ").Cut
    wkshData.Columns("K").Insert
End Sub

Then in the calling macro, pass the Worksheet to each sub:

Sub HourlyInterval()
    Dim wkbkIntervals As Workbook, wkshData As Worksheet
    
    Set wkbkIntervals = ActiveWorkbook
    Set wkshData = wkbkIntervals.Worksheets("Sheet1")
    
    ' Pass the Worksheet to each sub
    Call MoveCopyRowsColumns(wkshData)
    Call DeleteExtraColumns(wkshData)
    Call InsertColumns(wkshData)
    Call SumDragCopyPaste(wkshData)
    Call DeleteColumns(wkshData)
End Sub
RichardCook
  • 846
  • 2
  • 10