Code works fine once, but not twice

[celias]

Hello, all!
I am taking my first baby-steps on visual basic. I need one main file (Tax_Receipts) to get data from two other files ("Amount" and "Contact"). The main file has sheets named as the two other files. I am creating a macro to clean the data on those two sheets and then get the new info from sheet1 on each on of the other two files.

I first tried just with file "Amount" and the code below worked just fine:

Sub ClearImportDataAndPaste()'Same as ImportDataAndPaste but also cleans target sheet before pasting


'First, we declare two variables of type String, a Worksheet object and one variable of type Integer.
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer, mainWB As Workbook
Set mainWB = Workbooks("Tax_Receipts.xlsm")


 
' Turn off screen updating and displaying alerts.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
 'Cleatr contents of sheet4
 mainWB.Sheets(4).UsedRange.Clear
              
        ' Import the sheets from the "Amount" Excel file into TaxReceipts.xlsm.
            Workbooks.Open ("C:\Users\Cel\DonorsInfo\Amount.xlsx")
            
              
            Workbooks("Amount.xlsx").Worksheets(1).UsedRange.Copy
            mainWB.Activate
            mainWB.Sheets(4).Range("A1").Select
            mainWB.Sheets(4).Paste
           mainWB.Sheets(4).Range("A1").Select
           
           Workbooks("Amount.xlsx").Close SaveChanges:=False         
            
'Turn on screen updating and displaying alerts again
Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

Then I added the same process to do the same with the file "Contact". However, not it won't work. Here is the code:

Sub ImportContactAndAmount()'Same as ClearImportDataAndPaste applied twice for the files and sheets "Amount" and Contact"


'First, we declare two variables of type String, a Worksheet object and one variable of type Integer.
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer, mainWB As Workbook
Set mainWB = Workbooks("Tax_Receipts.xlsm")
'Set mainWB = "C:\Users\Cel\Tax_Receipts.xlsm"

 
' Turn off screen updating and displaying alerts.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
'Clear contents of Amount
 mainWB.Sheets("Amount").UsedRange.Clear
 
'Clear contents of Contact
 mainWB.Sheets("Contact").UsedRange.Clear


 
         ' Import sheet1 from the "Contact" Excel file into TaxReceipts.xlsm - "Contact" sheet
            Workbooks.Open ("C:\Users\Cel\DonorsInfo\Contact.xlsx")
            
              
            Workbooks("Contact.xlsx").Worksheets(1).UsedRange.Copy
            mainWB.Activate
            mainWB.Sheets(2).Range("A1").Select
            mainWB.Sheets(2).Paste
           mainWB.Sheets(2).Range("A1").Select
           
           Workbooks("Contact.xlsx").Close SaveChanges:=False
        
        ' Import sheet1 from the "Amount" Excel file into TaxReceipts.xlsm - "Amount" sheet
            Workbooks.Open ("C:\Users\Cel\DonorsInfo\Amount.xlsx")
            
              
            Workbooks("Amount.xlsx").Worksheets(1).UsedRange.Copy
            mainWB.Activate
            mainWB.Sheets(3).Range("A1").Select
            mainWB.Sheets(3).Paste
           mainWB.Sheets(3).Range("A1").Select
           
           Workbooks("Amount.xlsx").Close SaveChanges:=False
 
            
'Turn on screen updating and displaying alerts again
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

I get the error: Run-time error '1004': Select method of range class failed

It points to Line 56: mainWB.Sheets(2).Range("A1").Select

Can any of you tell me WHY? Thank you in advance for any help you provide.

 

[MARK858]

Sheets(2) is probably not the active sheet and you can't "Select" a cell on an inactive sheet.

Try changing

    mainWB.Sheets(2).Range("A1").Select
            mainWB.Sheets(2).Paste
           mainWB.Sheets(2).Range("A1").Select

to

mainWB.Sheets(2).Range("A1").PasteSpecial

or you can do

Workbooks("Contact.xlsx").Worksheets(1).UsedRange.Copy mainWB.Sheets(2).Range("A1")

as one line.

If you must actually move to the cell use..

Application.Goto mainWB.Sheets(2).Range("A1")

 

[celias]

Thank you so much, @MARK858. Most helpful! I used your 1st and 3rd suggestions and now it is working perfectly. It made my day! Thanks again and hope you have a great day!

 

[celias]

This is solved. Should I be marking this thread as SOLVED? How do I do that?

 

[MARK858]

No you don't need to mark threads "Solved" here. You just (if you wish) say thank you, which you have already done