VBA: moving every nth column and pasting the results to a

Giorgio · Dec 2nd 2003

I'm trying to automate 650 tables that are 8R x 9C into a single array for each table.

What do I have to do to create a macro that starts on a particular line then moves every 13 row?

any help would be greatfully appreciated.

I would post a sample bit of the spreadsheet if I could find the attach button

Anonymous · Dec 2nd 2003

Unfortunately, the attachment feature is currently not working. I'm confused about what you want to do. Can you provide more detail? Do you want to make the 8Rx9C array into a 1x72 array or a 72x1 array? Where does 13 come into play?

Giorgio · Dec 3rd 2003

the table is 8r x 9c and repeats 650 times down the page at intervals of 13 rows. I've got a load of spreadsheets to go through and it takes about 10-20 minutes to got through each sheet manually. I'm certain a macro could reduce the boring and repeative bits of this operation.

Mainly creating the array, copying it down the page, copying the single resulting array to a new sheet.

The array I'm trying to create is 1 X 72. I would like to copy this new array onto a new sheet with 1 row for each of the 650 tables. I have a small section as a sample 47kb if this helps explain my predicament more clearly.

thanks for taking the time to respond.

Anonymous · Dec 3rd 2003

Try the following macro. It assumes the tables are on the active sheet. Please post back if I misunderstood the tables layout.

Code

Sub DoTables()
    Dim i As Long, j As Long, k As Long, r As Range, s As Worksheet
    Application.ScreenUpdating = False
    Set s = Sheets("sheet2") 'sheet to copy tables to
    s.UsedRange.Clear 'start with clean sheet
    k = 0
    i = 1 'first row of table to convert
    Set r = Range(Cells(i, 1), Cells(i + 7, 9)) 'table to convert
    While Application.CountA(r) &gt; 0
        k = k + 1
        For j = 1 To 8
            r.Rows(j).Copy s.Cells(k, 9 * (j - 1) + 1)
        Next j
        i = i + 13
        Set r = Range(Cells(i, 1), Cells(i + 7, 9))
    Wend
End Sub

Display More

Giorgio · Dec 3rd 2003

thanks for this it sort of answers my question.
as ever this is like a piece of string.

the first table of cells I want are
A502
B503:i503 ... B511:i511

A515
B516:i516 ... B524:i524

etc.

sorry to be such a pain

Anonymous · Dec 3rd 2003

Ok, try this version.

Code

Sub DoTables()
    Dim i As Long, j As Long, k As Long, r As Range, s As Worksheet
    Application.ScreenUpdating = False
    Set s = Sheets("sheet2") 'sheet to copy tables to
    s.UsedRange.Clear 'start with clean sheet
    k = 0
    i = 502 'first row of table to convert
    Set r = Cells(i, 1) 'table label
    While Not IsEmpty(r)
        k = k + 1
        r.Copy s.Cells(k, 1)
        Set r = Range(Cells(i + 1, 2), Cells(i + 8, 9)) 'table to convert
        For j = 1 To 9
            r.Rows(j).Copy s.Cells(k, 8 * (j - 1) + 2)
        Next j
        i = i + 13
        Set r = Cells(i, 1) 'table label
    Wend
End Sub

Display More

Giorgio · Dec 3rd 2003

your a complete star,

thanks a million this will save me literaly hours.

VBA: moving every nth column and pasting the results to a

Participate now!

Share