Error pasting a worksheet from one excel workbook to another workbook

Question

From a VBA project in Excel, I want to open another workbook, copy the contents of a worksheet and paste it into a worksheet where my project is running. I am basically trying to create a restore feature.

I created the following macro to do the job which worked.

Sub CopyandPaste()
'
' CopyandPaste Macro
'

'
    Workbooks.Open Filename:= _
        "C:\ Backup-File.xlsm"
    Cells.Select
    Selection.Copy
    Windows("Current_File.xlsm").Activate
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

End Sub

I then added the code to my VBA project. I am getting a “Run-time error 1004 – PasteSpecial method of class failed” on the line:

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

If I use “ActiveSheet.Paste”, the data is pasted in to the Current_File.xlsm but my formulas are lost.

Can anyone help. I have scoured this and other forums and cannot find where I am going wrong. I suspect I am close to the solution but nothing is working.

Goeff

Answer 1

Try this code,

Sub CopyandPaste()
'
' CopyandPaste Macro
'
Dim rng As Range
'
     Workbooks.Open Filename:= _
        "C:\ Backup-File.xlsm"
   ' Cells.Select
  ActiveSheet.UsedRange.Copy
    Windows("Current_File.xlsm").Activate
    'Cells.Select
    Range("A1").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Answer 2

I'd go as follows

Sub CopyandPaste()
    Windows("Current_File.xlsm").ActiveSheet.UsedRange.Clear ' clear previous content in "active sheet of "Current_File"

    Workbooks.Open Filename:= "C:\Backup-File.xlsm"
    ActiveSheet.UsedRange.Copy destination:=Windows("Current_File.xlsm").ActiveSheet.Range("A1") ' copy "Backup-File" "active" sheet used range and paste them to "Current_File" "active" sheet
End Sub