Macro: copying, inserting & autofilling.

[Steve Simons]

'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 are:

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

 

[Don Guillett]

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.

 

[Steve Simons]

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