vba Excel 宏：初学者 - 将格式从一个工作簿粘贴到另一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19982947/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Macro: Beginner - Pasting Formats from one Workbook to Another
提问by user2992957
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)
我不知道 Excel 宏，所以我确定这是一个荒谬的问题。我这里有一个 excel 工作簿，里面有 7 个工作表（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.
我想将每个工作表的格式（每个工作表上的格式不同）复制到这个工作簿（K:\Common\HSRE\Hospice Payment Reform\Plotzke\Ad Hoc Tasks\OY1\Monitoring for MACs\Results\Results_2012 – 模板.xlsx）。此工作簿中的工作表名称与第一个工作簿中的名称相同。
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
运行时错误“9”：下标超出范围
Any ideas?
有任何想法吗？
采纳答案by Siddharth Rout
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
Workbooks("Results_2012 - Template - Master").Worksheets("Provider Level").Range("A1:CZ600").Copy
Same with the other.
和另一个一样。
回答by sancho.s ReinstateMonicaCellio
This does not specify the Range, and thus the Formatis copied for the entire Worksheet.
这不指定Range，因此Format为整个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 xlsmextension because it contains the Sub.
其中一个文件具有xlsm扩展名，因为它包含Sub.
I do not know the reason for you obtaining the error quoted.
我不知道您获得所引用错误的原因。
回答by Jaycal
You can open the Master workbook first, and then copy the format to the opened template
可以先打开Master工作簿，然后将格式复制到打开的模板中
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
