+ Reply to Thread
Results 1 to 10 of 10

Macro, Copy Selected Cells Down a Column

  1. #1
    DB33
    Guest

    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. #2
    DB33
    Guest

    Re: 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. #3
    Dave Peterson
    Guest

    Re: 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. #4
    DB33
    Guest

    Re: 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. #5
    Dave Peterson
    Guest

    Re: 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. #6
    DB33
    Guest

    Re: 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. #7
    DB33
    Guest

    Re: 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. #8
    Dave Peterson
    Guest

    Re: 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. #9
    DB33
    Guest

    Re: 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. #10
    Dave Peterson
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1