EXCEL MACRO. Please help. 1

[jyvu]

How do I write a macro for the following task:

I have an Excel spreadsheet:

a a a
a a a
a a a

b b b
b b b
b b b

c c c
c c c
c c c

d d d
d d d
d d d

and so on....

I'd like to CUT pages that contain b,c,d,etc. and PASTE them next to "page a" as:

a a a b b b c c c d d d
a a a b b b c c c d d d
a a a b b b c c c d d d

If anyone knows how to write this macro, please help me out.
My email address is vu@sgtx.com

Thanks,
Jeffrey

 

[SkipVought]

Hi,

Are you saying that you want to take the data from sheets 2 and following and for each row of data, append (add) it to the corresponding row on sheet 1? That's not very difficult at all.

Let me know. Skip,
SkipAndMary1017@mindspring.com

 

[jyvu]

Thanks for replying, Skip.

I just want to move data within one sheet. Assuming I'm working on sheet1:

Before running a macro:

a a a
a a a
a a a

b b b
b b b
b b b

c c c
c c c
c c c

d d d
d d d
d d d

and so on....


After running the macro, sheet1 will become:

a a a b b b c c c d d d
a a a b b b c c c d d d
a a a b b b c c c d d d

How do I write a loop through a, b, c, d, etc... blocks to cut and paste?
Many thanks,
Jeffrey

 

[SkipVought]

How are these blocks of data defined. One empty row between blocks? Skip,
SkipAndMary1017@mindspring.com

 

[jyvu]

Yes, Skip. One empty row between blocks - in this case (maybe 2 empty rows between blocks or none empty rows.)

Thanks,

 

[SkipVought]

try this...

Sub MoveBlocks()
    Dim rngBase As Range, rngMove As Range
    Dim aRow, aCol, bRow, PrevRow
    
    With ActiveSheet.UsedRange
        Set rngBase = Cells(.Row, .Column).CurrentRegion
        aRow = rngBase.Row
    End With
    
    Do While (rngBase.Rows.Count < ActiveSheet.UsedRange.Rows.Count)
        Set rngMove = Cells(rngBase.Rows.Count, 1).End(xlDown).CurrentRegion
        With ActiveSheet.UsedRange
            Set rngBase = Cells(.Row, .Column).CurrentRegion
            aCol = rngBase.Columns.Count
        End With
        bRow = rngMove.Row
        For Each r In rngMove
            Cells(aRow + (r.Row - bRow), aCol + r.Column).Value = r.Value
            PrevRow = r.Row
        Next
        rngMove.Delete
    Loop
End Sub

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[jyvu]

Skip,

It worked well. However, when I inserted a blanked column before column A, there was an infinite loop.

Another thing, how do I change the code if the format of blocks have changed (such as just only one empty row between blocks) ?

Many thanks,
Jeffrey