Macro, Copy Selected Cells Down a Column
 

Hello,
I'm not even sure if what I want to do is possible but here's the scenario:

I want to be able to select (highlight) multiple cells in a column, then
press a command button, and those selected cells will repeat down all the way
to the bottom of the column, no matter how many selected cells have been
selected. So if I select 2 cells containing 1-2, I want the entire column to
be 1-2-1-2-1-2-1-2,etc.. and If I select 3 cells such as 1-2-3, The entire
column should be 1-2-3-1-2-3-1-2-3.

How Can I do that? I've tried everything.

Thank you.:

Macro, Copy Selected Cells Down a Column
 

I should also mention that I have 4 columns, and that I need the command
button to work for each of them, depending on which one I select the cells in.

Your help would be much apprciated.

Macro, Copy Selected Cells Down a Column
 

If you select the range to copy and then rightclick on that autofill button
(bottom right corner of selection) and drag it down as far as you need, then
you'll see an option to Copy Cells.

When you do it manually, you know when to stop.

The code has to know when to stop. I used the column to the left--unless you're
in column A. Then I used column B.

Option Explicit
Sub Testme01()

Dim Rng As Range
Dim LastCell As Range
Dim LastRow As Long
Dim OffsetCol As Long

With ActiveSheet
Set Rng = Selection.Columns(1)
If Rng.Column 1 Then
OffsetCol = Rng.Column - 1
Else
OffsetCol = Rng.Column + 1
End If
LastRow = .Cells(.Rows.Count, OffsetCol).End(xlUp).Row
Set LastCell = .Cells(LastRow, Rng.Column)

Rng.AutoFill _
Destination:=.Range(Rng, LastCell), Type:=xlFillCopy
End With
End Sub

DB33 wrote:

Hello,
I'm not even sure if what I want to do is possible but here's the scenario:

I want to be able to select (highlight) multiple cells in a column, then
press a command button, and those selected cells will repeat down all the way
to the bottom of the column, no matter how many selected cells have been
selected. So if I select 2 cells containing 1-2, I want the entire column to
be 1-2-1-2-1-2-1-2,etc.. and If I select 3 cells such as 1-2-3, The entire
column should be 1-2-3-1-2-3-1-2-3.

How Can I do that? I've tried everything.

Thank you.:

--

Dave Peterson

Macro, Copy Selected Cells Down a Column
 

WOW DAVE!!
you are a genius!!

the only thing I'm wondering is why does it only work for my firs column? for
instance, I have 4 columns, C,D,E,F and your code only works when I use it in
C. Else I get this error:

"Autofill method of Range Class Failed" and it points to the last line of the
code: Rng.AutoFill Destination:=.Range(Rng, LastCell), Type:=xlFillCopy

Thank you so much again.

Dave Peterson wrote:
If you select the range to copy and then rightclick on that autofill button
(bottom right corner of selection) and drag it down as far as you need, then
you'll see an option to Copy Cells.

When you do it manually, you know when to stop.

The code has to know when to stop. I used the column to the left--unless you're
in column A. Then I used column B.

Option Explicit
Sub Testme01()

Dim Rng As Range
Dim LastCell As Range
Dim LastRow As Long
Dim OffsetCol As Long

With ActiveSheet
Set Rng = Selection.Columns(1)
If Rng.Column 1 Then
OffsetCol = Rng.Column - 1
Else
OffsetCol = Rng.Column + 1
End If
LastRow = .Cells(.Rows.Count, OffsetCol).End(xlUp).Row
Set LastCell = .Cells(LastRow, Rng.Column)

Rng.AutoFill _
Destination:=.Range(Rng, LastCell), Type:=xlFillCopy
End With
End Sub

Hello,
I'm not even sure if what I want to do is possible but here's the scenario:
[quoted text clipped - 9 lines]

Thank you.:

Macro, Copy Selected Cells Down a Column
 

It could be that there isn't anything surrounding that column???

If Rng.Column 1 Then
OffsetCol = Rng.Column - 1
Else
OffsetCol = Rng.Column + 1
End If

I guessed at how the lastrow should be determined. Do you have a fool-proof way
to do it?

DB33 wrote:

WOW DAVE!!
you are a genius!!

the only thing I'm wondering is why does it only work for my firs column? for
instance, I have 4 columns, C,D,E,F and your code only works when I use it in
C. Else I get this error:

"Autofill method of Range Class Failed" and it points to the last line of the
code: Rng.AutoFill Destination:=.Range(Rng, LastCell), Type:=xlFillCopy

Thank you so much again.

Dave Peterson wrote:
If you select the range to copy and then rightclick on that autofill button
(bottom right corner of selection) and drag it down as far as you need, then
you'll see an option to Copy Cells.

When you do it manually, you know when to stop.

The code has to know when to stop. I used the column to the left--unless you're
in column A. Then I used column B.

Option Explicit
Sub Testme01()

Dim Rng As Range
Dim LastCell As Range
Dim LastRow As Long
Dim OffsetCol As Long

