Need a macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
Back
Top