Copy and Paste from One workbook to another

I

ipon70

Board Regular
Joined
May 8, 2013
Messages
86
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

C
  • Question Question
If User Selects Cancel All Sheets are Unprotected
Replies
4
Views
130
christianbiker
C
G
  • Question Question
Disable Cut Copy Paste
Replies
1
Views
717
jkpieterse
jkpieterse
B
  • Question Question
Copy and paste from one worksheet into a new workbook and save to a particular directory
Replies
3
Views
572
Joe4
Joe4
H
  • Question Question
Advanced Multi VBA - Creating multiple temporary files and attach them to one email on outlook
Replies
2
Views
262
Hayf13
H
I
  • Question Question
Vba has stopped adding hyperlink to my customers name
Replies
3
Views
69
ipbr21054
I

Forum statistics

Threads
1,223,884
Messages
6,175,177
Members
452,615
Latest member
bogeys2birdies

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