Copying and pasting sets of 20 rows

[Brain DR]

Hello,

Kind of new to macros.

My problem: I have a long list of data (say 14000 rows with data from A:E). There are 20 sites reporting data for 700 measures. (For whatever reason the data is sent to me in list format). I am not sure of the best way to proceed but I am thinking that a VB macro could quickly solve my issue

I am in need of a macro that will transpose the data so that all data for each site is on one row. Ideally, this macro would:

1) Prompt me to enter the number of sites (N) (this number varies from report to report)
2) Copy every N number of rows and paste in the next available column. (For example, copy rows A21:E40 and paste in F1:J20 then copy rows A41:E60 and past in K1:P20..and so on an so forth).

You help with this is much appreciated. Thanks

David

 

[cb12]

This is rather messy, apologies. However, it should calculate exactly how you described in your question, just remember to change the starting range to where your first line of data you want copied is.

Sub copy_and_paste_rows()
    Dim N, i, j, k, myrows As Integer
        Range("A1").Select
    i = Selection.CurrentRegion.Rows.Count
    N = CInt(InputBox("How many sites are there?"))
    k = 4
                For j = 1 To i / N
                    For myrows = 1 To N
                        ActiveCell.Copy Destination:=ActiveCell.Offset(0, k)
                        ActiveCell.Offset(1, 0).Select
                    Next myrows
                 k = k + 1
                Next j
End Sub

 

[cb12]

Sorry having reread your initial post, something like this might be more appropriate.

Sub copy_and_paste_rows()
    Dim n, i, j, k, l, numrows, numcolumns As Integer
        Range("A1").Select
    i = Selection.CurrentRegion.Rows.Count
    n = CInt(InputBox("How many sites are there?"))
    k = Selection.CurrentRegion.Columns.Count
    l = k
                For j = 1 To i / n
                        numrows = Selection.Rows.Count
                        numcolumns = Selection.Columns.Count
                        Selection.Resize(numrows + n - 1, numcolumns + k - 1).Select
                        Selection.Copy Destination:=ActiveCell.Offset(0, k)
                        ActiveCell.Offset(20, 0).Select
                 k = k + l
                Next j
End Sub

 

[cb12]

Apparently I still can't read. You should probably use this code to get your desired result. This time I have read correctly, I promise.

Sub copy_and_paste_rows()
    Dim n, i, j, k, l, numrows, numcolumns, rownum As Integer
        Range("A1").Select
    i = Selection.CurrentRegion.Rows.Count
    n = CInt(InputBox("How many sites are there?"))
    k = Selection.CurrentRegion.Columns.Count
    l = k
                For j = 1 To i / n
                        rownum = ActiveCell.Row
                        numrows = Selection.Rows.Count
                        numcolumns = Selection.Columns.Count
                        Selection.Resize(numrows + n - 1, numcolumns + k - 1).Select
                        Selection.Copy Destination:=ActiveCell.Offset(-rownum + 1, k)
                        ActiveCell.Offset(n, 0).Select
                        k = k + l
                Next j
End Sub

note: you don't really need the numrows and numcolumns variables but they are there incase you ever want to modify the code. In the interim it would be fine I guess to simply remove the variables and resize the selection with the n and k values

 

[Brain DR]

cb12,

This is GLORIOUS. Thanks for your help (and for relieving me of a major headache).

By the way it was actually quite helpful to see your progression of VB code. Thanks!!

David