Question about how to use range in VBA

Hello,
I am trying to cut and paste one range from one sheet to the same
range in a different sheet.  I used the following code:


Range(Cells(startrow, 1), Cells(endrow, 12)).Select
Selection.Copy

Worksheets("SUMM").Activate
Range(Cells(startrow, 1), Cells(endrow, 12)).Select   --- ERROR HERE
ActiveSheet.Paste

This code gives me an error where indicated: Application defined or
Object defined error

Notice that I use the exact same line of code three lines earlier and
I get no error.  If I change my code to read as

Range(Cells(startrow, 1), Cells(endrow, 12)).Select
Selection.Copy

Worksheets("SUMM").Activate
Range("A12").Select   --- NO ERROR
ActiveSheet.Paste

I get no error.  What am I doing wrong?

Also, one other question.  When I write Range(Cells(startrow, 1), Cells
(endrow, 12)).Select
I get a bunch of highlighted cells.  How do I unselect them in VBA?

thanks
0
Andrew
1/22/2010 12:24:30 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
875 Views

Similar Articles

[PageSpeed] 46

Hi

Not sure why the code gives you an error. Which kind of error do you
get.

Your code can be reduced to this single statement, and then you have
no highlighted cells:

Range(Cells(startrow, 1), Cells(endrow, 12)).Copy Worksheets
("SUMM").Cells(startrow, 1)

Hopes this helps.
....
Per


On 22 Jan., 01:24, Andrew <andrewkgent...@gmail.com> wrote:
> Hello,
> I am trying to cut and paste one range from one sheet to the same
> range in a different sheet. =A0I used the following code:
>
> Range(Cells(startrow, 1), Cells(endrow, 12)).Select
> Selection.Copy
>
> Worksheets("SUMM").Activate
> Range(Cells(startrow, 1), Cells(endrow, 12)).Select =A0 --- ERROR HERE
> ActiveSheet.Paste
>
> This code gives me an error where indicated: Application defined or
> Object defined error
>
> Notice that I use the exact same line of code three lines earlier and
> I get no error. =A0If I change my code to read as
>
> Range(Cells(startrow, 1), Cells(endrow, 12)).Select
> Selection.Copy
>
> Worksheets("SUMM").Activate
> Range("A12").Select =A0 --- NO ERROR
> ActiveSheet.Paste
>
> I get no error. =A0What am I doing wrong?
>
> Also, one other question. =A0When I write Range(Cells(startrow, 1), Cells
> (endrow, 12)).Select
> I get a bunch of highlighted cells. =A0How do I unselect them in VBA?
>
> thanks

0
Per
1/22/2010 12:50:05 AM
If your code is in a general module, then the unqualifed ranges refer to the
activesheet.

But I'm guessing that the code is behind a worksheet.  That means the
unqualified ranges belong to the sheet that owns the code.

And since you've selected a different sheet, you're trying to select a range on
a non-active sheet.

worksheets("summ").activate
worksheets("Summ").range(worksheets("Summ").cells(startrow, 1), _
                         worksheets("Summ").cells(endrow,12)).select

will work.

Or

with worksheets("summ")
  .activate
  .range(.cells(startrow,1), .cells(endrow,12)).select
end with

if you want to save typing.

Remember that you don't need to select an object/range to work with it.


Andrew wrote:
> 
> Hello,
> I am trying to cut and paste one range from one sheet to the same
> range in a different sheet.  I used the following code:
> 
> Range(Cells(startrow, 1), Cells(endrow, 12)).Select
> Selection.Copy
> 
> Worksheets("SUMM").Activate
> Range(Cells(startrow, 1), Cells(endrow, 12)).Select   --- ERROR HERE
> ActiveSheet.Paste
> 
> This code gives me an error where indicated: Application defined or
> Object defined error
> 
> Notice that I use the exact same line of code three lines earlier and
> I get no error.  If I change my code to read as
> 
> Range(Cells(startrow, 1), Cells(endrow, 12)).Select
> Selection.Copy
> 
> Worksheets("SUMM").Activate
> Range("A12").Select   --- NO ERROR
> ActiveSheet.Paste
> 
> I get no error.  What am I doing wrong?
> 
> Also, one other question.  When I write Range(Cells(startrow, 1), Cells
> (endrow, 12)).Select
> I get a bunch of highlighted cells.  How do I unselect them in VBA?
> 
> thanks

-- 

Dave Peterson
0
Dave
1/22/2010 1:26:43 AM
Reply:

Similar Artilces:

asap utilities question
I have Msft. Excel Vsn 2002 and latest vsn. of ASAP Utilities. I used ASAP Utilities to merge single rows of data together so that more than one row will fit on a single line of text. My question now, is, is it possible using ASAP Utilities to separate the data that has been merged back into single rows of data? All information welcomed! Aaron ...

Isinteg Warning Message Question
I ran isinteg -pri -test alltests. I came up with 506 warnings. There are basically 4 types of warnings. They are below. Are these something I need to worry about? What should I do? thanks stewart sschwartz@nal.usda.gov ================================== Warning: MsgFolder 165 (Fid=0001-0000000D1864, Mid=0001- 0000000D559D, Inid=0001-000000F33436): PR_READ_RECEIPT_REQUESTED(0029000B) prop in Messages table and MsgFolder table do not match. Warning: MsgFolder 4 (Fid=0001-0000086DC911, Mid=0001- 00000436F972, Inid=0001-000004A583F1): Error JET_errRecordNotFound seeking to INID for this ...

