Macro, Copy Selected Cells Down a Column

[DB33]

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.:

 

[DB33]

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.

 

[Dave Peterson]

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]

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.

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]

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

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!!

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.

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.:

 

[DB33]

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

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

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]

 

[DB33]

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]

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]

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

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]

Glad you got it working.

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

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.