Select Sheet Name on a sheet to use in VBA

[ElRoober]

Hi Everybody,

Hopefully a very simple one...........

Below I have some code that allows me to open a workbook elsewhere based on a location (M12) on a Sheet called 'Address'. What I can't figure out is how to then select the Sheet I am after in what would be cell E13 on the sheet address as well. Its currently called and coded as "VAT Return" but the name changes hence why I want to use a cell reference in the code. Is this possible as I cant seem to figure it out.

Dim sourceBook As Workbook
Application.ScreenUpdating = False
Set sourceBook = Workbooks.Open(Sheets("Address").Range("M12").Value)
sourceBook.Sheet.Name("VAT Return").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
sourceBook.Close
Application.ScreenUpdating = True

Many Thanks

D

 

[Alex Blakenburg]

See if this helps.

Sub CopySheet()

    Dim sourceBook As Workbook
    Dim srcWBName As String
    Dim srcShtName As String
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook.Worksheets("Address")
        srcWBName = .Range("M12").Value
        srcShtName = .Range("E13").Value
    End With
    
    Set sourceBook = Workbooks.Open(srcWBName)
    With ThisWorkbook
        sourceBook.Worksheets(srcShtName).Copy After:=.Sheets(.Sheets.Count)
    End With
    sourceBook.Close
    
    Application.ScreenUpdating = True

End Sub

 

[ElRoober]

See if this helps.

Sub CopySheet()

    Dim sourceBook As Workbook
    Dim srcWBName As String
    Dim srcShtName As String
   
    Application.ScreenUpdating = False
   
    With ThisWorkbook.Worksheets("Address")
        srcWBName = .Range("M12").Value
        srcShtName = .Range("E13").Value
    End With
   
    Set sourceBook = Workbooks.Open(srcWBName)
    With ThisWorkbook
        sourceBook.Worksheets(srcShtName).Copy After:=.Sheets(.Sheets.Count)
    End With
    sourceBook.Close
   
    Application.ScreenUpdating = True

End Sub

Thank you so much for your help with this; this is great

 

[ElRoober]

See if this helps.

Sub CopySheet()

    Dim sourceBook As Workbook
    Dim srcWBName As String
    Dim srcShtName As String
   
    Application.ScreenUpdating = False
   
    With ThisWorkbook.Worksheets("Address")
        srcWBName = .Range("M12").Value
        srcShtName = .Range("E13").Value
    End With
   
    Set sourceBook = Workbooks.Open(srcWBName)
    With ThisWorkbook
        sourceBook.Worksheets(srcShtName).Copy After:=.Sheets(.Sheets.Count)
    End With
    sourceBook.Close
   
    Application.ScreenUpdating = True

End Sub

Thank you so much for looking at this for me; this is great and much appreciated

 

[Alex Blakenburg]

You're welcome. Glad I could help.