Transfer a selected sheet from one workbook to another

[zoog25]

Hello All,

Here is my situation, I'm trying to create a compiler program for a project we are working on. We have one department that generates a raw excel file with a ton of data. I have a userform with a few different button. One of the button is for "Importing Raw Data". Now I've been doing research and i can't seem to find coding for what i want. So when the user presses the button, an open window pops up and allows the user to select the excel file required. (Note the raw data is always stored in a new folder and constantly has different file names) Once the user select the excel file. The program will copy over sheet 1 from that file and transfer it to the main program file and rename the sheet "Raw Data".

If anyone can please help me with this. The main program .xlsm file is called "MD Compiler.xlsm".

Thank you for your help.

 

[JLGWhiz]

The code would go into the UserForm code module where the button resides. You will need to edit the title line for the correct button name.

Sub commandbutton1_click()  'Assumes use of Active-X button, edit button name
Dim fName, wb As Workbook
fName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
    If fName <> False Then
        Set wb = Workbooks.Open(fName)
        wb.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = "Raw Data"
    End If
End Sub

You will need to change the Raw Data name before running the code again to avoid error message telling you that you already have a sheet named Raw Data.

 

[zoog25]

Thank you so much JLGWhiz. I tested out the code and it is working perfectly.

 

[JLGWhiz]

Thank you so much JLGWhiz. I tested out the code and it is working perfectly.

You're welcome,
regards, JLG