VBA copy and paste sheet from one workbook to the other

[Saraaga]

Hi,

I am trying to copy a sheet from one workbook to another workbook. When I debug it is opening both the workbooks and the active one is the old workbook. Please take a look and let me know where I am doing wrong. Thanks!

here is my code:
Sub CopyInfoSheet()
Const Procname = "CopyInfoSheet"
Dim StrCurrentRpt As String
Dim strInfoPath As String
Dim strInfofile As String
Dim wb As Workbook

'Get Current Report Name
StrCurrentRpt = wb.Name

'Open file with Lookup tables and import the 1st needed table to the current report
strInfoPath = "\\Reporting\ValueChange\ValueChange.xlsx"
Workbooks.Open (strInfoPath)
strInfofile = ActiveWorkbook.Name
Workbooks(StrCurrentRpt).Sheets("Review").Copy After:=Workbooks(strInfofile).Sheets("Info").Range("InfoRange") 'This is where I am getting error
Workbooks(strInfofile).Close SaveChanges:=False
End Sub

[AlphaFrog]

Delete the Range reference;
After:=Workbooks(strInfofile).Sheets("Info").Range("InfoRange")


Or try this...

Sub CopyInfoSheet()
    
    'Open file with Lookup tables and import the 1st needed table to the current report
    Application.ScreenUpdating = False
    With Workbooks.Open("\\Reporting\ValueChange\ValueChange.xlsx")
        .Sheets("Review").Copy After:=ThisWorkbook.Sheets("Info")
        .Close SaveChanges:=False
    End With
    Application.ScreenUpdating = True
    
End Sub

[Saraaga]

Thank you.
Tried to use your code and I get the below error:
Error(438) : Object doesn't support this property or method.

Please help. Thanks!

[AlphaFrog]

Thank you.
Tried to use your code and I get the below error:
Error(438) : Object doesn't support this property or method.

Please help. Thanks!

I edited the the code above. Try again with the new code.

[Saraaga]

Sorry, think I am missing something here. Where did you edit the code? Looks same to me. Thanks!

[AlphaFrog]

Sorry, think I am missing something here. Where did you edit the code? Looks same to me. Thanks!

I deleted a period before ThisWorkbook

.Sheets("Review").Copy After:=.ThisWorkbook.Sheets("Info")

[Saraaga]

It is opening the old file where Info tab is there. Looks like it is copying too as the entire range is selected.

however, it is not going back to the new file(it is already opened, but not activated).

Macro is just staying at the old file and then throwing the error. Thanks!

[AlphaFrog]

Are you getting the same error or is it different?
Are you using the new code without the period?
Is the code located in the new file?

[Saraaga]

It gives error subscript out of range.

Yes, I am using the new without the period.

Regarding the code, it is located in 3rd file which is macro file. I have raw data coming in from other system which is the 1st file. Macro does a lot in addition to this functionality. 2nd file which has Info tab(this is just information) has to be copied to the 1st file. Just to let you know I run the macro from the 1st file(raw data file).
Thanks!

[AlphaFrog]

It gives error subscript out of range.

Yes, I am using the new without the period.

Regarding the code, it is located in 3rd file which is macro file. I have raw data coming in from other system which is the 1st file. Macro does a lot in addition to this functionality. 2nd file which has Info tab(this is just information) has to be copied to the 1st file. Just to let you know I run the macro from the 1st file(raw data file).
Thanks!

Try this. It pastes to the original active workbook and not the workbook with that contains the code.

Sub CopyInfoSheet()
    
    Dim wb As Workbook
    
    Set wb = ActiveWorkbook
    
    'Open file with Lookup tables and import the 1st needed table to the current report
    Application.ScreenUpdating = False
    With Workbooks.Open("\\Reporting\ValueChange\ValueChange.xlsx")
        .Sheets("Review").Copy After:=wb.Sheets("Info")
        .Close SaveChanges:=False
    End With
    Application.ScreenUpdating = True
    
End Sub

[Saraaga]

It still gives subscript out of range.

[AlphaFrog]

Double check the exact spelling of the tab names
The Source workbook is suppose to be "Review"
The active destination workbook is "Info"

[Saraaga]

ok, I think I know where the problem is but I don't know the solution.

There are 2 excel files and one macro file.

1st excel file - it has raw data and after running macro, this is the destination file too. When I get raw data, name is ValueChanges and then I am appending the name with the date of run, so final file name is ValueChanges_09012015. Running macro from this file itself. This has Review tab.

2nd excel file has just info in it and it is located in the folder - \\Reporting\ValueChange\ValueChange.xlsx"). So everytime, I run the macro it has to open the ValueChange.xlsx and then copy "Info" sheet and paste it in the file named ValueChanges_09012015 with the same name -Info. This has to be pasted after Review tab.

Please note that the file names are ValueChange.xlsx which has Info and the raw data file ValuesChanges_09012015

Hope it is clear. Please help!

[AlphaFrog]

The workbook names shouldn't matter in this case.

So you you want to copy Info sheet and paste it after Review sheet? If yes, then that's the problem.

Your original code had it the other way around and I duplicated it. The original code copies Review and pastes it after Info.


Try this...

Sub CopyInfoSheet()
    
    Dim wb As Workbook
    
    Set wb = ActiveWorkbook
    
    'Open file with Lookup tables and import the 1st needed table to the current report
    Application.ScreenUpdating = False
    With Workbooks.Open("\\Reporting\ValueChange\ValueChange.xlsx")
        .Sheets("Info").Copy After:=wb.Sheets("Review")
        .Close SaveChanges:=False
    End With
    Application.ScreenUpdating = True
    
End Sub

[Saraaga]

Thank you. However, now it throws different error.

Run-time error '1004':
Excel cannot insert sheets into destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data and then use the copy and paste commands.

[AlphaFrog]

Can you resave the Destination workbook with the newer .xlsx file format? Then try it again.

[Saraaga]

Saved the raw file(which is the destination file), it is now throwing error:
Application-defined or object-defined error

.Sheets("Info").Copy After:=wb.Sheets("Review") -this is where code throwing error

Do you think I need to specify cells or range? Thanks

[AlphaFrog]

Do you think I need to specify cells or range? Thanks

No. The code copies the sheet and not a range. This should be easy.


Confirm what file format it was saved as and what is now the raw data destination workbook's file name.

Was the Raw destination workbook the active workbook when you started the macro?

[Saraaga]

Got it. I saved the info file with the extension .xls
and then I tried to run this macro, it worked fine.

not sure what's happening, the raw file coming from the system is in xls file, however when I am saving with the date appending, saving it as xlsx file. Anyway, it is working fine now. Thank you so much for helping me. I was going crazy.

[AlphaFrog]

You're welcome. Glad it worked out.