MACRO FOR PASTING A ROW 28 TIMES N KEEP 1 BLANK ROW in BETWEEN PASTES

[sara2009]

Hii i have so mANY saleS invoices to organise before i import into a database. please give me a macro for this:

data range is in sheet2 in A2:A255 and C2:C255, D2:D255. each row has unique text data, i need to paste to sheet1 each sheet2 row 28 times followed by a blank row after every 28rows of data. e.g row2 shud be pasted 28times from A2 to A29,D2 toD29,C2 to C29 then have 1 rowspace then paste Row3 28 times from A31 to A58 for column C and D as well and have 1 row space and go on for the rest of the rows.

 

[AlphaFrog]

[color=darkblue]Sub[/color] Paste_28_Rows()
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], j [color=darkblue]As[/color] Long
    j = 2
    [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] 255
        Sheets("Sheet1").Range("A" & j).Resize(28).Value = _
            Sheets("Sheet2").Range("A" & i).Value
        Sheets("Sheet1").Range("C" & j).Resize(28, 2).Value = _
            Sheets("Sheet2").Range("C" & i).Resize(, 2).Value
        j = j + 29
    [color=darkblue]Next[/color] i
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[sara2009]

It worked!!thank you alphafrog!! I nealrly gave up organising this data! Love macros!!

 

[tusharm]

Macros have their value but they come with accountability, security, and maintenance issues.

Here's how to do what you want w/o any code.

In Sheet1 cell A2 enter the formula =IF(MOD(ROW()-ROW($A$1),29)=0,"", INDEX(Sheet2!A$2:A$5,1+INT((ROW()-ROW(Sheet1!$A$1))/29)))

Adjust A2:A5 to your actual range. A2:A5 was my test range in Sheet2.

Copy A2 down column A to cell A{29*#rows with data in Sheet2}. For confirmation go down by a few more cells and you should see at least 1 #REF! error.

Select column A2:A{whatever}, copy, select C2:D2, and paste.

It worked!!thank you alphafrog!! I nealrly gave up organising this data! Love macros!!