Excel: Extract columns from one workbook to append to another workbook
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 fil开发者_运维知识库e 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!
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.
精彩评论