我有一个格式化报告的宏。我想将此宏包含在我的个人工作簿中，但是代码的一部分引用了第二个工作簿，并从该工作簿中复制了格式。宏使用个人工作簿只能部分工作（某些工作表未按我想要的方式格式化），并且我认为它不能完全工作，因为我正在引用其他工作簿。当我自己运行宏时，一切正常。我的代码中缺少什么吗？
Sub Format()
Sheets(Sheets.Count).Move Before:=Sheets(1)
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Rows("1:11").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("12:12").Select
Selection.Font.Bold = True
Selection.AutoFilter
Cells.Select
Cells.EntireColumn.AutoFit
ws.Range("A4") = ws.Name
Range("A4").Select
Selection.Font.Bold = True
On Error Resume Next
Sheets(ActiveSheet.Index + 1).Activate
If Err.Number <> 0 Then Sheets(1).Activate
Next ws
Range("D13:E222").Select
Selection.ClearContents
xlApp.Workbooks.Open FileName:="C:\Automation\Format.xlsm"
Sheets("All_Leadsheet").Select
Range("A1:O233").Select
Selection.Copy
ActiveWorkbook.Activate
Range("A1:N471").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
Columns("A:F").Select
Selection.ColumnWidth = 40
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True '!!!!!!!!!!!!!!!!!!!!!
.Orientation = 0
End With
Windows("Format.xlsm").Close savechanges:=False
我相信您遇到的主要问题是，您在引用sheets（）和range（）时未指定它们的位置，因此VBA在猜测。而且我猜想VBA在猜的是个人工作簿，而是工作表，而不是您要对其执行操作的工作簿。
我不得不猜测一下您在这里所做的事情，但是您应该从我的更改中删除的主要内容是，不必在更改之前先选择一个单元格。我猜这是您录制宏（顺便说一句，很好的学习方式）引起的。但这会减慢您的宏的速度，仅在实际需要时才选择它。实际上，这也适用于工作表，您无需激活工作表即可对其进行更改。希望这可以帮助。
Sub Format()
Dim wb As Workbook
Set wb = ActiveWorkbook
wb.Sheets(Sheets.Count).Move Before:=Sheets(1)
wb.Sheets("not sure which sheet you want").Rows("1:1").Delete 'Shift:=xlUp
Dim ws As Worksheet
For Each ws In wb.Worksheets
ws.Activate
ws.Rows("1:11").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
With ws.Rows("12:12")
.Font.Bold = True
.AutoFilter
End With
ws.Cells.EntireColumn.AutoFit
ws.Range("A4") = ws.Name
Range("A4").Font.Bold = True
'On Error Resume Next 'Careful with these, all your errors will now just resume next
'Sheets(ActiveSheet.Index + 1).Activate 'Next ws takes care of this, unless you are wanting to skip two worksheets
'If Err.Number <> 0 Then Sheets(1).Activate
Next ws
Set ws = wb.Sheets(1)
ws.Activate
ws.Range("D13:E222").ClearContents
xlApp.Workbooks.Open Filename:="C:\Automation\Format.xlsm" 'Where is xlApp set at?
Set ws = Sheets("All_Leadsheet")
ws.Range("A1:O233").Copy
'ActiveWorkbook.Activate 'active workbook should already be active
ws.Range("A1:N471").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'ws.Range("A1").Select
With ws.Columns("A:F")
.ColumnWidth = 40
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True '!!!!!!!!!!!!!!!!!!!!!
.Orientation = 0
End With
Windows("Format.xlsm").Close savechanges:=False 'Not sure what this file is? Perhaps the individual file before
'moving it to personal workbook? If so delete this.
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句