Copy/paste without clipboard "Object required" ?

SnakZ

New Member
Joined
Feb 12, 2015
Messages
5
Windows 7 excel 2007
Im real new at VBA (but do know some PHP if that helps lol)

ANYWAY a website had a way to copy and past without using the clipboard link here: "Optimize Slow VBA Code. Speed Up Efficient VBA Code/Macros"
Code:
Sub Testing()
Sheet1.Range("A1").Copy Destination:=Sheet2.Range("B1")

End Sub

When i use this code i get an "Object required" and if i use "Option Explicit" i get "Variable not defined"

Only thing i can think of is i need to use a "Set" or "Dim" of some type at the top but that where im lost at lol

If i use it on the same sheet there no errors

Ty for any help one can give
 
Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Do you have both a Sheet1 and Sheet2? Note that they are not the sheetnames they are the names outside the brackets when you look in the project explorer window in the VBE.
 
Upvote 0
I'm not sure what your saying. The workbook is name something different but there is a Sheet1 and a Sheet2 in the workbook
 
Upvote 0
You would get that error if there aren't sheets with codenames Sheet1 and Sheet2 in the workbook with the code in it. In the VB Editor, if you look at the sheets in the Project Explorer window, the name before the name in the parentheses is the codename. So, if you see:
Sheet1 (blah)

Sheet1 is the codename, and blah is what appears on the worksheet tab.
 
Upvote 0
Does this work?
Code:
Sub Testing()
Sheets("Sheet1").Range("A1").Copy Destination:=Sheets("Sheet2").Range("B1")

End Sub
 
Upvote 0
Both of the above scripts work for me. I'm using excel 2013. Not sure what changes have been made since Excel 2007
 
Upvote 0
Both of the above scripts work for me. I'm using excel 2013. Not sure what changes have been made since Excel 2007

They both will work if your sheetname and codename are the same.See Rory's post.
 
Upvote 0
Does this work?
Code:
Sub Testing()
Sheets("Sheet1").Range("A1").Copy Destination:=Sheets("Sheet2").Range("B1")

End Sub


that did the trick for me ty but now i got one more thing on a merged cell it says "We can't do that to a merged cell"
is there anyway for it to work or is it just a dead end ? :D
 
Upvote 0
Try this:
Code:
Sheets("Sheet1").Range("A1").Copy Destination:=Sheets("Sheet2").Range("B1").MergeArea
 
Upvote 0
You would get that error if there aren't sheets with codenames Sheet1 and Sheet2 in the workbook with the code in it. In the VB Editor, if you look at the sheets in the Project Explorer window, the name before the name in the parentheses is the codename. So, if you see:
Sheet1 (blah)

Sheet1 is the codename, and blah is what appears on the worksheet tab.

I didnt know that :D but i did make sure there was a Sheet1(Sheet1) and a Sheet2(Sheet2) on the Project Expolorer (new workbook and it still gave the same error even does it on a 2013 excel idk :D


User "My Aswer Is This"
That did the trick for me ty

i still dont know why i got the error but im happy now everything works :D ty you all for the help

 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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