echane
12-14-2009, 12:43 AM
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
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