April 22, 2008 at 1:29 am
I have a user who wishes to copy a range of data from one spreadsheet and paste it into another. However the range of copied data varies from month to month and the position in the spreadsheet to which it is pasted also varies.
He was thinking about using a key word within the spreadsheets. Is there a better way?
:Whistling:
Madame Artois
April 22, 2008 at 2:08 pm
Methods of finding pre-defined ranges from Excel macros:
1) Fixed column/row offsets
2) "marker" or "flag" values
3) Named ranges
4) Dedicated sheet
(anything on the sheet is part of the range)
5) User selection
(user selects the input range first, then activates the macro)
6) Floating Links
(source cells contain links to the beginning and ends of the range(s). So long the ends are not overwritten or deleted, they should "float" with any inserts or deletes with the range).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 23, 2008 at 5:34 am
In VBA, the CurrentRegion property of a range can be used to find the limits of an area containing data, as defined by blank rows/columns surrounding the data area.
So if the user firsts selects any cell of data, the VBA code can simply:
Selection.CurrentRegion.copy
to place the entire data area on the clipboard.
(Also, the subsequent paste needs to know only the upper left hand corner of the destination area.)
April 23, 2008 at 5:58 am
Thanks for that. I also came up with the Selection.CurrentRegion.Select so I sent that to the user.
I've had no further feedback to date so I hope it solved his problem.
:Whistling:
Madame Artois
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply