Previous Topic Next Topic

Copy a row and paste it down 119 times

Closed
ybiancheng Posts 3 Registration date Monday September 22, 2014 Status Member Last seen February 6, 2015 - Sep 22, 2014 at 09:07 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 29, 2014 at 01:19 AM
Hello,

I am using Mac Excel 2011 and I am quite new to Excel Marco. For the purposes of organizing research data, I need help with creating VBA codes for the following task:

Suppose I have the following Columns and Rows:

A1B1C1D1
A2B2C2D2
A3B3C3D3

I would like a macro to be able to copy and the first row down ( A1B1C1D1)119times, immediately followed by the 2nd row being copied and pasted down 119times, lastly the 3rd row being copied and pasted down 119times.

So they would look like the following:

A1B1C1D1
A1B1C1D1
A1B1C1D1
... (119th time)
A2B2C2D2
A2B2C2D2
A2B2C2D2
... (119th time)
A3B3C3D3
A3B3C3D3
A3B3C3D3
... (119th time)


I have been doing this manually and it's killing me, in addition to making many errors. If anyone could help me with it, I would be really grateful!

Thanks!!




2 responses

Answer 1 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 23, 2014 at 03:44 AM
data is like this col A to D(copy sheet 1 to shee2 also to preserve original data)

A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3


for experiment in the macro I have used m=10 that is repeated 10 time. you can change this code as you like.

macro is 

Sub test()
    Application.ScreenUpdating = False
    Dim j As Long, k As Long, m As Long
    m = 10
    Worksheets("sheet1").Activate
    j = Range("A1").End(xlDown).Row
    For k = j To 1 Step -1
        Cells(k, 1).EntireRow.Copy
        Range(Cells(k, 1), Cells(k + m, 1)).EntireRow.Insert
    Next k
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
End Sub
0
ybiancheng Posts 3 Registration date Monday September 22, 2014 Status Member Last seen February 6, 2015
Sep 28, 2014 at 11:46 PM
Thank you so much for your help. Apologies for the late reply!

I tried the codes, and there are two issues that I hope you can help me with further:

1) Each row was copied 12 time down (including the original data), rather than 10 times. I wonder why that is the case?

2) I realized that for some of my data, the first cell is actually blank (but the remaining ones have data). Those rows need to be copied as well, but are ignoed by the macro. I was wondering if there is any way to have the macro include those rows as well. So for instance:

A1 B1 C1 D1
A2 B2 C2 D2
B3 C3 D3

The macro would ingore the 3rd row. Can the codes be amended to include the 3 row as well?

Thanks a lot!
0
Answer 2 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 29, 2014 at 01:19 AM
adjust m value to get what you want

as entire row is copied I expect it does not matter the cells in the first column of row is blank.
0

Similar discussions

Deleted Message icon
thina -
BunoCS -

1 response
You have guessed the code too many times. Contact support
nanosama95 -
Komu -

4 responses
Facebook says I have been trying too often
Sameer -
owilson -

2 responses
Comparing Two Excel Sheets and copying like data to third sheet
coz2992 -
TrowaD -

11 responses
Copy row and insert n times
Islandman -
sandy -

46 responses