Copy rows from sheet 1 to sheet 2Solved/Closed

Question

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

venkat1926 · Answer

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

venkat1926 · Answer

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.

venkat1926 · Answer

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.

venkat1926 · Answer

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.

venkat1926 · Answer

not clear whether the problem is solved.  if not send a private message to me.

venkat1926 · Answer

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.

lenpaint · Answer

I need to copy sheet 1 to a blank sheet sheet 2

venkat1926 · Answer

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.

Copy rows from sheet 1 to sheet 2

8 responses

Subscribe To Our Newsletter!