Excel Macro: Beginner - Pasting Formats from one Workbook to Another

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?

score 2 · Answer 1 · answered Nov 14 '13 at 16:47

2

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.

answered Nov 14 '13 at 16:47

sancho.s ReinstateMonicaCellio

14,708
20
93
185

1

`I do not know the reason for you obtaining the error quoted.` You get the "Subscript out of Range" in the above scenario error when Excel is not able to find the workbooks with a specific name. :) – Siddharth Rout Nov 14 '13 at 16:52
1

Great. Adding the path is not optional, it is mandatory that it be absent. – sancho.s ReinstateMonicaCellio Nov 15 '13 at 16:27

score 0 · Accepted Answer · answered Nov 14 '13 at 16:38

0

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.

answered Nov 14 '13 at 16:38

Siddharth Rout

147,039
17
206
250

score 0 · Answer 3 · answered Nov 14 '13 at 16:39

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

Excel Macro: Beginner - Pasting Formats from one Workbook to Another

3 Answers3

Linked