Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Copy rows from sheet 1 to sheet 2 [Solved/Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 -
I need to have a macro that will copy rows from sheet1 to sheet 2 . The catch is from sheet 1 col A for eg A3 may have the number 10 in it. I need to copy that row and paste it 10 times on sheet 2 and then copy the next row from Sheet 1 A4 and if it has 2 in that column paste it 2 times on Sheet 2 and so on.

Any help will be appreciated

8 replies

Answer 1 / 8
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 795
I presume you have data (numbers ) only from A3 down
see the sample data below (this is in sheet1)

A3 is 10
A4 is 2

now try this macro and see sheet 2

Sub test() 
Dim r As Range, c As Range, dest As Range
Dim j As Long, r1 As Range
Worksheets("sheet1").Activate
Set r = Range(Range("A3"), Range("A3").End(xlDown))
For Each c In r
j = c.Value
c.EntireRow.Copy
With Worksheets("sheet2")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Set r1 = Range(dest, dest.Offset(j - 1, 0))
r1.PasteSpecial
End With
Next c
application.cutcopymode=false
End Sub
Report

I get error 400, if I do an error catch I get Method Range of Obeject_worksheet failed. Tried with a blank work book with just the two number in col a still same result.

Thank you for yor help
Answer 2 / 8
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 795
I have checked before sending the macro. I again checked it is ok

which line of the macro gives trouble.?

check whether the sheet names are exactly correct.
Report

I belive it is with the paste, when I run the macro the row A4 in Sheet1 is highlighted like it would be if i was copying it, but in the status bar it says select destination and press enter to choose paste. Should have some features enabled on the work book. I saved the file as a macro enabled file, also checked work book names and changed 's' in sheet1 to capital but did not change anyhting. I can try from a differrent computer on Monday and see if it will work .

Thanks you agian.
Answer 3 / 8
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 795
It cannot be due to "paste" . The file is unsaved file. I checked in my excel 2007 version it works perfectly. It is true that in the status bar the line "select destination ...." comes up. I ignore it and the macro continued

I suggest one thins go the vb editor. go to the macro keep the cursor within the macro. Nor hit F8(function key F8) successively some where it stops and an error message comes up. tell me in which code statement it stops and also any error message.

I hope you only copy pasted the macro and not typed it in which case there is possibility of spelling or grammar (excel spelling and excel grammar) mistakes.
Report

It stops on

Set r1 = Range(dest, dest.Offset(j - 1, 0)) --once this is done press F8 again get the message below

Run time error 1004
Application-defined or object-defined error
Answer 4 / 8
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 795
I have added a couple of msgbox . when the macro comes to this msgbox it will give a message . note down that message and post back. 
Sub test()   
Dim r As Range, c As Range, dest As Range  
Dim j As Long, r1 As Range  
Worksheets("sheet1").Activate  
Set r = Range(Range("A3"), Range("A3").End(xlDown))  
For Each c In r  
j = c.Value  
c.EntireRow.Copy  
With Worksheets("sheet2")  
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)  
msgbox dest.address  
Set r1 = Range(dest, dest.Offset(j - 1, 0))  
msgbox r1.address  
r1.PasteSpecial  
End With  
Next c  
application.cutcopymode=false  
End Sub


also check whether there are sheets named sheet1 and sheet2 in your workbook.
Report

Sheets as named sheet1 and sheet2, created a new workbook with just the two numbers in sheet1 A3 and A4.

$A$2 is what I get after running the macro and doing a step through.

Thanks again
Answer 5 / 8
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 795
not clear whether the problem is solved. if not send a private message to me.
Report

No it is not,

Sheets are named correctly and after running the new macro I get

$A$2 adn then error 400
Answer 6 / 8
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 795
save this sample file in our hard disk.
go to your browser and click
https://authentification.site
here you can upload your file. do not give any password. delete the password. given there.
this webpage will give you the name of that url where the file is parked.
post the url. let me see what is the problem

it is intriguing when it works in my computer and not yours.
Answer 7 / 8

I need to copy sheet 1 to a blank sheet sheet 2
Answer 8 / 8
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 795
poovem

you have given the web page address . there is no file of yours ther
first save your file
goto www.speedyshare.com
there there is "choose file" and click that and goto your file in your disk and follow instsruction
you will get the complete url of your file parked in speedyshare. the name of this will end with the name of your file
move cursor to password and click delete key
and go back the name of the web page where the file is parked(ending in your fiel name) and hit contr;;l+C
go to reply to my message in the forum postings and click control+v. so that I can get your file

lenpaint-
it would have been advisable if you had opened a new thread yourself.
what do you want. you just want to copy sheet1 as it is to sheet2.
then best will be righclick sheet 1 tab
click copy of move
choose "create a copy" at the bottom
at the top "to book"
choose your file name there.
you will get a copy of sheet 1 named sheet1(2)
change the name of this copied sheet.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!