CODEWITHSUNDEEP
excelvbaloops
Jiyang Zhang
Jiyang Zhang

Reputation: 57

loop all sheet-not working, only re perform on single sheets

I would like the code below to work on all (20) sheets on the workbook,but it only work on 1 sheet and repeatedly copy/paste on that 1 sheet, any advice please?

Much appreciated your time

Sub Copylastcolumn()

Dim ws As Worksheet
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets

Dim LastRow As Long
Dim LastCol As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastCol = Cells(6, "BJ").End(xlToLeft).Column

Cells(6, LastCol).Resize(LastRow - 5).Copy Cells(6, LastCol + 1)

Columns(LastCol).Copy
Columns(LastCol).PasteSpecial xlPasteValues

Next ws

End Sub

Upvotes: 1

Views: 22

Answers (1)

iDevlop
iDevlop

Reputation: 25252

You have to specify the object (ws) you are working with

Sub Copylastcolumn()

Dim ws As Worksheet
Dim LastRow As Long
Dim LastCol As Long

On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
   LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
   LastCol = ws.Cells(6, "BJ").End(xlToLeft).Column

   ws.Cells(6, LastCol).Resize(LastRow - 5).Copy ws.Cells(6, LastCol + 1)

   ws.Columns(LastCol).Copy
   ws.Columns(LastCol).PasteSpecial xlPasteValues

Next ws

End Sub

You could also use With...End With to be more elegant

Upvotes: 1

Related Questions