Clash Royale CLAN TAG#URR8PPP

Error pasting a worksheet from one excel workbook to another workbook

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

2 Answers
2

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


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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.