Copy and Paste from One workbook to another

I

ipon70

Board Regular
Joined
May 8, 2013
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
I have been getting a 1004 error, as if it doesn't have anything copied. But if I open just a blank excel sheet and manually click CTRL-V it works and pastes it in.
Here is the copy code and the paste code, the line causing the error is highlighted in purple.
Both workbooks I am trying to copy and paste to are identical. Both are open just named 123_old and 678_new. They click copy and it copies the db page and that works, you then click on the paste button the new workbook, put in your password and it throws a "Run time error 1004" "Pastespecial method of range class failed".

ANY HELP OR IDEAS ARE WELCOME. I am stuck on this.


Sub Copy_data()
101:
Application.ScreenUpdating = False
Sheets("Setup").Visible = True
answer = MsgBox("Click YES to copy this sheets entire db page." & vbCrLf & "This will not delete this sheets db information." & vbCrLf & "Click YES to Continue." & vbCrLf & "Click NO to cancel.", vbQuestion + vbCritical + vbYesNo, "DB PAGE ENTRY COPY")
If answer = vbYes Then
x = InputBox("Enter your Password.", "Password Required")
If x = "123456" Then
Worksheets("db").Unprotect Password:="123456"
Worksheets("db").Range("A3:DS50000").Copy
'Worksheets("db").Protect Password:="123456"
Else
If i <= 1 Then
MsgBox "Invalid Password. Try again"
i = i + 3
GoTo 101:
Else
MsgBox "Incorrect password entered too many times. Try again later."
Exit Sub
End If
End If
End If
Application.ScreenUpdating = True
End Sub


Sub Paste_data()
101:
Application.ScreenUpdating = False
Sheets("Setup").Visible = True
answer = MsgBox("Click YES to paste all information copied" & vbCrLf & "from the other db page." & vbCrLf & "This will overwrite the current information in this db page." & vbCrLf & "Click YES to Continue." & vbCrLf & "Click NO to cancel.", vbQuestion + vbCritical + vbYesNo, "DB PAGE ENTRY PASTE")
If answer = vbYes Then
x = InputBox("Enter your Password.", "Password Required")
If x = "123456" Then
Worksheets("db").Unprotect Password:="123456"
Worksheets("db").Range("A3:DS50000").PasteSpecial
Worksheets("db").Protect Password:="123456"
Else
If i <= 1 Then
MsgBox "Invalid Password. Try again"
i = i + 3
GoTo 101:
Else
MsgBox "Incorrect password entered too many times. Try again later."
Exit Sub
End If
End If
End If
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Hi There,


The only issue I found with the code you are using is you are not telling it where to paste the data.
In the paste code I have added/changed the below to make it work. If you know the document location and file name does not change, then you could have the code open the "Save Too" and protect and close the "Copy From" files. This way all the code would be on one file not two.

VBA Code: 
Sheets("db").Select
Range("A3").Select 'Add here where you want your data to be pasted
ActiveSheet.Paste


VBA Code: 
Sub Copy_data()
101:
Application.ScreenUpdating = False
Sheets("Setup").Visible = True
answer = MsgBox("Click YES to copy this sheets entire db page." & vbCrLf & "This will not delete this sheets db information." & vbCrLf & "Click YES to Continue." & vbCrLf & "Click NO to cancel.", vbQuestion + vbCritical + vbYesNo, "DB PAGE ENTRY COPY")
If answer = vbYes Then
x = InputBox("Enter your Password.", "Password Required")
If x = "123456" Then
Worksheets("db").Unprotect Password:="123456"
Worksheets("db").Range("A3:DS50000").Copy
'Worksheets("db").Protect Password:="123456"

Else
If i <= 1 Then
MsgBox "Invalid Password. Try again"
i = i + 3
GoTo 101:
Else
MsgBox "Incorrect password entered too many times. Try again later."
Exit Sub
End If
End If
End If
Application.ScreenUpdating = True

End Sub

VBA Code: 
Sub Paste_data()
101:
Application.ScreenUpdating = False
Sheets("Setup").Visible = True
answer = MsgBox("Click YES to paste all information copied" & vbCrLf & "from the other db page." & vbCrLf & "This will overwrite the current information in this db page." & vbCrLf & "Click YES to Continue." & vbCrLf & "Click NO to cancel.", vbQuestion + vbCritical + vbYesNo, "DB PAGE ENTRY PASTE")
If answer = vbYes Then
x = InputBox("Enter your Password.", "Password Required")
If x = "123456" Then
Worksheets("db").Unprotect Password:="123456"
Sheets("db").Select
Range("A3").Select
ActiveSheet.Paste
Worksheets("db").Protect Password:="123456"
Else
If i <= 1 Then
MsgBox "Invalid Password. Try again"
i = i + 3
GoTo 101:
Else
MsgBox "Incorrect password entered too many times. Try again later."
Exit Sub
End If
End If
End If
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
dnorm said:
Hi There,


The only issue I found with the code you are using is you are not telling it where to paste the data.
In the paste code I have added/changed the below to make it work. If you know the document location and file name does not change, then you could have the code open the "Save Too" and protect and close the "Copy From" files. This way all the code would be on one file not two.

VBA Code: 
Sheets("db").Select
Range("A3").Select 'Add here where you want your data to be pasted
ActiveSheet.Paste


