ElPietro
09-30-2002, 08:17 AM
Ok, I am sick today and not sure if I can't think straight of if I just don't know the answer. I've got a macro that used to work but now is messed up. What I want it to do is select and copy all active cells in a spread sheet. The way I have always done this is go to the last active cell and then select all cells between the last active and range A1. The problem I have is I want it to work for various spreadsheets, but when the macro is recorded it inputs the actual numbered range, instead of the commands I use.
For example:
ActiveCell.SpecialCells(xlLastCell).Select
Range("A1:F975").Select
Range("F975").Activate
Selection.Copy
The lines I've bolded are the problem. I don't want range F975 to be used, because if I run this macro in another spreadsheet then 975 will not be a valid row number as the new spreadsheet may be longer or shorter. So I need to VBA command that mimics the control+shift+home command, or some other method that will just select and copy all populated cells in a spreadsheet.
And no, I don't want to just select all 66k rows, this will be generating about 20 new worksheets and if it selected and copied that many rows each time my PC would just explode.
So, any VBA or Excel Macro gurus out there that can gimme a hand with this? My brain hurts today when I stare at the screen too long, otherwise I'd just search the net. Thanks.
For example:
ActiveCell.SpecialCells(xlLastCell).Select
Range("A1:F975").Select
Range("F975").Activate
Selection.Copy
The lines I've bolded are the problem. I don't want range F975 to be used, because if I run this macro in another spreadsheet then 975 will not be a valid row number as the new spreadsheet may be longer or shorter. So I need to VBA command that mimics the control+shift+home command, or some other method that will just select and copy all populated cells in a spreadsheet.
And no, I don't want to just select all 66k rows, this will be generating about 20 new worksheets and if it selected and copied that many rows each time my PC would just explode.
So, any VBA or Excel Macro gurus out there that can gimme a hand with this? My brain hurts today when I stare at the screen too long, otherwise I'd just search the net. Thanks.