Solved: Problems with finding last column

[echane]

I'm having problems with the line in red below and getting an "invalid or unqualified reference" error. I'm not sure how to fix this. Any help is much appreciated. Thanks!

I'm trying to get it to go through a bunch of worksheets and copy only certain cells from each column to an "Output" sheet which just summarizes all the other worksheets.

Private Sub worksheet_activate()
Range("A9:I5000").ClearContents
Dim wks As Worksheet
Dim OutSH As Worksheet
Dim i As Long
Dim LastCol As Long
Set OutSH = Sheets("Output")
For Each wks In Worksheets
If wks.Name <> "Output" Then
If wks.Name <> "Input" Then
LastCol = .Cells(3, Columns.Count).End(xlLeft).Column
For i = 2 To LastCol

.Cells(3, i).Copy
OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial xlPasteValues

.Cells(6, i).Copy
OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Select
Selection.PasteSpecial xlPasteValues

Next i
End If
End If
Next wks
Range("A1").Select
End Sub

[Bob Phillips]

Private Sub worksheet_activate()
Range("A9:I5000").ClearContents
Dim wks As Worksheet
Dim OutSH As Worksheet
Dim i As Long
Dim LastCol As Long
Set OutSH = Sheets("Output")
For Each wks In Worksheets
With wks
If .Name <> "Output" And .Name <> "Input" Then
LastCol = .Cells(3, .Columns.Count).End(xlLeft).Column
For i = 2 To LastCol

.Cells(3, i).Copy
OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

.Cells(6, i).Copy
OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).PasteSpecial xlPasteValues
Next i
End If
End With
Next wks
Range("A1").Select
End Sub

[mdmackillop]

Try
xlToLeft

[echane]

I bow to you guys. I've been staring at it for awhile not knowing what to do with it. Thank you so much!!!