Elvis
02-03-2009, 07:15 AM
Hi,
I have the code below which loops through all sheets in a given workbook and consolidates the data in a summary sheet for the ranges defined in the code.
However I need to change this so that it loops through all workbooks in a given folder and copies the information from the first sheet of each workbook.
Would this be easy to change?
Thanks,
Elvis
----------------------------
Sub Summarisesheets2() 'works - returns the value in cells below in every sheet in workbook
Dim SummarySheet As String
Sheets.Add before:=Sheets(1) 'inserts sheet
SummarySheet = ActiveSheet.Name
For i = 2 To ActiveWorkbook.Sheets.Count
Sheets(SummarySheet).Cells(i - 1, 2).Value = Sheets(i).Name 'inserts the name of indivdual sheet
Sheets(SummarySheet).Cells(i - 1, 3).Value = Sheets(i).Range("d8").Value ' Spend
Sheets(SummarySheet).Cells(i - 1, 7).Value = Sheets(i).Range("m25").Value 'Revenue ROI
Sheets(SummarySheet).Cells(i - 1, 8).Value = Sheets(i).Range("f25").Value ' Max Rev ROI
Sheets(SummarySheet).Cells(i - 1, 9).Value = Sheets(i).Range("J25").Value 'Min Rev ROI
Sheets(SummarySheet).Cells(i - 1, 10).Value = Sheets(i).Range("m22").Value 'Min Rev ROI
Sheets(SummarySheet).Cells(i - 1, 11).Value = Sheets(i).Range("m23").Value 'incremental Vol
Sheets(SummarySheet).Cells(i - 1, 12).Value = Sheets(i).Range("m24").Value 'Incremental Revenue
Sheets(SummarySheet).Cells(i - 1, 13).Value = Sheets(i).Range("F27").Value 'LT ROI Low
Sheets(SummarySheet).Cells(i - 1, 14).Value = Sheets(i).Range("J27").Value 'LT ROI High
Sheets(SummarySheet).Cells(i - 1, 15).Value = Sheets(i).Range("M27").Value 'LT ROI Avg
'column headings
Next i
End Sub
I have the code below which loops through all sheets in a given workbook and consolidates the data in a summary sheet for the ranges defined in the code.
However I need to change this so that it loops through all workbooks in a given folder and copies the information from the first sheet of each workbook.
Would this be easy to change?
Thanks,
Elvis
----------------------------
Sub Summarisesheets2() 'works - returns the value in cells below in every sheet in workbook
Dim SummarySheet As String
Sheets.Add before:=Sheets(1) 'inserts sheet
SummarySheet = ActiveSheet.Name
For i = 2 To ActiveWorkbook.Sheets.Count
Sheets(SummarySheet).Cells(i - 1, 2).Value = Sheets(i).Name 'inserts the name of indivdual sheet
Sheets(SummarySheet).Cells(i - 1, 3).Value = Sheets(i).Range("d8").Value ' Spend
Sheets(SummarySheet).Cells(i - 1, 7).Value = Sheets(i).Range("m25").Value 'Revenue ROI
Sheets(SummarySheet).Cells(i - 1, 8).Value = Sheets(i).Range("f25").Value ' Max Rev ROI
Sheets(SummarySheet).Cells(i - 1, 9).Value = Sheets(i).Range("J25").Value 'Min Rev ROI
Sheets(SummarySheet).Cells(i - 1, 10).Value = Sheets(i).Range("m22").Value 'Min Rev ROI
Sheets(SummarySheet).Cells(i - 1, 11).Value = Sheets(i).Range("m23").Value 'incremental Vol
Sheets(SummarySheet).Cells(i - 1, 12).Value = Sheets(i).Range("m24").Value 'Incremental Revenue
Sheets(SummarySheet).Cells(i - 1, 13).Value = Sheets(i).Range("F27").Value 'LT ROI Low
Sheets(SummarySheet).Cells(i - 1, 14).Value = Sheets(i).Range("J27").Value 'LT ROI High
Sheets(SummarySheet).Cells(i - 1, 15).Value = Sheets(i).Range("M27").Value 'LT ROI Avg
'column headings
Next i
End Sub