IF THEN stuff to search-paste-copy rows Help???

T

Tom

Hi there,

I've just started VBA programming in VBA and have found this NG really
useful. However i have searched and searched and can't find the
solution to this:

I have say 20 files which all contain similar rows of data and one
source file which has data which is updated and changed regularly. I
want to select all rows of data currently in the source file and then
put them into the the 20 or so destination files.

Column A denotes the row of data's 'name'
However:
1.
If a row of data from the source file is matched to one in the
destination file (I.e. Col A number's are equal) then i want that new
data to be pasted into that row - i.e. search in column A and where
there is a NUMBER match then Paste in new data from source file.

But:
2. If the row of data from the source file is NOT in the dest. file
then I want to find the last row of data in the file - then do a 'copy
and insert copied cells' on that row (there are charts which need to
be updated automatically) - and then place the new row of data from
Source file into that final row which was just 'made'.

This then needs to be done over and over again for the 20 or so files.

So far i have only been trying to transfer data within one file (to
keep it simple)
and this is the code i have got:

'Rows i want to be copied have already been selected (highlighted)
rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
For i = 1 To rng
If ActiveCell.Value > 0 Then
ActiveCell.Offset(0, 0).Select
Selection.EntireRow.Copy
Dim Celll
Celll = Cells(ActiveCell.Row, 1)
ActiveCell.Offset(1, 0).Select
Sheets("Meta").Select
Range("A1:A50").Select
rngg = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
For a = 1 To rngg
If ActiveCell.Value = Celll Then
ActiveCell.PasteSpecial
ActiveCell.Offset(1, 0).Select
GoTo 100
ElseIf ActiveCell.Value = "" Then
ActiveCell.Offset(-1, 0).Select
Selection.EntireRow.Copy
Selection.Insert Shift:=x1Down
ActiveCell.Offset(1, 0).Select
Sheets("Sheet2").Select
ActiveCell.Offset(-1, 0).Select
Selection.EntireRow.Copy
Sheets("Meta").Select
ActiveCell.PasteSpecial
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Next a
100:
Sheets("Sheet2").Select

Else
ActiveCell.Offset(1, 0).Select
End If

Next i

End Sub

It's probably a real mish-mash of code but i'm just beginning with all
this VBA excel lark!

Any help would be appreciated. Even if it is just to show me how to do
the IF THEN End If stuff etc.

Cheers

Tom Secretan
 
Top