copying rows from next sheet over

I'm trying to copy a range of rows from one sheet over from the activ
sheet, and paste them onto another sheet.

This is what I tried:

ActiveSheet.Next.Range("a2:b100").select
selection.copy

However, I'm getting the error "selection method of the range clas
failed"
What am I doing wrong, and is there an alternative way to do this?

Any help would be appreciated

--
ayl32
-----------------------------------------------------------------------
ayl322's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=984
View this thread: http://www.excelforum.com/showthread.php?threadid=48723

0
11/22/2005 3:11:24 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
129 Views

Similar Articles

[PageSpeed] 37

try
Sheets(ActiveSheet.Index + 1).Range("a2:b100").Copy

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"ayl322" <ayl322.1ywm2y_1132672500.9836@excelforum-nospam.com> wrote in 
message news:ayl322.1ywm2y_1132672500.9836@excelforum-nospam.com...
>
> I'm trying to copy a range of rows from one sheet over from the active
> sheet, and paste them onto another sheet.
>
> This is what I tried:
>
> ActiveSheet.Next.Range("a2:b100").select
> selection.copy
>
> However, I'm getting the error "selection method of the range class
> failed"
> What am I doing wrong, and is there an alternative way to do this?
>
> Any help would be appreciated!
>
>
> -- 
> ayl322
> ------------------------------------------------------------------------
> ayl322's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=9846
> View this thread: http://www.excelforum.com/showthread.php?threadid=487235
> 


0
Don
11/22/2005 3:53:52 PM
it works beautifully!  thank you

--
ayl32
-----------------------------------------------------------------------
ayl322's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=984
View this thread: http://www.excelforum.com/showthread.php?threadid=48723

0
11/22/2005 4:49:32 PM
I probably should have added that you do not need to do any selections. This 
line will work by itself from the destination sheet.

sub copyit()'all you need

Sheets(ActiveSheet.Index + 1).Range("a2:b100").Copy range("a1")
end sub



-- 
Don Guillett
SalesAid Software
donaldb@281.com
"ayl322" <ayl322.1ywqhb_1132678202.197@excelforum-nospam.com> wrote in 
message news:ayl322.1ywqhb_1132678202.197@excelforum-nospam.com...
>
> it works beautifully!  thank you~
>
>
> -- 
> ayl322
> ------------------------------------------------------------------------
> ayl322's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=9846
> View this thread: http://www.excelforum.com/showthread.php?threadid=487235
> 


0
Don
11/22/2005 7:39:44 PM
Reply:

Similar Artilces:

"Do you want to move or copy files from this zone?"
I cannot get rid of the message above on a single system running Windows XP. It seems to be only on this one system Tried: Open Control Panel -> Internet Options. Open Security-tab; mark the zone "Local intranet", press the "Sites" button, UN-tick "Automatically detect intranet network",THEN tick "Include all local (intranet) sites not listed in other zones" and tick "Include all network paths (UNCs)". And http://social.technet.microsoft.com/forums/en-US/itproxpsp/thread/59c3397d-b071-4618-9252-0269e4f316ab/ I bel...

How to delete rows
I have a work sheet that has many rows like this and 3 coloums 1 chair custom 24 2 table custom 0 3 spoon custom 3563 4 hat custom 0 now I would like to make another colom and only extract those that their sum is greather than zero. in other words I would like to delete rows 2 and 4 how can i do that using if-function or any other function tnx khers If you have many rows, first sort the data by the numeric field. This will gather all the zeros together and the rows can be deleted with on...

Hundreds of Blank rowas below my content-filled rows
Hey y'all, Somehow my spreadsheet, which contains about 350 rows of populated data, includes rows all the way down to 2500. This makes the scroll bar very small, and it's just plain annoying. I've tried deleting, hiding, setting print area, doing the edit-go to-special-blank-delete thing, and nothing works. This doesn't affect data quality or even print range, but it makes the scrolling hard to do, and it's bugging me. How do I fix this? Debra Dalgleish shares a few ways here: http://contextures.com/xlfaqApp.html#Unused Laura wrote: > > Hey y'...

