Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee
Hmmm, I'm not sure.
Try adding "NumSheets" to the Dim Statement since I hadn't defined it in my original example
Dim r, NumSheets As IntegerIt worked for me without it, but sometimes it is not so forgiving.
If it is still giving you problems it may be the version of Office you have. I am using MS Office 2010.
Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee
Slightly different take on it cshortt,
I usually use Cells(row #, col #) to reference my data.
Try the following:
Sub CombineAllSheetsinOne()
Dim r, NumSheets As Integer
'Do not care about the first 2 or the last two worksheets so subtract 4
NumSheets = ActiveWorkbook.Sheets.Count - 4
' first row in new sheet is 7
r = 7
For i = 1 To NumSheets
'start grabbing data from the 3rd worksheet (A-1)
ActiveWorkbook.Worksheets(i + 2).Activate
Worksheets("List - By Vehicle Number").Cells(r + i - 1, 3).Value = Cells(30, 2).Value
Worksheets("List - By Vehicle Number").Cells(r + i - 1, 4).Value = Cells(18, 2).Value
Worksheets("List - By Vehicle Number").Cells(r + i - 1, 5).Value = Cells(9, 2).Value
Worksheets("List - By Vehicle Number").Cells(r + i - 1, 6).Value = Cells(12, 2).Value
Worksheets("List - By Vehicle Number").Cells(r + i - 1, 7).Value = Cells(15, 2).Value
Worksheets("List - By Vehicle Number").Cells(r + i - 1, 8).Value = Cells(21, 2).Value
Worksheets("List - By Vehicle Number").Cells(r + i - 1, 9).Value = Cells(33, 2).Value
Worksheets("List - By Vehicle Number").Cells(r + i - 1, 10).Value = Cells(27, 2).Value
Worksheets("List - By Vehicle Number").Cells(r + i - 1, 11).Value = Cells(30, 10).Value
Worksheets("List - By Vehicle Number").Cells(r + i - 1, 12).Value = Cells(24, 6).Value
Worksheets("List - By Vehicle Number").Cells(r + i - 1, 13).Value = Cells(24, 8).Value
Worksheets("List - By Vehicle Number").Cells(r + i - 1, 14).Value = Cells(18, 4).Value
Next i
End Sub