Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: copying, inserting & autofilling.
'Scuse the length of this post I wanted to cover everything as
completely as possible to save people having to ask extra info. I have a sheet which intermittently gets external data. This data is then manually inserted into another sheet which has calculations to the right of the data. The manual process takes the form: 1) In column A of the "old" data is a record number, so find that; 2) Look on the new data (the one that was returned from the Get External data feature); find how many records need to be copied (i.e. are NOT in the "old" data) 3) from new data, copy (and then paste into the "old" data) the new records 4) use the fill-handle to fill up the rows (to row 3, where the data ends) the formulas from columns J to AH Here is an abbreviated version of the data:- "OLD" DATA 1115 19/07/2006 Mike Laura Calculations are here (down columns and across to row AH) 1116 17/07/2006 Tony Emma 1117 13/07/2006 Peter Jim 1118 19/07/2006 Tracey Steve 1119 17/07/2006 Karen Lenny 1120 13/07/2006 Andy Susan 1121 19/07/2006 Luke Carol 1122 17/07/2006 Pippa Alice 1123 13/07/2006 Debbie James "NEW" DATA 1111 08/08/2006 Amy Sean 1112 06/08/2006 Zoe Paul 1113 04/08/2006 Eve Matt 1114 02/08/2006 Troy Richard 1115 31/07/2006 Mike Laura 1116 29/07/2006 Tony Emma 1117 27/07/2006 Peter Jim 1118 25/07/2006 Tracey Steve 1119 23/07/2006 Karen Lenny 1120 21/07/2006 Andy Susan 1121 19/07/2006 Luke Carol 1122 17/07/2006 Pippa Alice 1123 13/07/2006 Debbie James I have created the following macro on some dummy data, but it needs some refining (see my thoughts below the code) Sub Macro1() Sheets("New Data").Select Rows("3:6").Select Selection.Copy Sheets("Old Data").Select Rows("3:3").Select Selection.Insert Shift:=xlDown Range("E3").Select ' This is the first cell to the right of the data that has a calculation in it. Selection.End(xlDown).Select Range(Selection, Selection.End(xlToRight)).Select Application.CutCopyMode = False Selection.AutoFill Destination:=Range("E3:AH7"), Type:=xlFillDefault Range("A1").Select End Sub The parts I require help with a 1) Finding how many rows to copy from the New data; 2) On the "Old Data" once the new dta is inserted, finding the cell with the first calculation to the right and filling it up to row 3. Any help/suggestions much appreciated Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro: copying, inserting & autofilling.
Hahaha lol;
thanks for the help Don. I'm lol cos of your "It probably would have been easier to give you the code but you should learn." It certainly would have been easier, but you're right, I SHOULD learn! Thanks again. I'll post any subsequent questions here Steve On Tue, 19 Sep 2006 07:49:18 -0500, "Don Guillett" wrote: to determine the last row use lr=cells(rows.count,"a").end(xlup).row or lr=cells(2,1).end(xldown).row use the same idea with rows instead to find the next available column. remove all selections as they are not necessary or desirable. example Rows("3:" & lr).copy sheets("dest").Range("A10").Insert Shift:=xlDown now play with it using your variables to do it all without selections It probably would have been easier to give you the code but you should learn. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inserting special character in a cell as a macro | New Users to Excel | |||
how do I email amacro? | Excel Worksheet Functions | |||
Macro Help In Excel | Excel Discussion (Misc queries) | |||
copying text into a macro | Excel Worksheet Functions | |||
Inserting Blank Rows Macro? | Excel Worksheet Functions |