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
823 Views

Similar Articles

[PageSpeed] 23

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:

Cannot log in using WindowsID (was able to before)
I just bought MS Money 2007 Deluxe yesterday. The install went smooth, without any issues. I registered a new WindowsID and was able to successfully log into MS Money. However, today when I tried to log into MS Money, the messge apeared that the "Money was not able to recognize your WindowsID" or something like it and that "You will still have access to some functions but will not be able to make online updates". Now I'm not able to make updates. Any idea what this could be? Is the authentication server for WindowsID down or something? See the post under Mon...

2 Questions
Hi I run Win 2K with Excel 2K Question 1 I have some VB code in the "Sheet 1" module of my excel file. The code is as follows: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column <> 12 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) ErrHandler: Application.EnableEvents = True End Sub I would like this code to run automatically whenever the file is opened. (I am assuming that this would remove the enable/disable dialog box option when opening the file) Can someone tell ...

Exchange 5.5 to Exchange 2003 Migration Question: No ADC??
Hello all: I have an environment where I need do do an Exch 5.5 to 2003 Migration. The domain has already been migrated from NT4 to 2003. The only thing is, is that there is no Active Directory Connector setup anywhere in the environment. That leads me to my first question I guess. How is this possible that Exch 5.5 is even working with AD? In terms of upgrading, what would recommendations be? It is a single Site. I was thinking about bringing up a new Exch 2003 server, and using "Move Mailbox" to the new exch server. Replicate all the necessary public folders, decomission the old ...

Can't use Merge feature
If I have a postcard file I've already made on the screen, when I try to pull up the "Merge" feature under Tools, it is shaded grey and I can't open it. It insists that I either form a new database list of addresses or edit the one that I want to merge. I don't want to edit the one I want to merge. I just want to MERGE it. If, however, I have just a postcard template on the screen (without it having been made into anything), I CAN pull up the "merge" feature under Tools. It is not shaded out. How do I un-shade the "merge" feature? To merg...

