Copy Range of cells from active workbook to existing workbook

[praveenpatel421983]

Hi all,

I am trying to copy a range from a sheet called "Test" of active workbook to existing workbook in which a sheet called "Test" (which already exists) but I am not able to copy. Please help.

Application.ScreenUpdating = False

Dim parentWorkbook As Excel.Workbook
Dim otherWorkbook As Excel.Workbook
Dim workbookName As Variant

Set parentWorkbook = ActiveWorkbook
workbookName = "C:\New Microsoft Excel Worksheet.xlsx"

If Not workbookName = False Then
Set otherWorkbook = Workbooks.Open(workbookName)
otherWorkbook.Sheets("Test").Range("A1:G1000").Value = parentWorkbook.Sheets("Test").Range("A1:G1000").Value
otherWorkbook.Close False
Set otherWorkbook = Nothing
End If

Application.ScreenUpdating = True

 

[gallen]

Without testing, your confusion lies with the IF statement. Remove that and it should be ok:

    Dim parentWorkbook As Excel.Workbook
    Dim otherWorkbook As Excel.Workbook
    Dim workbookName As String [COLOR=#008000]'I Changed this to string as that was what it was set to[/COLOR]
    
    Set parentWorkbook = ActiveWorkbook
    workbookName = "C:\New Microsoft Excel Worksheet.xlsx"
    
   [COLOR=#008000] 'If Not workbookName = False Then 'Confusing. Not needed. WorkbookName is set in previous line and is a string and was declared as a variant[/COLOR]
    Set otherWorkbook = Workbooks.Open(workbookName)
    otherWorkbook.Sheets("Test").Range("A1:G1000").Value = parentWorkbook.Sheets("Test").Range("A1:G1000").Value
    otherWorkbook.Close False
    Set otherWorkbook = Nothing
    [COLOR=#008000]'End If[/COLOR]
    
    Application.ScreenUpdating = True 'This isn't set to false anywhere?

 

[praveenpatel421983]

Hi gallen,

It is not copying anything. I am still confused on what is going wrong in the code.

Thanks

 

[praveenpatel421983]

Ok I got it. I was not saving the changes.

Thanks for the help gallen!

 

[gallen]

Ha, yes should have spotted that. Always better if you spot it yourself. Glad it's working