Import sheet from a known file and location and rename

[LittlegmoonBH]

Hi Guys,

I’ve got some code that imports “Sheet1” from a file I needto select from a pop up box.

Sub ImportReport()

Dim Fname As String
Dim Wbk As Workbook

For Each Sheet InActiveWorkbook.Worksheets
If Sheet.Name ="Sheet1" Then
Sheet.Delete
End If
Next Sheet

Fname =Application.GetOpenFilename
Set Wbk =Workbooks.Open(Fname)
Wbk.Sheets("Sheet1").Copy ThisWorkBook.Sheets(1)
Wbk.Close False

Sheets("Sheet1").Select


End Sub

I know the name of the file, location and sheet name that I needto import, but I just can’t get anything to work. Once imported to my mastersheet, I want to change the name of the sheet to the name of the report I’musing it to produce.

Any help?

Thanks

 

[Fluff]

How about

Sub LittlegmoonBH()
   Dim Pth As String, Fname As String
   Dim Wbk As Workbook
   
   Pth = "[COLOR=#ff0000]C:\MrExcel\[/COLOR]"
   Fname = "[COLOR=#ff0000]Book1.xlsm[/COLOR]"
   Set Wbk = Workbooks.Open(Pth & Fname)
   Wbk.Sheets("[COLOR=#ff0000]List[/COLOR]").Copy ThisWorkbook.Sheets(1)
   Wbk.Close False
   ActiveSheet.Name = "[COLOR=#ff0000]New name[/COLOR]"
End Sub

Change values in red to suit