With ActiveSheet
Set Rng = Selection.Columns(1)
If Rng.Column 1 Then
OffsetCol = Rng.Column - 1
Else
OffsetCol = Rng.Column + 1
End If
LastRow = .Cells(.Rows.Count, OffsetCol).End(xlUp).Row
Set LastCell = .Cells(LastRow, Rng.Column)

Rng.AutoFill _
Destination:=.Range(Rng, LastCell), Type:=xlFillCopy
End With
End Sub

Hello,
I'm not even sure if what I want to do is possible but here's the scenario:
[quoted text clipped - 9 lines]

Thank you.:


--

Dave Peterson

Macro, Copy Selected Cells Down a Column
 

Well my 4 columns are actually C-F-G-J since I hid columns D-E-H-I for
appearance purposes.
I'm not sure if it changes anything to the code but now I seem to get that
msg quite a few times when I try it in columns F-G-J, maybe you know what it
means:

"This operation requires the merged cells to be identically sized"

It's a little odd, since I don't get this message when I use it for the first
column..

thanks for your time

Dave Peterson wrote:
It could be that there isn't anything surrounding that column???

If Rng.Column 1 Then
OffsetCol = Rng.Column - 1
Else
OffsetCol = Rng.Column + 1
End If

I guessed at how the lastrow should be determined. Do you have a fool-proof way
to do it?

WOW DAVE!!
you are a genius!!
[quoted text clipped - 45 lines]

Thank you.:

Macro, Copy Selected Cells Down a Column
 

Ok, here's another thing Ijust noticed, I decided to unhide my Columns D & E,
and when I fill out C, then I can do D, E, F no problem.. as long as the
column preceding is filled.

Is there any way we can change the code so that I can do it in a random order,
say for example, fill out G, and then C, since there are really no link
between the columns anyways.. they can all have different values.

Macro, Copy Selected Cells Down a Column
 

If you can tell the macro how to figure out what the last row should be, then
yep.

Can you pick out a column that always has data in it when the row is used?

For instance, if you said A always had data in it:

Option Explicit
Sub Testme01()

Dim Rng As Range
Dim LastCell As Range
Dim LastRow As Long

With ActiveSheet
Set Rng = Selection.Columns(1)
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set LastCell = .Cells(LastRow, Rng.Column)

Rng.AutoFill _
Destination:=.Range(Rng, LastCell), Type:=xlFillCopy
End With
End Sub

But I don't know enough about your file to really guess.

DB33 wrote:

Ok, here's another thing Ijust noticed, I decided to unhide my Columns D & E,
and when I fill out C, then I can do D, E, F no problem.. as long as the
column preceding is filled.

Is there any way we can change the code so that I can do it in a random order,
say for example, fill out G, and then C, since there are really no link
between the columns anyways.. they can all have different values.

--

Dave Peterson

Macro, Copy Selected Cells Down a Column
 

Dave,
I can't thank you enough, My column B is always full, and I used your code
and it works perfectly.
You saved me hours of work.

Greatly appreciated. Have a good week :P

Dave Peterson wrote:
If you can tell the macro how to figure out what the last row should be, then
yep.

Can you pick out a column that always has data in it when the row is used?

For instance, if you said A always had data in it:

Option Explicit
Sub Testme01()

Dim Rng As Range
Dim LastCell As Range
Dim LastRow As Long

With ActiveSheet
Set Rng = Selection.Columns(1)
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set LastCell = .Cells(LastRow, Rng.Column)

Rng.AutoFill _
Destination:=.Range(Rng, LastCell), Type:=xlFillCopy
End With
End Sub

But I don't know enough about your file to really guess.

Ok, here's another thing Ijust noticed, I decided to unhide my Columns D & E,
and when I fill out C, then I can do D, E, F no problem.. as long as the
[quoted text clipped - 3 lines]
say for example, fill out G, and then C, since there are really no link
between the columns anyways.. they can all have different values.

Macro, Copy Selected Cells Down a Column
 

Glad you got it working.

DB33 wrote:

Dave,
I can't thank you enough, My column B is always full, and I used your code
and it works perfectly.
You saved me hours of work.

Greatly appreciated. Have a good week :P

Dave Peterson wrote:
If you can tell the macro how to figure out what the last row should be, then
yep.

Can you pick out a column that always has data in it when the row is used?

For instance, if you said A always had data in it:

Option Explicit
Sub Testme01()

Dim Rng As Range
Dim LastCell As Range
Dim LastRow As Long

With ActiveSheet
Set Rng = Selection.Columns(1)
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set LastCell = .Cells(LastRow, Rng.Column)

Rng.AutoFill _
Destination:=.Range(Rng, LastCell), Type:=xlFillCopy
End With
End Sub

But I don't know enough about your file to really guess.

Ok, here's another thing Ijust noticed, I decided to unhide my Columns D & E,
and when I fill out C, then I can do D, E, F no problem.. as long as the
[quoted text clipped - 3 lines]
say for example, fill out G, and then C, since there are really no link
between the columns anyways.. they can all have different values.


--

Dave Peterson