Need a macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pcor - 05 Sep 2006 01:00 GMT

Ihave data in col A & B
Theamount of data can be only 10 lines or could be 100 lines
lets assume I have 99 entries in col A & B
I would like the macro to copy lines 33 to 66 to start at d1 & d1
then copy the remiander (ie line 67 to 69) to start at f1 & g1
Thanks for the help

Reply to this Message

David McRitchie - 05 Sep 2006 09:51 GMT

The description of your problem leaves a lot to be desired,
but it is understandable if you know the answer, you want to
snake the columns, see
http://www.mvps.org/dmcritchie/excel/snakecol.htm#snakecols
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> Ihave data in col A & B
> Theamount of data can be only 10 lines or could be 100 lines
> lets assume I have 99 entries in col A & B
> I would like the macro to copy lines 33 to 66 to start at d1 & d1
> then copy the remiander (ie line 67 to 69) to start at f1 & g1

Reply to this Message

pcor - 06 Sep 2006 02:01 GMT

Sorry about that..I will try and explain better
Lets assume I have 100 lines of data starting at line 1 in col A & B
I would like to have a macro that would leave the first third of the data(ie
lines 1 to 33) in its original place.
The next move would grab the second third(ie lines 34 to 66) and copy that
date to col d & E startubg at line 1
The remainder of the date located at A & B 67 to 100 would be COPIED to line
1 of column g & H
Hope that is more clear
Thanks
Ian M

> The description of your problem leaves a lot to be desired,
> but it is understandable if you know the answer, you want to
[quoted text clipped - 11 lines]
> > I would like the macro to copy lines 33 to 66 to start at d1 & d1
> > then copy the remiander (ie line 67 to 69) to start at f1 & g1

Reply to this Message

Gord Dibben - 06 Sep 2006 02:23 GMT

pcor

Something like this?

Sub Set_Three_Times()
Dim iSource As Long
Dim iTarget As Long
Dim cCols As Long
Dim rrows As Long

iSource = 1
iTarget = 1
cCols = InputBox("Original Number of Columns")
rrows = InputBox("rows per set")
Do
Cells(iSource, "A").Resize(rrows, cCols) _
.Cut Destination:=Cells(iTarget, "A")
Cells(iSource + rrows, "A").Resize(rrows, cCols) _
.Cut Destination:=Cells(iTarget, (cCols + 1))
Cells(iSource + (2 * rrows), "A").Resize(rrows, cCols) _
.Cut Destination:=Cells(iTarget, (2 * cCols) + 1)
iSource = iSource + (rrows * (cCols + 1))
iTarget = iTarget + (rrows + 1)
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub

Gord Dibben MS Excel MVP

>Sorry about that..I will try and explain better
>Lets assume I have 100 lines of data starting at line 1 in col A & B
[quoted text clipped - 23 lines]
>> > I would like the macro to copy lines 33 to 66 to start at d1 & d1
>> > then copy the remiander (ie line 67 to 69) to start at f1 & g1

Reply to this Message

pcor - 06 Sep 2006 16:50 GMT

Thanks...That is EXACTLY what I need with three small exceptions.
I want to copy COL A and Col B(not just Col A) and I want to place the
first cut in col D & E(leaving a col C blank). and the same for the next cut.
That would go into col g & H leaving Col F blank.
And just one more thing...
I would very much line to COPY the data and not cut it. (IE Leave the
original data where it was
Many thanks In advance
Ianm

> pcor
>
[quoted text clipped - 52 lines]
> >> > I would like the macro to copy lines 33 to 66 to start at d1 & d1
> >> > then copy the remiander (ie line 67 to 69) to start at f1 & g1

Reply to this Message

David McRitchie - 06 Sep 2006 18:00 GMT

Change the line of code
cCols = InputBox("Original Number of Columns")

to
cCols = 2
but I would really recommend the following if column 3 is empty
cCols = 3

--
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> Thanks...That is EXACTLY what I need with three small exceptions.
> I want to copy COL A and Col B(not just Col A) and I want to place the
[quoted text clipped - 62 lines]
> > >> > I would like the macro to copy lines 33 to 66 to start at d1 & d1
> > >> > then copy the remiander (ie line 67 to 69) to start at f1 & g1

Reply to this Message

pcor - 06 Sep 2006 19:51 GMT

Many thanks for getting me on the right track

> Change the line of code
> cCols = InputBox("Original Number of Columns")
[quoted text clipped - 76 lines]
> > > >> > I would like the macro to copy lines 33 to 66 to start at d1 & d1
> > > >> > then copy the remiander (ie line 67 to 69) to start at f1 & g1

Reply to this Message

Gord Dibben - 06 Sep 2006 22:12 GMT

Thanks for jump-in David.

Seems pcor is now happy so operation complete.

Gord

>Change the line of code
> cCols = InputBox("Original Number of Columns")
[quoted text clipped - 3 lines]
>but I would really recommend the following if column 3 is empty
> cCols = 3

Reply to this Message