Copy data from new workbook into existing one

[ai1094]

Hi,

I have workbook. I want to run a macro that let's me select an excel file from my computer and copy information from the selected file into a new sheet of my existing workbook. Is there a simple VBA code to do this?


Thanks.

 

[Moses99]

Could you give more detail, the target file is in what 1) worksheet, 2)which range?

 

[ai1094]

Ok say I have a workbook open right now. When I run the macro I want a popup box to come up and let me select an excel file from my computer. Once that excel file is selected, just copy all contents within the sheet into a new sheet of my already opened workbook. I hope that makes it a little more clear.

 

[Fluff]

How about

Sub ai1094()
   Dim Fname As String
   Dim Wbk1 As Workbook, Wbk2 As Workbook
   
   Set Wbk1 = ActiveWorkbook
   Fname = Application.GetOpenFilename
   Set Wbk2 = Workbooks.Open(Fname)
   Wbk2.Sheets(1).Copy Wbk1.Sheets(1)
   Wbk2.Close False
End Sub

 

[ai1094]

This works. However, I have a macro that is creating a pivot table within that existing workbook and I think it may be referencing the new sheet that was added, which is not what I want it to do. Can I fix this?

How about

Sub ai1094()
   Dim Fname As String
   Dim Wbk1 As Workbook, Wbk2 As Workbook
   
   Set Wbk1 = ActiveWorkbook
   Fname = Application.GetOpenFilename
   Set Wbk2 = Workbooks.Open(Fname)
   Wbk2.Sheets(1).Copy Wbk1.Sheets(1)
   Wbk2.Close False
End Sub

 

[Fluff]

Maybe

Sub ai1094()
   Dim Fname As String
   Dim Wbk1 As Workbook, Wbk2 As Workbook
   
   Set Wbk1 = ActiveWorkbook
   Fname = Application.GetOpenFilename
   Set Wbk2 = Workbooks.Open(Fname)
   Wbk2.Sheets(1).Copy , Wbk1.Sheets(Wbk1.Sheets.Count)
   Wbk2.Close False
End Sub

 

[ai1094]

Ok I think I got it to work. I'll be back if I have any more questions. Thanks a lot!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[ai1094]

Quick question, once the new worksheet is copied into my existing workbook, is there a way I can change the sheet name to "REF" ?

 

[Fluff]

Try

Sub ai1094()
   Dim Fname As String
   Dim Wbk1 As Workbook, Wbk2 As Workbook
   
   Set Wbk1 = ActiveWorkbook
   Fname = Application.GetOpenFilename
   Set Wbk2 = Workbooks.Open(Fname)
   Wbk2.Sheets(1).Copy , Wbk1.Sheets(Wbk1.Sheets.Count)
   Wbk2.Close False
   Wbk1.Sheets(Wbk1.Sheets.Count).Name = "Ref"
End Sub