All,
The below code is suppose to copy columns from 3 sheets into a final sheet (sheet "All"). The first two columns are the same so I've just copy pasted them into the final sheet. The error flags up when it tries to run the following "ws.Range("C" & LC).Copy sh.Cells(1, CC)". I'm guessing the range I've selected if cant be selected and that's why the code brings up an error.
Thank you for the help in advance.
The below code is suppose to copy columns from 3 sheets into a final sheet (sheet "All"). The first two columns are the same so I've just copy pasted them into the final sheet. The error flags up when it tries to run the following "ws.Range("C" & LC).Copy sh.Cells(1, CC)". I'm guessing the range I've selected if cant be selected and that's why the code brings up an error.
Thank you for the help in advance.
VBA Code:
Sub CompileAll()
Dim LC As Long, CC As Long
Dim ws As Worksheet, sh As Worksheet
'LC - Last column
'CC - Last compile column
Sheets("All").Delete
Sheets.Add(After:=Sheets("sheet3")).Name = "All"
Sheets("sheet1").Range("A:B").Copy
' Paste Special
Sheets("All").Range("A:B").PasteSpecial xlValues
Sheets("All").Range("A:B").PasteSpecial xlFormats
Set sh = Worksheets("All")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "All" Then
CC = sh.Cells(3, sh.Columns.Count).End(xlToLeft).Column + 1
LC = ws.Range("C" & ws.Columns.Count).End(xlToRight).Column
If CC = 2 Then CC = 1
ws.Range("C" & LC).Copy sh.Cells(1, CC)
End If
Next ws
End Sub