Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF THEN stuff to search-paste-copy rows Help???
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy & Paste Formula | Excel Discussion (Misc queries) | |||
Search, copy and paste help | Excel Discussion (Misc queries) | |||
Search-Copy and Paste-code | Excel Programming | |||
search/copy/paste macro | Excel Programming | |||
Search/copy/paste Macro | Excel Programming |