flip a sheet format
Hi, I am looking for a solution where I could change the table in a different format. Any office product solution, Excel or Access or any other tool could be used where the process can be automated (macro, SQL query, etc) I have this table: T1 App1, App2, App3 T2 App4, App5 T3 App6, App7, App8 I want to create this table of the previous table: App1 T1 App2 T1 App3 T1 App4 T2 App5 T2 App6 T3 App7 T3 App8 T3 Thank you for any help Hi The following code should do what you want. Sub ChangeLayout() Dim i As Long, j As Long, k As Long, lr As Long, lc As Long Dim wss As...

how do I make the first three rows repeat on top of every page?
I am using Excel 2002 and have documents with 9 colums wide and 100 to 200 rows long. I am printing in landscape mode on letter size paper. I want the first three rows of the very first page to repeat at the top of each new page that gets printed. So every time there is a page break it will re-insert the top three rows before continuing with the row sequence? Ok,,, so I am a newbe :o) Thanks for any help. StardustDave. Hi Dave Have a look at Page Setup/Sheet/Rows to repeat box HTH Michael "StardustDave" wrote: > I am using Excel 2002 and have documents with 9 colums wide...

Copy & Paste defaults
When copying text from another application into a cell in Excel, I can either choose to retain the source formatting or use the destination formatting. Does anyone know a way that I can make the Excel destination formatting the default so that it is not necssary to make the selection on every copy/paste? Jerry I don't think there is a way to default Excel to the destination formatting. The only workaround might be to paste via a macro: Sub PasteDestinFormat() ActiveSheet.PasteSpecial Format:="Text" End Sub -- Jim "Jerry" <Jerry@discussions.microsoft.com> ...

Outlook 2003
Hello, I migrate from Outlook Express 6 to Outlook 2003. I have problems with my rules: I have a first set of rules to MOVE messages to specific folders according to e-mail addresses (To: ...) AND a second set of rules to filter spam (MOVE messages to Deleted Items) This set of rules worked perfectly with OE6 (more than 90% of spam filtered). With Outlook 2003, COPIES of messages filtered remain in Inbox and specific folders of first set of rules. So I have now 3 copies of 200 junk mails per day, now... It is a real problem Why does Outlook make COPIES of the messages when I ask to MOVE t...

how do I get rid of extra rows
I have a large spreadsheet, and for some reason on one page, there are some 5000 extra rows at the bottom. There's nothing in them - I've selected them all, cleared the contents, and asked Excel to delete them, but they won't go away. They're making the spreadsheet even larger, and the page navigation that much more difficult. Why won't they go away?? Hi Sarah, One thing that you have to do to finalize things is to save the file. I find that trying to do everything without using a macro usually misses. Suggest you try the macro on Debra Dalgleish's site to fix all ...

macro to find date format in a cell and delete that entire row
macro to find date format in a cell and delete that entire row i have dates in a column and date keeps changing, i want that if macro find date in a column , it deletes that entire row help me thank u so much everyone for ue hel -- Message posted from http://www.ExcelForum.com try Sub deletedate() For Each c In Selection If IsDate(c) Then c.EntireRow.Delete Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "vikram >" <<vikram.15ix9t@excelforum-nospam.com> wrote in message news:vikram.15ix9t@excelforum-nospam.com... > macro to find date format in a ...

Formula to deduct unpaid breaks in time sheet
Hi, I have created a Time sheet to work out hours worked but need to create a column to show unpaid breaks, if they have worked more than 5 hours, in the following format 00:30 ( = 30 minutes unpaid break) and then a Formula to show total paid hours as per below. Can anyone help please; A1 = 14:00 (Start Time) A2 = 22:00 (End Time) A3 = 08:00 (Total Hours worked) A4 = 00:30 (unpaid break) FORMULA FOR A4 REQUIRED A5 = 07:30 (Paid hours) A6 = £8.00 (Hourly rate) A7 = Total Pay (Paid Hours) Rick, Try: A3: =A2-A1+(A1>A2) A4: =IF(A3>TIME(5,30,0),TIME(0,30,0),TIME(0,0,0)) A5: =...

