Copying and Pasting Worksheet Name to another Worksheet that is not active, without using select/activate

Question

I am trying to speed up my code by re-writing it not using select.

I have three tabs, and I want to paste the tab name next to the data that appears in that respective tab, without selecting the tab.

However, I am finding that when I run the code, say for example on sheet 1, it works for sheet 1 but when it trys to do the same for sheet 2 it fails with error "Run-time error '1004': Application-defined or object-defined error."

Please see below my code.

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

Answer 1

Here it is:

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

You can't use Cells on non-active Worksheets. You need to use wb.Sheets(2).Cells(x,y)

The With-blocks in this code are just there for saving space. Every .Range or .Cells refers to the e.g. wb.Sheets(1) and could be seen as wb.Sheets(1).Cells(x,y)..

And btw: it's very good to stop using Select and Activate, you should also avoid ActiveWorkbook or ActiveWorksheet. It's very unreliable and you'll never know what users will do. ;)

HTH