PDA

View Full Version : Solved: Copy Paste from workbook to workbook



jamiwr
07-31-2008, 09:36 AM
I am looking to create a macro to do the following while on the active sheet of the active workbook:

1. Open file to select a workbook
2. Copy all in new workbook
3. Paste all from new workbook into original workbook at bottom of row
4. Close new workbook
5. Do it again which can vary for the amount of times

This is what I have so far and the text file name will always be the same but for the purpose of the upload I had to make them different. I would like this to be dynamic:



Dim strfile As String
Dim wkb1 As Workbook
Dim wkb2 As Workbook

Set wkb1 = ActiveWorkbook

strfile = Application.GetOpenFilename
If strfile = "False" Then Exit Sub

Workbooks.Open strfile

'This is where I get an error message
Range("A1", Range("A65536").End(xlUp)).Copy Destination:=wkb1.Sheets("Feb 08").Range("A1").End(xlDown).Offset(1, 0)

End Sub

mdmackillop
07-31-2008, 10:40 AM
Welcome to VBAX

Sub Test()
Dim strfile As String
Dim wkb1 As Workbook
Dim wkb2 As Workbook

Set wkb1 = ActiveWorkbook

strfile = Application.GetOpenFilename
If strfile = "" Then Exit Sub

Set wkb2 = Workbooks.Open(strfile)
wkb2.Sheets(1).Range("A1", Range("A65536").End(xlUp)).Copy _
Destination:=wkb1.Sheets("Feb 08").Range("A65536").End(xlUp).Offset(1, 0)
wkb2.Close False

End Sub

jamiwr
07-31-2008, 02:59 PM
This works great but there are two things I would like to do to make it more efficient.

1. Instead of having the destination be Sheets("Feb 08") I need it to be dynamic so that regardless of which worksheet it is on it will work.

2. I would like it to loop so that I do not have to hit the macro button each time i want it to perform. I need it to repeat itself about thirty times but it can vary. I have to choose the file manually each time.

Thanks,

mdmackillop
07-31-2008, 04:37 PM
Sub Test()
Dim strfile As String
Dim wkb2 As Workbook
Dim Ws As Worksheet
Set Ws = ActiveSheet
Do
strfile = Application.GetOpenFilename
If strfile = "" Then Exit Sub
Set wkb2 = Workbooks.Open(strfile)
wkb2.Sheets(1).Range("A1", Range("A65536").End(xlUp)).Copy _
Destination:=Ws.Range("A65536").End(xlUp).Offset(1, 0)
wkb2.Close False
Loop
End Sub

Mavyak
07-31-2008, 07:29 PM
You can also process multiple files with one call:

Sub Test()
Dim strfile As Variant
Dim wkb2 As Workbook
Dim Ws As Worksheet

Set Ws = ActiveSheet

strfile = Application.GetOpenFilename("Excel Files (*.xls), *.xls", ,
"Hold the Ctrl or Shift key down to select multiple files.", "Select", True)

If Not IsArray(strfile) Then
GoTo ExitSub
End If

For Each varfile In strfile
Set wkb2 = Workbooks.Open(varfile)
wkb2.Sheets(1).Range("A1", wkb2.Sheets(1).Range("A65536").End(xlUp)).Copy _
Destination:=Ws.Range("A65536").End(xlUp).Offset(1, 0)
wkb2.Close False
Next varfile

ExitSub:
If Not wbk2 Is Nothing Then
Set wbk2 = Nothing
End If
Set Ws = Nothing
End Sub