Thread: Pulling data from worksheets, compiling it in CSV file

Try this macro

Sub Test()
Dim SourceWorkbook As Workbook
Dim TargetWorkbook As Workbook
Set SourceWorkbook = ActiveWorkbook
Workbooks.Add
Set TargetWorkbook = ActiveWorkbook
Cells(1, 1) = "name"
Cells(1, 2) = "company"
'Add the rest of the column headings
N = 1
For Each Sheet In SourceWorkbook.Sheets
    N = N + 1
    TargetWorkbook.Sheets(1).Cells(N, 1) = Sheet.Cells(7, 4)
    TargetWorkbook.Sheets(1).Cells(N, 2) = Sheet.Cells(1, 1)
    'Add the rest of the source cells

Next Sheet


End Sub

The workbook containing the source data will need to be open.


Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.