Restore Exchange 5.5 on 2000 using ArcServe
I've got - Windows NT 4 Server SP6 - Exchange 5.5 SP3 - ArcServeIT 6.61 with Exchange Agent I'm trying to restore the Exchange information (database, mailbox... everything) on another computer. That computer has - Windows 2000 Server SP4 - Exchange 2000 - Brighstore ArcServe 9 (build 2020) with Exchange Agent ------------------- Now the situation: I need to verify that the backup was made correctly (entirely) in order to confirm the total validity of the backup (to validate my disaster recovery plan) and to update to SP4. I tried to find documentation with both Microsoft (for Ex...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

Pivot Table Question #15
Hey all, Love pivot tables but i have a question, the table is set up as so Month Pallet# BoxesPur BoxesSold Price Frt Gross Net Jan 1 5 1.0 ..3 Formula Formula 2 5 2.0 .5 3 10 3 .15 Jan Total 10 10 6.0 ..95 So, the problem is in the Jan Total row, is there a way...

Insert an autofiltered range into another tab
I'm am having an issue when trying to insert a range from an autofilter on one sheet to another. The range has to be inserted above rows that have data in them. When I use the insert.shift:xldown, it only moves down the first column. TIA, Sub B_CreateTabs() Dim rngE As Range Dim lngLastRow As Long Dim mgrval, lobval, shtval As String mgrval = "myself" lobval = "dept" shtval = mgrval & "-" & lobval Windows("Mybook.xls").Activate Sheets(shtval).Select Sheets(shtval).Copy After:=Workbooks("Mybook.xls&quo...

How to use structured reference (ListColumns) in VBA
I want to do something simple...loop through a range retrieving values one at time and taking action on that value. This range is an Excel 2007 table column. Easy enough...BUT I want to use structured references to a table column by NAME, not numbers. I don't want to define new names, just use the table column headings. I've found ways of doing it with quotes, but that's not structured....the names in quotes do not change if the table heading is changed by the user. The formulas change if the table column heading is changed, but I want it to change in VBA to...

Error- Project is used by another user -Please select another proj
Please see the subject line, when im trying to open a particular project. it gives me following error. I checked the current users, there are no users. Apart from me. There are some non PA users,i tried to remove them from activity. inspite of me removing them from activity, im geting the same error. Help !!! Ramakrishnan Hello Ram, There is a stranded user in PA000001 table. You need to clear this User/Project combination. Ajay "Ram" wrote: > Please see the subject line, when im trying to open a particular project. > it gives me following error. > > I che...

Mock question?
Hi all Not sure if this is the right group, I am happyo to repost to correct group if I know what that is! but in the mean time - here goes I am using NMock2 and I have question. I have the following interface: public interface ITime { int Hour { get; } } and the following weather service: public class WeatherService:ITime { public string GetTodaysWeather() { if (Hour >= 0 && Hour <= 6) { return "Sunny"; } else if (Hour >= 7 &&...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

Using Excel in Mac office 2004 with Microsoft/Mac 2008:Analysis toolpack
Although I am disappointed with Office 2008 for Mac because of the absent of Analysis toolpack, I figured things out quite recently that I can still install my Mac office 2004 and use the excel package that contains the Analysis tool pack. How? ...... let me show you: 1. Insert your CD for Mac office 2004 2. When it open on your desk top, two options will be given to you to download the software (a) Office set-up assistant, and (b) Microsoft office 2004. 3. CHOOSE MICROSOFT OFFICE 2004 (Drag the icon onto your desk top) and it will start to run/download. 4. When done, this will appear as ...

How to obtain enumerated attribute declarations from a DTD using MSXML & VB?
I have a VB (VB6, not .NET) application that reads an XML file (using MSXML v3.2 parser); the XML file contains a reference to an external DTD. The DTD has numerous enumerated attribute declarations like so: <!ATTLIST MyElement MyAttribute (One | Two | Three) "One" > The VB code needs to generate dynamic pick-list for all the attributes that appear in the DTD as part of a data-entry application; each dynamically created pick list should contain all the allowable values for the attribute as defined by the DTD (and presented in some kind of multiple choice control like a comb...

Using Excel spreadsheet as input to Access
Hello, I posted this in the New Users forum but only got one answer, so thought I'd try here as well. Like so many others, I am an Excel newbie. I was a mainframe COBO programmer in another life, but that was a few years back My manager would like me to write an app that will take tracking dat from an existing Excel spreadsheet (generated by our system) but onl use a select handful of columns as input to a new Access database tha I will create. I'm guessing that I can either a) create a new edited spreadsheet to b used as input to the Access database or b) use the Import wiza...

Printing Word and excel documents via excel vba
I have a document that has some Excel and some word content i would like to use excel vba to print both a page from word and then a page from excel. I know how to select and open the word document, however i cant find any code to print page X from word. basically i need to: print page 1 of c:\xx\document1.doc print page1 of sheet1 of c:\yy\excelwkbk1.xls print page 2 of c:\xx\document1.doc print page2 of sheet1 of c:\yy\excelwkbk1.xls and so on it will require moving between word and excel - the excel bit i get, but getting back to excel from word, and printing out in word is slightly more...

Using subtotals as single data entries
Sorry about the subject--I couldn't figure out how to describe it simply. I have a large file (16,000 records) of amounts billed by roughly 10,000 service providers. A number of these providers have multiple office locations, so each record is unique to a specific office location. In other words, a provider who billed from 3 different office locations will have 3 entries. Each provider has a unique provider ID number, which stays the same regardless of which office location he is billing from. I want to be able to subtotal the amount billed by each provider for all their office locations...

Charting dynamic range
Ok let me say i fully understand how to name a range and in the source data use the formula =sheetname!rangename Problem arrises when my macro that runs an advanced filter with the source data and add data to the bottom of the range the chart reverts back to it's initial data source =sheet1!$a$4:$b$15 so even though it accept the named range intitailly once the advanced filter runs it almost ommits the named range. I am using 2007 and i never had this problem in 2003. What suggestions do you have for me? Thank you!!!!! -- Thank you, Jennifer ...

Installation question 06-05-07
I have one server with sql2005 running MOSS 2007 (sharepoint) I would like that sql server also to run CRM dynamics 3.0 pro. We are only 5 users and its a rather fast server so we believe it will go fine performance vice. Can I install SRS from the crm3 disk on a SQL2005 ? does it NEED default website and port 80 ? (the MOSS are using that) any help appreciated Hello, I suggest install SQLServer 2005 Reporting Services (SRS) manually using the SQLServer 2005 installation media. Configure SRS to use a different (new) website, having its own application pool. Then during CRM setup speci...

Using Risk+ with MSP 2007
We use Risk+ as a risk simulation tool. We have discovered that it works approximately 20 times SLOWER in MSP 2007 than MSP 2003. Has anyone else run into this problem. If so, is there a remedy? My hunch this not a generic issue, but surely best that you consult with the Risk+ people on this. --rms www.rmschneider.com On 02/03/10 18:08, Tom Mc wrote: > We use Risk+ as a risk simulation tool. We have discovered that it works > approximately 20 times SLOWER in MSP 2007 than MSP 2003. Has anyone else run > into this problem. If so, is there a remedy? W...

Historical IV Trial Balance
When running this report in Summary, I am receiving an error when trying to run with a date range. Has anyone seen this before? are you getting a duplicate error? If you are, then what you are experiencing is a bug. To fix, you will have to apply the latest service pack. Hope this helps. Rheiner "DavidInterDyn" wrote: > When running this report in Summary, I am receiving an error when trying to > run with a date range. Has anyone seen this before? I think so. I'll try that by applying SP 4 for 10.0 "Rheiner" wrote: > are you gettin...

how to use roundup
how to roundup the number to hundreds example 1250101 to 1250200 , 1250901 to 1251000 , 1250750 to 1250800 thank alot =ROUNDUP(A1,-2) -- Kind regards, Niek Otten "r4319hb" <r4319hb@discussions.microsoft.com> wrote in message news:A8D5EA24-858C-49D3-A5ED-481FEBEA36CB@microsoft.com... > how to roundup the number to hundreds example 1250101 to 1250200 , 1250901 > to > 1251000 , 1250750 to 1250800 > thank alot =CEILING(A1,100) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "r4319hb" <r4319hb@discussions.microsoft...

Using 'like' on a user prompt
I want to prompt the user for a value to use to filter a form. Here is my command with the WhereCondition: docmd.OpenForm "test", acNormal, , "LastName=[Enter a Last Name]" I want to use the WhereCondition as a "LIKE" criteria. Variations on the WhereCondition itself that I've tried include various combinations of LastName LIKE %...% LastName LIKE *...* LastName LIKE [%...%] LastName LIKE [*...*] You get the idea... No luck so far. Any thoughts? On Thu, 27 May 2010 21:52:50 GMT, "RicDon" <u60397@uwe> wrote: >I wan...

Using online services from Europe
I'm going to be temporarily stationed in Europe for a couple of months. Are there any problems with my using M2002 to access my accounts, pay bills, etc, from there just as I do from the US? TIA You should be fine, so long as you have a 128 bit encryption version of IE. If you do have problems, please feel free to post here :-) -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh...

Dynamic chart: Changing Ranges
Hi: I have a set of data, going across by months, and going down into different categories that I have to graph using stacked column charts. Since the data is going to be in the same columns, just going down a category...I'd like to know if there is a way for me to add some kind of drop-down to the chart so that users can just drop-down to CATEGORY 1, and see the chart for CATEGORY 1, without me having to build 10 different charts that i'll have to update everytime i add new data. I would appreciate any advice or feedback. Hello: Check out Jon Peltier's website. It looks ...