Copy/Paste Macro With Varying Ranges

[donh]

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 ]

[RET79]

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

[donh]

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

[NateO]

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:

'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 ]