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:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,451
Office Version
365, 2010
Platform
Windows, Mobile
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

New Member
Joined
Feb 12, 2015
Messages
5
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Does this work?
Code:
Sub Testing()
Sheets("Sheet1").Range("A1").Copy Destination:=Sheets("Sheet2").Range("B1")

End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
Both of the above scripts work for me. I'm using excel 2013. Not sure what changes have been made since Excel 2007
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,451
Office Version
365, 2010
Platform
Windows, Mobile
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

New Member
Joined
Feb 12, 2015
Messages
5
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
Try this:
Code:
Sheets("Sheet1").Range("A1").Copy Destination:=Sheets("Sheet2").Range("B1").MergeArea
 

SnakZ

New Member
Joined
Feb 12, 2015
Messages
5
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

 

Forum statistics

Threads
1,081,515
Messages
5,359,229
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top