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

 

[Akuini]

Not sure why it fails.
Maybe you can try this:
1. Add Debug.Print

Debug.Print txtClaimsFname
Debug.Print txtClaimsShtName
Debug.Print txtMacroFname
Workbooks(txtClaimsFname).Worksheets(txtClaimsShtName).Copy After:=Workbooks(txtMacroFname).Worksheets("By Market Report")

2. Do the copy manually & record the process by Macro recorder. Maybe you can see something different with the names there.

 

[Fluff]

Do either of the workbooks, have workbook protection set on the Review tab?
Does the macro workbook already have a sheet with the same names as the sheet you are trying to copy?

 

[MAM8433]

Not sure why it fails.
Maybe you can try this:
1. Add Debug.Print

Debug.Print txtClaimsFname
Debug.Print txtClaimsShtName
Debug.Print txtMacroFname
Workbooks(txtClaimsFname).Worksheets(txtClaimsShtName).Copy After:=Workbooks(txtMacroFname).Worksheets("By Market Report")

2. Do the copy manually & record the process by Macro recorder. Maybe you can see something different with the names there.

Thank you, Akuini. I have been including debug.print on these variables ahead of the copy code, and the content matches what is displayed when I step through the code and hover over the variables. Thanks for the macro recorder idea. Unfortunately, I tried these combinations; the code chokes at line 2, whether it is an activate or select:

Macro recorder code:
Windows("Interim v1.2 test claims.xlsx"). Activate
Sheets("Provider Disruption Report").Select
Sheets("Provider Disruption Report").Copy Before:=Workbooks( _
"VBA DA Template 1.2. adding geo code.xlsm").Sheets(3)
Windows("Interim v1.2 test claims.xlsx"). Activate
Sheets("Geocoded").Select
Sheets("Geocoded").Copy Before:=Workbooks( _
"VBA DA Template 1.2. adding geo code.xlsm").Sheets(4)

I tried dropping the macro recorder code into my code, and it bombed. That tells me something needs to be edited.

'The following code causes error: select method of worksheet class failed at line 2,
Windows(txtClaimsFname).Activate
Sheets(txtClaimsShtName).Select
Sheets(txtClaimsShtName).Copy Before:=Workbooks(txtMacroFname).Sheets(3)
Windows(txtClaimsFname).Activate
Sheets(txtGeoClaimsShtName).Select
Sheets(txtGeoClaimsShtName).Copy Before:=Workbooks(txtMacroFname).Sheets(4)

'Here, I specified the workbook name. Error message: select method of worksheet class failed at Line 2.
Windows(txtClaimsFname).Activate
'Workbooks(txtClaimsFname).Sheets(txtClaimsShtName).Select
'Workbooks(txtClaimsFname).Sheets(txtClaimsShtName).Copy Before:=Workbooks(txtMacroFname).Sheets(3)
'Workbooks(txtClaimsFname).Activate
'Workbooks(txtClaimsFname).Sheets("Geocoded").Select
'Workbooks(txtClaimsFname).Sheets("Geocoded").Copy Before:=Workbooks(txtMacroFname).Sheets(4)

' I tried to copy both worksheets. This code creates error message: activate method of worksheet class failed at line 2:
Windows(txtClaimsFname).Activate
Sheets(txtClaimsShtName).Activate
Sheets(Array(txtClaimsShtName, txtGeoClaimsShtName)).Select
Sheets(txtGeoClaimsShtName).Activate
Sheets(Array(txtClaimsShtName, txtGeoClaimsShtName)).Copy Before:=Workbooks(txtMacroFname).Sheets(3)

What is the better way to code this?

 

[MAM8433]

Do either of the workbooks, have workbook protection set on the Review tab?
Does the macro workbook already have a sheet with the same names as the sheet you are trying to copy?

Hello, Fluff: there is no workbook protection in place in either file. The macro file and the claims file do not share worksheet or worksheet names until the copy code runs.

 

[Akuini]

Unfortunately, I tried these combinations; the code chokes at line 2,

It's weird.
Is it possible that you have trailing space in txtClaimsShtName?
Try this:

Debug.Print txtClaimsShtName = Trim(txtClaimsShtName)
Debug.Print "Provider Disruption Report" = txtClaimsShtName
Workbooks(txtClaimsFname).Worksheets(txtClaimsShtName).Copy After:=Workbooks(txtMacroFname).Worksheets("By Market Report")

If Debug.Print result is False then there's your answer.

 

[MAM8433]

Akuini: Thanks. I ran the 3 lines of code. The middle line shows "true" in the Immediate window. That makes sense because I open the userform as vbmodeless so I could double-click the worksheet name and copy each into the userform. When I run the 3rd line, I still get the Copy method of Worksheet class failed.

 

[Akuini]

The middle line shows "true" in the Immediate window.

What about the first line?
Debug.Print txtClaimsShtName = Trim(txtClaimsShtName)

Can you show us part of the code that assign the sheet name to txtClaimsFname?

 

[MAM8433]

What about the first line?
Debug.Print txtClaimsShtName = Trim(txtClaimsShtName)

Can you show us part of the code that assign the sheet name to txtClaimsFname?

My apologies; Both the 1st and 2nd lines return “true."

The claims file name (txtClaimsFname) is assigned in Sub PostQuestExport1:

With Application.FileDialog(msoFileDialogFilePicker)
.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
'Show the dialog box
'Store in fullpath variable
If .Show <> 0 Then
txtClaimsFpath = .SelectedItems.Item(1)
txtClaimsFname = Mid(.SelectedItems(1), InStrRev(.SelectedItems(1), "\") + 1)
Else
MsgBox ("No file was selected. When ready to start again, click the cloud icon.")
Exit Sub
End If
End With

Sub PostQuestExport1 ends by loading the userform: Form.Show vbModeless
Code from the userform that assigns the worksheet names:

If TextBox6 = "" Then
MsgBox ("Paste the claims file tab name here. This field cannot be blank.")
TextBox6.SetFocus
Exit Sub
Else
txtClaimsShtName = TextBox6.Text
End If

If TextBox7 = "" Then
MsgBox ("Paste the Geocoded claims tab name here. This field cannot be blank.")
TextBox7.SetFocus
Exit Sub
Else
txtGeoClaimsShtName = TextBox7.Text
End If

At the end of this sub, I then pass these values:

Call PostQuestExport2(txtDrive1, txtDiv, CompName, txtClaimsShtName, txtPdColPres, txtPdCol, intHeadrRow, txtGeoClaimsShtName)

Inside PostQuestExport2:

Sub PostQuestExport2(txtDrive1 As String, txtDiv As String, CompName As String, txtClaimsShtName As String, _
txtPdColPres As String, txtPdCol As String, intHeadrRow As Integer, txtGeoClaimsShtName As String)

 

[NdNoviceHlp]

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