VBA Code: 
Sub Copy_data()
101:
Application.ScreenUpdating = False
Sheets("Setup").Visible = True
answer = MsgBox("Click YES to copy this sheets entire db page." & vbCrLf & "This will not delete this sheets db information." & vbCrLf & "Click YES to Continue." & vbCrLf & "Click NO to cancel.", vbQuestion + vbCritical + vbYesNo, "DB PAGE ENTRY COPY")
If answer = vbYes Then
x = InputBox("Enter your Password.", "Password Required")
If x = "123456" Then
Worksheets("db").Unprotect Password:="123456"
Worksheets("db").Range("A3:DS50000").Copy
'Worksheets("db").Protect Password:="123456"

Else
If i <= 1 Then
MsgBox "Invalid Password. Try again"
i = i + 3
GoTo 101:
Else
MsgBox "Incorrect password entered too many times. Try again later."
Exit Sub
End If
End If
End If
Application.ScreenUpdating = True

End Sub

VBA Code: 
Sub Paste_data()
101:
Application.ScreenUpdating = False
Sheets("Setup").Visible = True
answer = MsgBox("Click YES to paste all information copied" & vbCrLf & "from the other db page." & vbCrLf & "This will overwrite the current information in this db page." & vbCrLf & "Click YES to Continue." & vbCrLf & "Click NO to cancel.", vbQuestion + vbCritical + vbYesNo, "DB PAGE ENTRY PASTE")
If answer = vbYes Then
x = InputBox("Enter your Password.", "Password Required")
If x = "123456" Then
Worksheets("db").Unprotect Password:="123456"
Sheets("db").Select
Range("A3").Select
ActiveSheet.Paste
Worksheets("db").Protect Password:="123456"
Else
If i <= 1 Then
MsgBox "Invalid Password. Try again"
i = i + 3
GoTo 101:
Else
MsgBox "Incorrect password entered too many times. Try again later."
Exit Sub
End If
End If
End If
Application.ScreenUpdating = True
End Sub
Click to expand...
Thank you for the help, let me try with those changes and see what I get.
Thanks again.
 
Upvote 0
dnorm said:
Hi There,


The only issue I found with the code you are using is you are not telling it where to paste the data.
In the paste code I have added/changed the below to make it work. If you know the document location and file name does not change, then you could have the code open the "Save Too" and protect and close the "Copy From" files. This way all the code would be on one file not two.

VBA Code: 
Sheets("db").Select
Range("A3").Select 'Add here where you want your data to be pasted
ActiveSheet.Paste


VBA Code: 
Sub Copy_data()
101:
Application.ScreenUpdating = False
Sheets("Setup").Visible = True
answer = MsgBox("Click YES to copy this sheets entire db page." & vbCrLf & "This will not delete this sheets db information." & vbCrLf & "Click YES to Continue." & vbCrLf & "Click NO to cancel.", vbQuestion + vbCritical + vbYesNo, "DB PAGE ENTRY COPY")
If answer = vbYes Then
x = InputBox("Enter your Password.", "Password Required")
If x = "123456" Then
Worksheets("db").Unprotect Password:="123456"
Worksheets("db").Range("A3:DS50000").Copy
'Worksheets("db").Protect Password:="123456"

Else
If i <= 1 Then
MsgBox "Invalid Password. Try again"
i = i + 3
GoTo 101:
Else
MsgBox "Incorrect password entered too many times. Try again later."
Exit Sub
End If
End If
End If
Application.ScreenUpdating = True

End Sub

VBA Code: 
Sub Paste_data()
101:
Application.ScreenUpdating = False
Sheets("Setup").Visible = True
answer = MsgBox("Click YES to paste all information copied" & vbCrLf & "from the other db page." & vbCrLf & "This will overwrite the current information in this db page." & vbCrLf & "Click YES to Continue." & vbCrLf & "Click NO to cancel.", vbQuestion + vbCritical + vbYesNo, "DB PAGE ENTRY PASTE")
If answer = vbYes Then
x = InputBox("Enter your Password.", "Password Required")
If x = "123456" Then
Worksheets("db").Unprotect Password:="123456"
Sheets("db").Select
Range("A3").Select
ActiveSheet.Paste
Worksheets("db").Protect Password:="123456"
Else
If i <= 1 Then
MsgBox "Invalid Password. Try again"
i = i + 3
GoTo 101:
Else
MsgBox "Incorrect password entered too many times. Try again later."
Exit Sub
End If
End If
End If
Application.ScreenUpdating = True
End Sub
Click to expand...
Ok that appears to have worked. Thank you so much for your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question
Copy each sheet to new workbook, paste values and save to specific folder
Replies
5
Views
80
JerrExcel
JerrExcel
S
  • Solved
VBA: Am I double-calling a Sub given nested If statements? Or other inefficiencies?
Replies
8
Views
250
dreid1011
dreid1011
P
  • Question
Check two different statements on the Exit Routine
Replies
3
Views
229
Micron
M
H
  • Question
Macro to download zip, unpack and copy data
Replies
0
Views
86
HeinrichPaul
H
D
  • Solved
Copy row based on cell value and paste as text in another worksheet
Replies
2
Views
483
DH937
D

Forum statistics

Threads
1,215,069
Messages
6,122,954
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top