Copy Method of Worksheet Class Failed

[MAM8433]

I’m running into a 1004 run-time error: copy method of worksheet class fails and I don’t know how to fix it.

My macro file contains 3 worksheets; the first 2 are named “Summary” and “By Market Report,” respectively. These worksheet names are static.

My claims file contains 3 worksheets, and I want to copy 2 Worksheets to the macro file. The file and worksheet names are dynamic. I get the claims file name through a file picker and I prompt the user for the worksheet names in a userform.

Here is my code that fails: Workbooks(txtClaimsFname).Worksheets(txtClaimsShtName).Copy After:=Workbooks(txtMacroFname).Worksheets("By Market Report"). Both files are open during the copy.

When I step through the code and hover over each variable, the contents are correct. That is, the FName variables show the correct file name and extension, and txtClaimsShtName contains the worksheet name.

So far, I’ve also tried these statements and get the same error: Workbooks(txtClaimsFname).Worksheets(ClaimsShtName).Copy After:=Workbooks(txtMacroFname).Worksheets(2) and Workbooks(txtClaimsFname).Worksheets(ClaimsShtName).Copy After:=Workbooks(txtMacroFname).Sheets(Workbooks(txtMacroFname).Worksheets.Count)

I hope you can help me and possibly provide an explanation of why it fails. Thank you

 

[MAM8433]

Hi MAM8433. It doesn't seem like you're actually opening your selected file. You can trial this code. Please save a back up copy of your wbs before trialing the code. HTH. Dave

Sub CopySheets()
Dim FileNm As Object, TargetFiles As FileDialog
Dim txtClaimsShtName As String, txtGeoClaimsShtName As String
txtClaimsShtName = TextBox6.Text
txtGeoClaimsShtName = TextBox7.Text

Set TargetFiles = Application.FileDialog(msoFileDialogFilePicker)
With TargetFiles
.Title = ("Find Your HPN Claims File and Click OK")
'Makes sure the user can select only one file
.AllowMultiSelect = False
'Filter to just the following types of files to narrow down selection options
.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb;*.csv", 1
If .Show = 0 Then
MsgBox ("No file was selected. When ready to start again, click the cloud icon.")
Exit Sub
End If
End With

On Error GoTo below
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'open the file and assign the workbook
Set FileNm = Workbooks.Open(TargetFiles.SelectedItems(1))
Workbooks(FileNm.Name).Worksheets(txtClaimsShtName).Copy _
    After:=Workbooks(txtMacroFname).Worksheets("By Market Report")
Workbooks(FileNm.Name).Worksheets(txtGeoClaimsShtName).Copy _
    After:=Workbooks(txtMacroFname).Worksheets("By Market Report")
Workbooks(FileNm.Name).Close SaveChanges:=False
below:
If Err.Number <> 0 Then
MsgBox "Error"
End If
Set FileNm = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Hi MAM8433. It doesn't seem like you're actually opening your selected file. You can trial this code. Please save a back up copy of your wbs before trialing the code. HTH. Dave

Sub CopySheets()
Dim FileNm As Object, TargetFiles As FileDialog
Dim txtClaimsShtName As String, txtGeoClaimsShtName As String
txtClaimsShtName = TextBox6.Text
txtGeoClaimsShtName = TextBox7.Text

Set TargetFiles = Application.FileDialog(msoFileDialogFilePicker)
With TargetFiles
.Title = ("Find Your HPN Claims File and Click OK")
'Makes sure the user can select only one file
.AllowMultiSelect = False
'Filter to just the following types of files to narrow down selection options
.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb;*.csv", 1
If .Show = 0 Then
MsgBox ("No file was selected. When ready to start again, click the cloud icon.")
Exit Sub
End If
End With

On Error GoTo below
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'open the file and assign the workbook
Set FileNm = Workbooks.Open(TargetFiles.SelectedItems(1))
Workbooks(FileNm.Name).Worksheets(txtClaimsShtName).Copy _
    After:=Workbooks(txtMacroFname).Worksheets("By Market Report")
Workbooks(FileNm.Name).Worksheets(txtGeoClaimsShtName).Copy _
    After:=Workbooks(txtMacroFname).Worksheets("By Market Report")
Workbooks(FileNm.Name).Close SaveChanges:=False
below:
If Err.Number <> 0 Then
MsgBox "Error"
End If
Set FileNm = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Hello, NdNoviceHlp. I supplied code that that I thought @Akuini wanted to see when Akuini asked: Can you show us part of the code that assign the sheet name to txtClaimsFname? I provided sections of code, and I see that I erred in including part of the filepicker code, which works really well for me.

The problem I cannot solve at this time is why I can’t copy two worksheets from an open xlsx file into my macro file, which is also open. The copy statement bombs. If I try to select, the select statement bombs. When I debug.print or hover over the variables in the copy statement, I can see that my file name and worksheet name variables are filled correctly. Can you see anything in the code that keeps me from copying? It would be a great help to understand what triggers “copy method of worksheet class failed.” Thanks!

 

[Akuini]

Not sure what cause the error.
If this part has the correct name
txtClaimsShtName = TextBox6.Text
txtGeoClaimsShtName = TextBox7.Text
then it should work.

You should check if the sheets does exist.
Try this:
1. Add this function:

Function WorksheetExists(sName As String) As Boolean
    WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)")
End Function

2. And amend your code:

Set FileNm = Workbooks.Open(TargetFiles.SelectedItems(1))

If Not WorksheetExists(txtClaimsShtName) Then MsgBox txtClaimsShtName & " not exist": Exit Sub
If Not WorksheetExists(txtGeoClaimsShtName) Then MsgBox txtGeoClaimsShtName & " not exist": Exit Sub
Workbooks(FileNm.Name).Worksheets(txtClaimsShtName).Copy _
    After:=Workbooks(txtMacroFname).Worksheets("By Market Report")
Workbooks(FileNm.Name).Worksheets(txtGeoClaimsShtName).Copy _
    After:=Workbooks(txtMacroFname).Worksheets("By Market Report")
Workbooks(FileNm.Name).Close SaveChanges:=False

 

[ankit_26812]

Hi,

I am facing the issue in copy and paste. Below is the VBA code that I am using. However on one of the file I am getting this error. Need help on this one.

Sub GetSheets()

Application.ScreenUpdating = False


Path = "C:\Users\agnihota\OneDrive - Blackstone\Desktop\Automation\Report\"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop

Application.ScreenUpdating = True

End Sub