Copy/paste without clipboard "Object required" ?

[SnakZ]

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"

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

 

[MARK858]

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.

 

[SnakZ]

I'm not sure what your saying. The workbook is name something different but there is a Sheet1 and a Sheet2 in the workbook

 

[RoryA]

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.

 

[Norie]

Does this work?

Sub Testing()
Sheets("Sheet1").Range("A1").Copy Destination:=Sheets("Sheet2").Range("B1")

End Sub

 

[My Aswer Is This]

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

 

[MARK858]

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.

 

[SnakZ]

Does this work?

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

 

[My Aswer Is This]

Try this:

Sheets("Sheet1").Range("A1").Copy Destination:=Sheets("Sheet2").Range("B1").MergeArea

 

[SnakZ]

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