Excel Macro: Beginner - Pasting Formats from one W

Question

I dont know Excel Macros so I'm sure this is a ridiculous question. I have an excel workbook here with 7 worksheets (K:\Common\HSRE\Hospice Payment Reform\Plotzke\Ad Hoc Tasks\OY1\Monitoring for MACs\Results\Results_2012 - Template – Master.xlsx)

I want to copy the formatting from each of those worksheets (the formats are different on each worksheet) to this workbook (K:\Common\HSRE\Hospice Payment Reform\Plotzke\Ad Hoc Tasks\OY1\Monitoring for MACs\Results\Results_2012 – Template.xlsx). The worksheet names in this workbook are identical to the names in the first workbook.

Based on what I saw online I thought I could do something like (at least for the first worksheet)

Sub FormatMAC()

Workbooks("K:\Common\HSRE\Hospice Payment Reform\Plotzke\Ad Hoc Tasks\OY1\Monitoring for MACs\Results\Results_2012 - Template - Master.xlsx").Worksheets("Provider Level").Range("A1:CZ600").Copy

Workbooks("K:\Common\HSRE\Hospice Payment Reform\Plotzke\Ad Hoc Tasks\OY1\Monitoring for MACs\Results\Copy of Results_2012 - Template1.xlsx").Worksheets("Provider Level").Range("A1:CZ600").PasteSpecial (xlPasteFormats)

End Sub

It seems like the program is getting hung up on the first line. I keep getting this error

Run-time error `9’: Subscript out of Range

Any ideas?

Answer 1

This does not specify the Range, and thus the Format is copied for the entire Worksheet.

Sub FormatMAC()
    Dim wb1 As Workbook, wb2 As Workbook
    Set wb1 = Workbooks("Results_2012 - Template - Master.xlsx")
    Set wb2 = Workbooks("Copy of Results_2012 - Template1.xlsm")
    Dim ws1 As Worksheet, ws2 As Worksheet
    For Each ws1 In wb1.Worksheets
      Set ws2 = wb2.Worksheets(ws1.Name)
      ws1.Cells.Copy
      ws2.Cells.PasteSpecial (xlPasteFormats)
    Next ws1
End Sub

One of the files has xlsm extension because it contains the Sub.

I do not know the reason for you obtaining the error quoted.

Answer 2

You can open the Master workbook first, and then copy the format to the opened template

Assuming the template workbook is open and this macro is in the template workbook, you can use the following

Sub FormatMAC()
Dim mstrWB as Workbook

Set mstrWB = Workbooks.Open("K:\Common\HSRE\Hospice Payment Reform\Plotzke\" & _
    "Ad Hoc Tasks\OY1\Monitoring for MACs\Results\Results_2012 - Template -" & _
    " Master.xlsx")
mstrWB.Worksheets("Provider Level").Range("A1:CZ600").Copy

Worksheets("Provider Level").Range("A1:CZ600").PasteSpecial (xlPasteFormats)

mstrWB.Close

End Sub

Answer 3

If the workbook is open then you don't need to supply the entire path

Try this

Workbooks("Results_2012 - Template - Master").Worksheets("Provider Level").Range("A1:CZ600").Copy

Same with the other.