append rows with new data

[Roland53]

I'm not sure where to begin on this one...Office Pro 03'


I am taking a list of data and blending it with an Excel database. For each row in the list of new data I need to take that information and add it in the proper row and in the proper columns in the database. The each row in the database is keyed off of a "Tag #"

 

[Roland53]

more

if the daily input can't match up with a tag# I need to gererate an exception report

 

[erik.van.geit]

sorry,

But this is a great mystery.
There is no correlation between the "audit info in the last columns" and the daily input. After some difficult copy and pasting and doing a search on the worksheet for all your audit info no match was found.

I tried to explain that you need simple examples to start with.
Like the example in my previous post...

kind regards,
ERik

 

[Roland53]

Thanks...here is another

Book1
	A	B	C	D	E
1	Sheet 1	Before
2
3	Tag #	LegFt	Auditor	Audit date
4	1	5
5	2	5
6	3	5
7	4	5
8
9
10	Sheet 2	New Data
11
12	Tag #	Auditor	Audit Date
13	1	JJ	1/5/2005
14	2	JJ	1/5/2005
15	8	JJ	1/5/2005
16
17	Sheet 1	After
18
19	Tag #	LegFt	Auditor	Audit date
20	1	5	JJ	1/5/2005
21	2	5	JJ	1/5/2005
22	3	5
23	4	5
24
25	Sheet 3	Exceptions
26
27	Tag #	Auditor
28	8	JJ
Sheet1

 

[erik.van.geit]

with sheets setup as this
run the code below please

are we coming closer?
kind regards,
Erik

Map2
	A	B	C	D
1	Sheet 1	Before
2
3	Tag #	LegFt	Auditor	Audit date
4	1	5
5	2	5
6	3	5
7	4	5
Database

Map2
	A	B	C	D
1	Sheet 2	New Data
2
3	Tag #	Auditor	Audit Date
4	1	JJ	1-5-05
5	2	JJ	1-5-05
6	8	JJ	1-5-05
New data

Map2
	A	B	C	D
1	Tag #	Auditor
Exceptions

'you'll have to find the Tag# of sheet 2 in sheet 1
'this will be done one by one with a For Each loop

Option Explicit

Sub copy_data()
Dim cell      As Range
Dim tags      As Range
Dim newtags   As Range
Dim sourcerow As Long
Dim targetrow As Long
Dim c As Range

Set tags = Range(Sheets(1).Cells(4, 1), Sheets(1).Cells(65536, 1).End(xlUp))
Set newtags = Range(Sheets(2).Cells(4, 1), Sheets(2).Cells(65536, 1).End(xlUp))


For Each cell In newtags
sourcerow = cell.Row
      Set c = tags.Find(cell, LookIn:=xlValues)
      If Not c Is Nothing Then
      targetrow = c.Row
      Sheets(2).Range(Cells(sourcerow, 2), Cells(sourcerow, 3)).Copy _
      Sheets(1).Cells(targetrow, 3)
      Else
      Sheets(2).Range(Cells(sourcerow, 1), Cells(sourcerow, 2)).Copy _
      Sheets(3).Cells(65536, 1).End(xlUp).Offset(1, 0)
      End If
Next cell
End Sub

 

[Roland53]

Thanks...I'll apply it to my main sheet

thanks