VBA to check for latest version of front end?
I have a split database where the users have a local copy of the frontend. I would like to use VBA to check whether the users have the mostrecent version. I have set up the following tables:tVersion-Back-End (link to table in the BackEnd version)tVersion-FrontEnd (local table in the FrontEnd version)Each has a field Version where I am planning to put in the versionnumbers.Now, I am just starting to use VBA in my Access projects. Can someonepoint me to some coding to accomplish my task above? Can someone alsopoint me to a good resource (book, website, course) that would take mefrom a beginn...

VBA training suggestions?
I'm looking for tips on software out there on learning VBA for Excel. I know how to create macros and have a good idea of what a script might look like for the macro I created. I just want to learn how to write them on my own for my personal use. Any suggestions? Thank you kindly for your input! I do not know of any software for learning VBA, but you can't go wrong looking at all the Excel VBA web sites as well as purchasing a copy of John Walkenbach's "Excel 2003 Power Programming with VBA" http://j-walk.com/ss/. Recording macros and then editing them is a pow...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

VBA to creating autotext entries or quickparts in different catego
I have a VBA application that basically allows people to easily create autotext entiries, move them between machines and use them making comments on assignments. Currently it operates in EXACTLY the same way in Word 2003 and 2007 (using userforms) and I want to keep that as long as possible. You can see the application at http://emarking-assistant.baker-evans.com and either the screen image or the video demos will give you an idea of what I am doing Currently I store all the comments in a long list of autotext entries that is displayed in a field with the value of the entr...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Non-VBA way of making custom menus.
Sorry for the new thread but even on Google, the thread isn't showing up. Well, it was SOOO easy, as I knew it would be. The webpage I quoted in my message this morning didn't mention the "New Menu" at all! So, here is the non-VBA way to create a custom menu: - TOOLS > CUSTOMIZE - under the categories available choose NEW MENU and then drag the NEW MENU option under the Commands window up to the menu bar (like D'UH!! <lol> Wish all those hours spent searching yielded webpages that gave this! <g>) - THEN one can go back to the MACROS category a few line...

Dotfuscator question
Hello Everybody !!! When i create programm using C# i must understand that my programm from exe - module can be converted to the source file by Reflector. In Order do not allow do this i must use dotfuscator, but i did not find any free normal dotfuscator. May be someone tell me what can i do cause now as think we have interesting paradox. We 've got free technology but if we want to protect our products we must buy expensive commercial dotfuscators. Alex Dmitriev You should post this message in a newsgroup which discusses managed code. This newsgroup does not have much managed traff...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

question on the rules wizard
When clikcing on the Rules Wizard, Outlook locks up - consistently. Has anyone got a remedy for this? Thank you, rich rpage@concerto.com ...

OE questions
Ok, I have two questions: 1. With Thunderbird I can use this with a hotkey manager to compose a new message with Tbird "D:\Program Files\Mozilla Thunderbird \thunderbird.exe -compose" -- how can I do the same for Outlook Express and Outlook 2003? 2. When I go to load Outlook Express I get this error message, followed by the error message below it: http://i180.photobucket.com/albums/x296/gwar_1/Untitled-3.png how can I fix this and use Outlook Express? Thanks! posted to the outlookexpress newsgroup via crosspost --=20 Peter Please Reply to Newsgroup for the benefit of others Re...

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

HQ Licensing question V.2
I have a client running HQ client at 8 stores + HQ server at the warehouse. They would like to be able to use transfers from the warehouse to the stores, so I would have to setup a new PC running Store Ops and a new database, however, in order for the transfers to take place, I need a HQ Client License correct? Do I also need a POS key? I appreciate any insights you may have. Thanks in advance Hi Phil yes - you'll need an HQ Client license so it can exchange with your headquarters - "export" the wh database as you would any store from HQ no - you won't need a PO...

Help with simple(?) VBA function
I'm trying to selectively BOLD cells by the use of a User-Defined function. No joy. The VBA Help topics suggest something like this: Function Bold() Worksheets("Sheet1").Range("A1:A5").Font.Bold = True End Function When I try to use it the referenced cells are not changed and the function returns "0". Can anyone point this VBA neophyte in the right direction? Thanks, -Dick- Hi Dick, A function can only return a value. Macros and Functions (Macros as Opposed to Functions) http://www.cpearson.com/excel/differen.htm If all...

MS Project 2007 question
Hello, I am developing a simple schedule with no resources but 2 tasks out of 100 are hazardous and I would like to make the schedule so that when these tasks occur, no other tasks can occur. In other words, even though all tasks are moving around as the schedule is being developed, I would like to make sure no other tasks can be scheduled wherever these 2 hazardous tasks fall. These 2 tasks are serial to every other task in the schedule. Is there a way to do this? Thanks for your help. Mike Create two milestones. Use milestone 1 as the successor task to all tasks th...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

SQL 2008 running on a VM using all allocated memory
Hi, I've got a sql2008 server running on a VM. There's 9GB of physical RAM, which 7GB have been allocated to SQL Server. But when i look at task manager, i see that the SQL server is actually using all 7GB, which is pegging the memory usage of the overal box at above 90% used. We're mostly a sql2005 shop, and none of those servers are doing this. I have sql2005 running on VM's, someone actual servers as a named or default instance, and some even clustered. None of them have this problem. Is this a normal thing with sql2008 only? Any insight would be greatly appre...