Help with VBA Script

Firstly I开发者_如何学编程 am a newbie to VBA but the pseudocode for what I am trying to do is:

 For All open Excel Files
 Copy all values in Colomns A,B, C and D
 Append into Tab 1 of output.xls

I would appreciate some pointers in the right direction.

Thanks

Sometimes the best way to learn is to record a macro.

Tools > Macros - Choose record.

Then into your workbook, select columns A,B,C,D then CTRL+C, then open your new TaB and CTRL+V.

Stop recording Macro, then ALT+F11 to see the generated code, this should give you a starter for ten.

If you need help understanding the generated code / what it does come back and we can explain.

There are a couple of things that recording a macro won't help you with though, for example, using For... Each to iterate through each sheet in a workbook. Here is some sample code to point you in the right direction. This will iterate through all open workbooks and copy the contents of the first four columns onto a worksheet.

Sub joinAllSheets()
Dim ws As Worksheet
Dim wb As Workbook
Dim wsOutput As Worksheet
Dim lngRowCount As Long
Dim wbSource As Workbook

'create output workbook
Set wsOutput = Application.Workbooks.Add.Sheets(1)
lngRowCount = 1

'Iterate through each open workbook
For Each wb In Application.Workbooks

'if the current workbook is not our output workbook then
If wb.Name <> wsOutput.Name Then
    'iterate through each worksheet
    For Each ws In wb.Worksheets
    'copy the first four columns of the used range in the worksheet
    Application.Intersect(ws.UsedRange, ws.Range("A:D")).Copy _
            Destination:=wsOutput.Cells(lngRowCount, 1)
    'we need to count how many rows there are in the usedrange so we know
    'where to paste into the output worksheet
    lngRowCount = lngRowCount + ws.UsedRange.Rows.Count + 1
    Next ws

End If

Next wb

End Sub