Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.: |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.: |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.: |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need macro to copy a list of named cells between worksheets | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Macro to copy cells | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |