• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

macro problem

M

markjenkins

New Member
Hi

I have managed to record a macro that copies a row and then copies it 5 times and changes some wording in one of the coloumns, but when I run it, it will only copy the row I originally done in the recording , but I need it to do every row in the spreadsheet.

I have copied the macro below, any help would be really, really appreciated, thank you.


Rows("4:4").Select

Selection.Copy

Rows("5:5").Select

Selection.Insert Shift:=xlDown

Selection.Copy

Rows("6:6").Select

Selection.Insert Shift:=xlDown

Selection.Copy

Rows("7:7").Select

Selection.Insert Shift:=xlDown

Rows("7:7").Select

Selection.Copy

Rows("8:8").Select

Selection.Insert Shift:=xlDown

Selection.Copy

Rows("9:9").Select

Selection.Insert Shift:=xlDown

Range("D5").Select

ActiveCell.FormulaR1C1 = _

"10 DOWNING STREET LONDON CITY 12"" x 18"" CANVAS PRINT NO FRAME"

Range("D6").Select

ActiveCell.FormulaR1C1 = _

"10 DOWNING STREET LONDON CITY 16"" x 24"" CANVAS PRINT NO FRAME"

Range("D7").Select

ActiveCell.FormulaR1C1 = _

"10 DOWNING STREET LONDON CITY 20"" x 30"" CANVAS PRINT NO FRAME"

Range("D8").Select

ActiveCell.FormulaR1C1 = _

"10 DOWNING STREET LONDON CITY 24"" x 36"" CANVAS PRINT NO FRAME"

Range("D9").Select

ActiveCell.FormulaR1C1 = _

"10 DOWNING STREET LONDON CITY 28"" x 42"" CANVAS PRINT NO FRAME"

Range("A10").Select

End Sub
 
Hi Mark ,


Can you specify exactly what you want a macro to do ?


Is it that for every row in your spreadsheet , it should insert 5 blank rows under it , and then in column D , it should put in the text you have posted in the 5 newly inserted rows ?


For how many rows should it do this ?


Narayan
 
Hi Narayan

Thank you very much for your reply it was much appreciated.

I have a spreadsheet that has 1185 rows in it, it is a stock inventory sheet, what needs to be done is each row needs to be copied 6 times ( sorry to confuse as I know in the macro I sent it is 5 )then I need to replace part of the text in column D.

The macro works fine but I don't know how to apply it to all 1185 rows.

Thank you very much for your help.

Mark
 
Hi, markjenkins!


I took the liberty of remaking the procedure and this is the result:

https://dl.dropbox.com/u/60558749/macro%20problem%20%28for%20markjenkins%20at%20chandoo.org%29.xlsm


You'll find there two options, one safe (the second one which uses another worksheet as output) and one unsafe (the first one which uses the same worksheet). As you didn't specify which was the original text in column D for the initial file, you'll see that in the 1st one you have 7 records per original entry and in the 2nd one you have 6. I hope you might be able to handle the adjustments.


Just advise if any issue.


Regards!
 
Hi SirJB7

Thank you very much for your reply and your kind work you have done, but I don't understand what I have to do with this? Appreciate your help
 
Hi, markjenkins!


Your goal was to insert 6 lines below each line in a worksheet and add 6 labels in column D. You provided a macro that wanted to be extended to all rows. I found easier to write down a new one rather than modifying yours.


When I had to choose how to do the job, I faced the issue that if you did it as you asked, you'd have to filter and delete and do things manually to revert the worksheet to its original status. So I wrote to procedures, one unsafe that follows your requirements (inserts rows in same worksheet) and another safe as I'd do the job (inserts rows in another clean worksheet).


So answering to your last question, you'll have to:

a) copy both procedures into your actual workbook

b) save the workbook and make a backup copy

c) first, run the safe version (it'll create entries in a new worksheet)

d) second, run the unsafe version (it'll create entries in the same worksheet)

e) then decide which version do you prefer and stick to it for future use.


Regards!
 
Hi

No I cant get this to work , it only copied the first two rows for some reason, I will try and upload the actual excel file so anyone who might be able to help and see the file

Thanks for all your help !
 
Can not upload the excel sheet if anyone can help my email address is mkjenkins@hotmail.co.uk

I can then send over the file , I am willing to pay somebody if they can sort this out

Many Thanks
 
Hi mkjenkins,


To upload sample file refer Sticky Post at forum's main page..

http://chandoo.org/forums/topic/posting-a-sample-workbook


or you can mail the same to me.. mail3debraj[at]gmail[dot]com


Regards,

Deb
 
Hi, markjenkins!

I've just dropped you an email. If need further help just get back to me.

Regards!
 
You must log in or register to reply here.
Back
Top