Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Copy/Paste Macro With Varying Ranges

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a question that is similar to:

    http://www.mrexcel.com/tip013.shtml

    I have been trying for several years to come up with a macro to copy/paste but have been unsuccessful. I remember from the Lotus days that Lotus macros would remember keystrokes but I cannot figure out how to do this in Excel.

    I transfer a lot of data from various software packages into excel. The problem I have is then trying to get the data so it is all on one row. The data comes in with subtotals and totals of the subtotals.

    For example, this appears all in one column:

    AL089 MADISON
    80048 BASIC METABOLIC PANEL
    85025 AUTOMATED HEMOGRAM
    94760 MEASURE BLOOD OXYGEN LEVEL
    99283 EMERGENCY DEPT VISIT
    358061238 HUNTSVILLE,AL
    71020 CHEST X-RAY
    76870 US EXAM, SCROTUM
    358100096 HUNTSVILLE,AL
    TOTAL AL089 MADISON

    This particular report begins with the county, lists procedures performed in the zip code, subtotals zip code, totals for county. I am able to break out into columns the county, procedure, zip code. From there I need to copy/paste (fill) the county to each row and then the zip code.

    The final result I am trying to get to is this:

    80048 AL089 35806-1238
    85025 AL089 35806-1238
    94760 AL089 35806-1238
    99283 AL089 35806-1238

    71020 AL089 35810-0096
    76870 AL089 35810-0096

    The subset data varies in ranges (could be 4 rows, the next 8, the next 5, . . .). I cannot get excel to perform the copy/paste function by keystrokes, it always locks in the range regardless of whether the absolute values is pressed in or not. How can I get the macro to perform these keystrokes:

    at cell xxx: copy
    down arrow
    shift, control, down arrow
    shift, up arrow
    paste
    control, down arrow (so the macro can then be repeated)
    (put data in a column in rows 4,8,9,18,23,42)


    I also would like to know how to do it with the up arrow too.

    at cell xxx: copy
    up arrow
    shift, control, up arrow
    shift, down arrow
    paste
    control, down arrow
    control, down arrow (so the macro can then be repeated)
    (for example put data in a column rows 6,15,20,39,47,52)

    Your help would be greatly appreciated

    [ This Message was edited by: donh on 2002-05-08 14:06 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Record your actions with a macro recorder and view the code, it's a great way to learn.

    Then come back if you have any problems.

    RET79

  3. #3
    Board Regular
    Join Date
    May 2002
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried this, I had visual basic open the same time I was making the keystrokes and nothing recorded until a range was selected.

    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1:A5").Select
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1:A11").Select
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    Application.CutCopyMode = False

    As can be seen from above it is locking in the range and the next time the macro is run it will pick the same range regardless of the keystrokes.

    This answer does not work

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm having a little trouble following this, probably because you have [row] spaces in your worksheet that I can't see, but here's how I do variable code along the lines you're speaking of:

    Code:
    'at cell xxx: copy
    'down arrow
    'shift, control, down arrow
    'shift, up arrow
    'Paste
    'control, down arrow (so the macro can then be repeated)
    '(put data in a column in rows 4,8,9,18,23,42)
    
    Sub test()
    Dim n As Range, p As Long, o As Integer
    Set n = ActiveCell
    p = ActiveCell.End(xlDown).Row 'last row #
    o = n.Column 'active column number
    'copy activecell below
    n.Copy _
    Range(ActiveCell.Offset(1).Address & ":" & Cells(p + 1, o).Address) 'paste
    Set n = Cells(p + 1, o) 'reset (to repeat)
    p = ActiveCell.End(xlDown).Row
    o = n.Column
    n.Copy _
    Range(ActiveCell.Offset(1).Address & ":" & Cells(p + 1, o).Address)
    'etc......
    End Sub

    Perhaps the methodology may be of some help.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-08 15:35 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com