我试图通过不使用select重写代码来加快代码的速度。
我有三个选项卡,我想将选项卡名称粘贴到相应选项卡中显示的数据旁边,而不选择该选项卡。
但是,我发现在运行代码时,例如在工作表1上,例如,它适用于工作表1,但是当它尝试对工作表2执行相同操作时,它失败,并显示错误“运行时错误'1004':应用程序-定义或对象定义的错误。”
请在下面查看我的代码。
Sub Create_Reports_NEWWWW()
Application.ScreenUpdating = False
Dim wb As Workbook
Dim LastRow As Integer
Dim LastColumn As Integer
Set wb = ActiveWorkbook
'copy sheet name to right of raw data on each sheet
LastRow = wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
LastColumn = wb.Sheets(1).Cells(4, Columns.Count).End(xlToLeft).Column
wb.Sheets(1).Range(Cells(4, LastColumn + 1), Cells(LastRow, LastColumn + 1)) = wb.Sheets(1).Name
LastRow = wb.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
LastColumn = wb.Sheets(2).Cells(4, Columns.Count).End(xlToLeft).Column
wb.Sheets(2).Range(Cells(4, LastColumn + 1), Cells(LastRow, LastColumn + 1)) = wb.Sheets(2).Name
LastRow = wb.Sheets(3).Cells(Rows.Count, 1).End(xlUp).Row
LastColumn = wb.Sheets(3).Cells(4, Columns.Count).End(xlToLeft).Column
wb.Sheets(3).Range(Cells(4, LastColumn + 1), Cells(LastRow, LastColumn + 1)) = wb.Sheets(3).Name
这里是:
Sub Create_Reports_NEWWWW()
Application.ScreenUpdating = False
Dim wb As Workbook
Dim lastRow As Integer
Dim lastColumn As Integer
Set wb = ActiveWorkbook
With wb.Sheets(1)
'copy sheet name to right of raw data on each sheet
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
lastColumn = .Cells(4, .Columns.Count).End(xlToLeft).Column
.Range(.Cells(4, lastColumn + 1), .Cells(lastRow, lastColumn + 1)) = .Name
End With
With wb.Sheets(2)
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
lastColumn = .Cells(4, .Columns.Count).End(xlToLeft).Column
.Range(.Cells(4, lastColumn + 1), .Cells(lastRow, lastColumn + 1)) = .Name
End With
With wb.Sheets(3)
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
lastColumn = .Cells(4, .Columns.Count).End(xlToLeft).Column
.Range(.Cells(4, lastColumn + 1), .Cells(lastRow, lastColumn + 1)) = .Name
End With
End Sub
您不能Cells
在非活动工作表上使用。您需要使用wb.Sheets(2).Cells(x,y)
With
这段代码中的-blocks只是为了节省空间。每个.Range
或都.Cells
指例如,wb.Sheets(1)
并且可以看作是wb.Sheets(1).Cells(x,y)..
顺便说一句:停止使用Select
and很好Activate
,您也应该避免使用ActiveWorkbook
or ActiveWorksheet
。这是非常不可靠的,而且您永远不会知道用户会做什么。;)
高温超导
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句