Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to find, copy, and paste until value change
On an imported worksheet I have a column that is mostly blanks with a company
number when the report changes. I would like a macro to go to the first filled in cell, copy it and paste it into every subsequent blank cell until the next company then copy and paste, etc to the bottom row. Ex: Row-Value 1 2 3 - A 4 5 6 - B Copy "A", paste to rows 4 and 5, copy "B", paste to following blank cells, etc. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to find, copy, and paste until value change
Valerie -
Instead of a macro you could use a formula in a helper column. For instance, if your value is in column A, then in cell B3 put the formula = if (isblank(A3), B2, A3) then copy this down. Once copied, perform a Copy - Paste Special Values to remove the formulas. - John Michl |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to find, copy, and paste until value change
Valerie:
You can do this easily from the GUI without a macro. 1. Place your cursor in Row 3-A. 2. Hold the Shift key down and then use PageDown and/or the Arrow keys, etc., to highlight the complete data range. 3. From the menu, select Edit, Go To, Special, Blanks. 4. With the "funny" range selected, build a formula: a. Press "=" and then the UP arrow. b. Hold the CTRL key down and then press ENTER. Voila! All of the blank cells now have a formula that evaluate to the Company number above. 5. Copy the range and use Paste Special, Values to convert the formulas to values. Now you can produce pivot tables or pivot charts to your heart's content. HTH, =Mac= L. A. McClelland "Valerie" wrote in message ... On an imported worksheet I have a column that is mostly blanks with a company number when the report changes. I would like a macro to go to the first filled in cell, copy it and paste it into every subsequent blank cell until the next company then copy and paste, etc to the bottom row. Ex: Row-Value 1 2 3 - A 4 5 6 - B Copy "A", paste to rows 4 and 5, copy "B", paste to following blank cells, etc. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to find, copy, and paste until value change
Mac -
That is very slick. I learn something new everyday. Thanks for sharing. I've never used the Edit - Go To - Special functionality. - John |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to find, copy, and paste until value change
John:
You are very welcome. I, too, learn something every time I visit this group. =Mac= "John Michl" wrote in message oups.com... Mac - That is very slick. I learn something new everyday. Thanks for sharing. I've never used the Edit - Go To - Special functionality. - John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions | |||
change from copy & paste task pane (yuk) to c&p little box | Excel Discussion (Misc queries) | |||
Copy & Paste macro | Excel Worksheet Functions | |||
Copy Paste Special Macro | Excel Discussion (Misc queries) |