With a lot of help from Google and Mr. Excel I was able to draft a code that would detect the last column with data on a certain worksheet and paste it on the next column:
However, I would like to run this macro automatically in a certain range of sheets and not run it individually in each and every sheet. This is the code I came up so far:
However, I am currently getting a "Invalid or unqalified reference" error with the word in bold and underlined in the code highlighted.
Can anyone help with this? Thanks a lot.
Code:
Sub CopyLastWeek()
'
' CopyLastWeek Macro
'
'Find the last used column in a Row: row 11 in this example
Dim LastCol As Integer
With ActiveSheet
LastCol = .Cells(11, .Columns.Count).End(xlToLeft).Column
'Shows confirmation with last input date'
If MsgBox("Latest input date : " & Cells(10, LastCol) & "?", vbQuestion + vbYesNo) = vbNo Then
Application.EnableEvents = True
Exit Sub
End If
NextCol = LastCol + 1
.Range(.Cells(11, LastCol), .Cells(60, LastCol)).Copy
.Range(.Cells(11, NextCol), .Cells(60, NextCol)).Select
ActiveSheet.Paste
End With
End Sub
However, I would like to run this macro automatically in a certain range of sheets and not run it individually in each and every sheet. This is the code I came up so far:
Code:
Sub MAN_CopyLastWeek()
Dim ws As Worksheet
Dim LastCol As Integer
For Each ws In ThisWorkbook.Worksheets
Application.ScreenUpdating = False
With ws
.unprotect Password:="mypasswords"
End With
'checks if required tab. only required tabs have A10 with that value'
If Range("A10").Value = "Big List" Then
LastCol = .Cells(11, .[U][B]Columns[/B][/U].Count).End(xlToLeft).Column
'Shows confirmation with last input date'
If MsgBox("Latest input date : " & Cells(10, LastCol) & "?", vbQuestion + vbYesNo) = vbNo Then
Application.EnableEvents = True
Exit Sub
End If
NextCol = LastCol + 1
.Range(.Cells(11, LastCol), .Cells(60, LastCol)).Copy
.Range(.Cells(11, NextCol), .Cells(60, NextCol)).Select
ws.Paste
Next ws
End Sub
However, I am currently getting a "Invalid or unqalified reference" error with the word in bold and underlined in the code highlighted.
Can anyone help with this? Thanks a lot.