Copy from one Worksheet to another

[scoobadave71]

Hey guys..

I am looking to copy a worksheet to a new workbook. The workbook I am copying from will only have one worksheet, but the name of the worksheet will change each time it is uploaded. I am trying to use the below code, but am failing miserably at reading the copy from worksheet:

Dim Wkb1 As Workbook ' Destination Workbook
Dim Wkb2 As Workbook ' Copy FROM workbook
Dim spath As String
spath = ActiveWorkbook.Path
Dim wkshtName As Worksheet


' set the workbook you are copying to as active and give it a name for reference
Set Wkb1 = ActiveWorkbook
' Check for and Delete Sheet 1 if exists
Application.DisplayAlerts = False
On Error Resume Next
Sheets("Fulfillment").Delete
Application.DisplayAlerts = True
On Error GoTo 0
' open the workbook you are copying from and activate it
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS; *.CSV), *.XLS; *.CSV", Title:="Select File To Be Opened")


If fNameAndPath = False Then
MsgBox ("Please Select a File")
Exit Sub
End If
' if open, close the file
Set Wkb2 = Workbooks.Open(fNameAndPath)
'Wkb2.Activate
Wkb2.Worksheets(1).Activate 'this works

With Wkb2
' copy sheet across
.Sheets(1).Copy before:=Wkb1.Worksheets(1)
'Wkb2.Worksheets(2).Copy after:=Wkb1.Worksheets(1)
End With
'close the file
Wkb2.Close SaveChanges:=False 'or True

 

[scoobadave71]

so this is where I am failing:
With Wkb2
' copy sheet across
.Sheets(1).Copy before:=Wkb1.Worksheets(1) 'FAILS I get Automation Error
'Wkb2.Worksheets(2).Copy after:=Wkb1.Worksheets(1)
End With

 

[scoobadave71]

[[Solved]]Re: Copy from one Worksheet to another

Found my answer. I had to activate the workbook I was copying from.