# Copy rows from sheet 1 to sheet 2

Solved/Closed
Pooven - Mar 31, 2010 at 09:24 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 24, 2010 at 08:47 PM
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
Related:

## 8 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 1, 2010 at 10:24 PM
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
```
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
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 2, 2010 at 08:38 PM
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.
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.
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 3, 2010 at 08:12 PM
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.
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
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 4, 2010 at 09:02 PM
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)
Set r1 = Range(dest, dest.Offset(j - 1, 0))
r1.PasteSpecial
End With
Next c
application.cutcopymode=false
End Sub  ```

also check whether there are sheets named sheet1 and sheet2 in your workbook.
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

Didn't find the answer you are looking for?

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 5, 2010 at 08:30 PM
not clear whether the problem is solved. if not send a private message to me.
No it is not,

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

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 5, 2010 at 10:12 PM
save this sample file in our hard disk.
go to your browser and click
https://authentification.site
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.
I need to copy sheet 1 to a blank sheet sheet 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 24, 2010 at 08:47 PM
poovem

you have given the web page address . there is no file of yours ther
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-