Excel: Extract columns from one workbook to append to another workbook

Question

I have a problem like this: I have multiple workbooks shared the same template with data: (let's say they're named "1.xlsx", "2.xlsx"...) A2: File# (eg: in ""1.xlsx" it's "File1") Field1 Field2 Field3 Field4 (the data stored in D3:G40)

And I have a file name "Full.xlsx" File# Field1 Field2

Now I need to copy data D3:E40 from "1.xlsx", "2.xlsx"... to sheet1 of "Full.xlsx" (Field1, Field2), and their A2 goes to File#

For example, I have:

1.xlsx:
[A2] File1
[D2]Field1 [E2]Field2 [F2]Field3 [G2]Field4
[D3]aa [E3]bb [F3]cc [G3]dd
[D4]ee [E4]ff [F4]gg [G4]hh
...

2.xlsx:
[A2] File2
Field1 Field2 Field3 Field4
11 22 33 44
55 66 77 88
...

I need to have sheet1 in "Full.xlsx":

[A1]File# [B1]Filed1 [C1]Filed2
[A2]File1 [B2]aa [C2]bb
[A3]File1 [B3]ee [C3]ff
...
[A40]File2 [B40]11 [C40]22
[A41]File2 [B41]55 [C41]66
...

Can some one please tell me how to do this? (I'm using MS Excel 2010)

Update: I've finally written something that works (I'm new to VBA so it may be not the best solution)

Public Sub copyrows()
Dim FileNum As String
Dim LastRow As Long, i As Long, Counter As Integer
Dim Dest As Workbook
Set Dest = Workbooks("Full.xlsm")

FileNum = Cells(2, 1).Value

Dest.Activate

LastRow = Dest.Worksheets("Sheet1").Range("C65536").End(xlUp).Row + 1
i = LastRow

For Counter = 3 To 40
    Dest.Worksheets("Sheet1").Cells(i, 1).Value = FileNum
    i = i + 1
Next

ThisWorkbook.Activate
ThisWorkbook.Worksheets("Sheet1").Range("D3", "E40").Copy
Dest.Activate
Dest.Worksheets("Sheet1").Range("B" & LastRow, "C" & i).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

Thank everyone for your concern!

do you want to do it through a program ??? or you want to something as Excel formulas to do the same ?? Please specify — Sumit, Jul 11 '11 at 07:31
@Sumit: Either way is fine. I just need the work done. If you know any please show me. — Risa, Jul 11 '11 at 07:43

score 0 · Answer 1 · edited May 23 '17 at 11:48

You could have a look of what's going on around here with the search box: - How can I copy columns from one sheet to another with VBA in Excel? - Copy data from one sheet to another using VBA - Excel Copy Worksheet from external WorkBook

Btw, you can find many related posts on the right column of this current page.

Yet, if you want help, please post the code you already built so that we could help you improve or debug it.

Excel: Extract columns from one workbook to append to another workbook

1 Answers1