Help building Group Expense Sheet with Equal Allocation
undefinedundefined I am trying to build a Spreadsheet that will allow me to distribut costs equally to all members of the group for vacation purposes. Fo instance, if there are 10 members, and one group member pays $100, an another pays $50, and the rest of the group does not contribute. Tha means that each member is required to pay $15, but b/c members 1 & already paid, they are entitled to reimbursement. What I want to b able to calculate is how much each person in the group owes the othe people. Obviously person one is entitled to $85 and member 2 i entitled to $35, but I want to ...

Row height changes when copying and pasting
I have several rows which are relatively large but which have a small font in the cells. When I copy and paste to another area or another worksheet, the row height decreases to fit the small font. How can I keep the row height the same when copying and pasting? I've tried several options in paste special but nothing works. I'm using Excel 97 at home and 2000 at work and they both do the same. Thanks in advance, -- BT You might just want to learn how to use the Format Painter. http://www.officearticles.com/misc/format_painter_in_microsoft_office.htm ******************* ~Anne Troy w...

In a word doucument that is a copy of a newspaper article where do you put the copyright symbol
This is some what related to the previous question about a font. (I've since found out its a custom built version). I have a Copy (with permission of the newspaper) of an Article, about a cousin of mine that was in WWII. I've been given permission to post it on my website from the Paper. They've even given me a Banner to use in jpeg format. I have saved it in PNG. since it a copyrighted article (I assume since I had to get permission), where do you put the copyright symbol? at: top left, top right, bottom right or left. And I supposed I will have to give notice of...

Questions on copying from one sheet to the other and coping every other cell.
I need to change a formula on sheet two reading... =OFFSET($B$1,0,ROW()-2) which I drag down the column and it copies text like this =B1 =C1 =D1 etc. I need to make it every other cell in the row instead of every cell. For instance now when I drag it, it will go down the column and copy text from =B1 =D1 =F1 +H1 Etc.... Second, first time ever but the columns in first row in sheet 2 is full, so I also need to continue same formula on the next sheet, sheet 3 but have it continue copying to same column on Sheet 2. How do i make this possible? Thanks! TKL -- KatyLady ---------------...

Create a button to find the next record
Hi I am trying to create a button to find the next record for a particular studentID. There is a main form and subform. I want to be able to find all relevent subforms for a particular student. The master link id is STUDENTID and the child link id is STUDENTID_FK. So for student #1, when I click on the subform and click on the button, it would cycle through all this student's records. What is the best way to set up this button? Thank you for your help in advance. You already have one that is built in. Just click the navigation button right of the record number ...

Comparing two rows
I have a list of about 15,000 items, one per row. The details of each item are in columns A to I. I need to find those rows where ALL the entries in cols A to I are the same as in the following row. I have tried putting this in col J: =IF((A893:I893)=(A894:I894),"Error","") and I get the #VALUE! error. Is there a way of doing this with a formula, or do I have to find another way. Many thanks in advance. You could try this formula instead: =3DSUMPRODUCT(--(A893:I893=3DA894:I894)) It will return a number which is the number of matching cells in corresponding colum...

copy only unhide columns
Dear Sir/Madam, I need to how do I copy all values in a worksheet EXCEPT hidden column and paste them into a new workbook. Regards, sea -- Message posted from http://www.ExcelForum.com Sean, Select your columns even the hidden ones. then press F5 click on "special" then click on visible cells only then do your copy and pasting. Regards, Cesar zapata seanyeap < wrote: > Dear Sir/Madam, > I need to how do I copy all values in a worksheet EXCEPT hidden columns > and paste them into a new workbook. > > Regards, > sean > > > --- > Message ...

Why is my row truncated in a pivot table?
my source of the pivot table has a data field with 81 words, yet only 44 words show up in the results table. How can I get the entire 81 words in the pivot table? A pivot table cell is limited to 255 characters. There's no setting you can change to increase this limit. mcmunnd1 wrote: > my source of the pivot table has a data field with 81 words, yet only 44 > words show up in the results table. How can I get the entire 81 words in the > pivot table? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Multiple windows updating multiple rows simultaneously ?
I'm maintaining an application that currently allows entry of new records or updating exisiting records. What would be involved in modifiying the application to allow multiple windows to be open simultaneously. In other words, if the user is updating or creating a record in one window, he can leave that window and go update another record in another window or create a new record in another window, returning to the other windows afterwards. How complex would this be to handle? Thanks in advance for any help. Yes, I guess that's what it would amount to. "SteveM" wrote...

How do I keep chart/data link when copying worksheet
I have a worksheet for each month within a file - each worksheet has data tables, and some charts generated from that data. With Excel 2003, when I copied a worksheet within the same file (for example, to create the July sheet, by copying the June sheet) the charts in the new (copied) July sheet correctly linked to the July data. When I do the same in Excel 2007, the charts in the July sheet link back to the June data instead. I have looked through the options and cannot see how to do this. Is the data on the same sheet as the chart? In 2003, if the chart was on a different worksheet, ...

Datagridview selected rows
I want to get the first selected row of DataGridView Selected Rows. The goal is that I would hold down CTRL or SHIFT and select multiple rows, then I could click a button or menuitem and update all selected rows with a value from the first selected row. For example in crystal reports you hold down shift and click multiple control and select Same Width and it resizes all to Width of the first one you clicked. Datagridview seems to works if you the select in order going down the grid, but from bottom to top its different. If I could find out the direction, then i could take the first ...

Copying Part of a row down part of a column
Hi, My problem is this. I have a specific row of data that I need to copy part of down a column, until the next row of data. The data is in a format 12345 abcd efgh ijkl I only need to copy the numeric portion (12345) down the column, until the next row I need to copy (same format) shows up. What's the best/quickest way to do this. It's like an =IF function, but I'm not sure of the syntax. Thanks Try this: assuming your column A is the column with your data with blank rows i between and column B is where you want the results then. =IF(A2<>"",LEFT...

pivot table row subtotals
Excel 2003 I am pretty good at pivot table but can't figure out how the have subtotals display for my 3rd row field in under my 2nd row field - they only display at the grand total line. Please see the example below. I need to get subtotals for A/P and OP under store 1 and then again under store 2. Also My calculated field do now show up in my grand totals? why not - they should work - the math is valid. thanks!!!! Div Desc Desc Ver Store 1 4 OP A/P 1 OP A/P 2 OP A/P 3 OP A/P Store 1 Total Store 2 5 OP A/P 6 OP A/P 7 OP A/P 8 OP A/P Store 2 Total Move ...

Converting varying length Vertically-aligned records to Horizontal Rows of list (tabular)
I have a Excel Spreadsheet with Records of books. The fields and data value is entered as the First and Second Column (ColA, ColB). The records are separated by 2 empty rows. Some fields are missing for some records: Some record may miss the 'author' whereas some records may miss some other fields, say ,'subject' AND 'totalpage'. The Objective is to tranform the record horizontally with Each ffield in one column with missing field blanked so that all the field are aligned in a column e.g. colA for 'Title', colB for 'Author' How can we convert varying ...

Vlookup on multiple sheets
I am copying a database fron access into excel; due to the size (100000+ lines) i have to put it on two excel sheets. i will like to do a vlookup that will search both sheets for the result i am looking for. can someone please help You need something like: =IF(ISNA(VLOOKUP($A1,Sheet1!A:F,2,0)),IF(ISNA(VLOOKUP($A1,Sheet2!A:F, 2,0)),"not present",VLOOKUP($A1,Sheet2!A:F,2,0)),VLOOKUP($A1,Sheet1! A:F,2,0)) Looks in Sheet1 first for an exact match on A1 in column A, and if not found then looks in Sheet2 for an exact match. If A1 is not in either sheet, returns "not present"....