Copying worksheets from one workbook to another

[DrH100]

I have a bit of code that I have used

Sub Copy_sh()
   Dim SDrv   As String
   Dim DDrv   As String
   Dim Sfname As String
   Dim Dfname As String
   Dim wkbSrc As Workbook
   Dim wkbDst As Workbook
 Dim strFName As String
    
    strFName = Sheet1.Range("A1").Value
    Workbooks.Open Filename:=strFName
    
 
'set source file
    SDrv = strFName

'set destination file (will be open)
   DDrv = "\\dfs60668\folders\Test\Test\Test\Complaints Data\New Data.xlsm"
  'Dfname = "New Data.xlsm"
    
   'open source file
    Set wkbSrc = Workbooks.Open(SDrv & Sfname)
    'Application.DisplayAlerts = False

' need the clever bit in here
 

End Sub

This opens a source document depending on what is in cell A1 which is working great (this file path is taken from another Macro somewhere else). Also has the destination document details here too

What I want to do (and cant) is the clever bit !! I want to copy all the tabs that are in the Source sheet and insert them into the destination sheet (this will always be the same file name as set in the code above)

I don't know how many or what the worksheets are called in the source document as it could be different each time the macro is run. So in effect will need to copy all the sheets - Is this at all possible?

As you will probably notice I have taken the above and amended it from elsewhere so I appreciate that there are bits in there that I don't necessarily need at the moment but will worry about those later.

Any help, as always, much appreciated.

 

[Norie]

In the posted code you open 2 workbooks, which means there will be 3 workbooks open in total including the one the code is in.

Which workbook to you want to copy from and which do you want to copy to?

Do you want to copy the sheets from wkbSrc to the workbook the code is in?

Or is it the workbook you open here?

    Workbooks.Open Filename:=strFName

 

[DrH100]

Thanks Norie

When I run the code it only opens one additional workbook to the one that is already open so it is probably my poor attempts at stealing code from others and not really understanding it.

I want to do as you say and copy from the wkbSrc file and pasted into the one that the code is being run from (this should be the DDrv as coded (if thats right))

Thanks

 

[Norie]

The code opens 2 workbooks, one here,

    Workbooks.Open Filename:=strFName

and one here.

    Set wkbSrc = Workbooks.Open(SDrv & Sfname)

If we ignore that for now, to copy a sheet from wkbSrc to the workbook the code is in you can use code like this.

wkbSrc.Sheets("Sheet1").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)