Append Unique Records to Master Worksheet
 

I have a Master Worksheet with historical data that I do all of my reporting
and charting from. It is rows of various data by create date with a unique
record identifier (Ticket Number). Each weekday, I get a new list of records
in and Update Worksheet with the past 7 days of records using MSQuery through
ODBC to external data. Some of the records repeat and others of them are new.
I would like to append only the NEW unique records (based on Ticket Number)
from the Update Worksheet into the Master Worksheet.

This what I tried and got stuck on --
The Master data is sorted so I can do a vlookup on the new record set. If
the record is new, I set the flag to 0, else 1. I created a macro to
autofilter for 0. In my macro, I selected Cell A1. I moved the cursor down 1
to eliminate the header row and then selected all the filtered data. This
would work fine if the first filtered row was always row 2 but as it turns
out, the macro recorded Row 54 as the first filtered row under the header
row. Unfortunately, this will not always be the case.

Can you please help me automate this task?

Append Unique Records to Master Worksheet
 

Post the recorded macro and we wil make changes as required. It is easier to
get it right by modifying the recorded code rather than send new code.

"Forest" wrote:

I have a Master Worksheet with historical data that I do all of my reporting
and charting from. It is rows of various data by create date with a unique
record identifier (Ticket Number). Each weekday, I get a new list of records
in and Update Worksheet with the past 7 days of records using MSQuery through
ODBC to external data. Some of the records repeat and others of them are new.
I would like to append only the NEW unique records (based on Ticket Number)
from the Update Worksheet into the Master Worksheet.

This what I tried and got stuck on --
The Master data is sorted so I can do a vlookup on the new record set. If
the record is new, I set the flag to 0, else 1. I created a macro to
autofilter for 0. In my macro, I selected Cell A1. I moved the cursor down 1
to eliminate the header row and then selected all the filtered data. This
would work fine if the first filtered row was always row 2 but as it turns
out, the macro recorded Row 54 as the first filtered row under the header
row. Unfortunately, this will not always be the case.

Can you please help me automate this task?

Append Unique Records to Master Worksheet
 

Sub UpdateMaster()
'
'

'
Sheets("UpdateData").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range(Selection, Selection.End(xlToRight)).Select
Range("A2").Select
Selection.End(xlToRight).Select
Selection.AutoFilter Field:=23, Criteria1:="0"
Selection.End(xlToLeft).Select
Range("A157").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("MasterData").Select
Range("A2").Select
Selection.End(xlDown).Select
Range("A10936").Select <===This is where I attempted to go to the first
available blank line.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("UpdateData").Select
Range("A2").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Sheets("MasterData").Select
End Sub


"Joel" wrote:

Post the recorded macro and we wil make changes as required. It is easier to
get it right by modifying the recorded code rather than send new code.

"Forest" wrote:

I have a Master Worksheet with historical data that I do all of my reporting
and charting from. It is rows of various data by create date with a unique
record identifier (Ticket Number). Each weekday, I get a new list of records
in and Update Worksheet with the past 7 days of records using MSQuery through
ODBC to external data. Some of the records repeat and others of them are new.
I would like to append only the NEW unique records (based on Ticket Number)
from the Update Worksheet into the Master Worksheet.

This what I tried and got stuck on --
The Master data is sorted so I can do a vlookup on the new record set. If
the record is new, I set the flag to 0, else 1. I created a macro to
autofilter for 0. In my macro, I selected Cell A1. I moved the cursor down 1
to eliminate the header row and then selected all the filtered data. This
would work fine if the first filtered row was always row 2 but as it turns
out, the macro recorded Row 54 as the first filtered row under the header
row. Unfortunately, this will not always be the case.

Can you please help me automate this task?

Append Unique Records to Master Worksheet
 

This should work. I simplified your code. The macro recorder really makes
the code much more complicated than required. I used the specialcells method
to copy the visible rows.

Sub UpdateMaster()
'
'

'
With Sheets("UpdateData")
.Range("A2").QueryTable.Refresh BackgroundQuery:=False

'get range of cells onUpdateData sheets
UpdateLastRow = .Range("A" & Rows.Count). _
End(xlUp).Row
Set UpdateRows = .Rows("2:" & UpdateLastRow)

'filter on column W
.Range("W2").AutoFilter Field:=1, Criteria1:="0"

'find where first blank row is on Master Data sheet
MasterLastRow = Sheets("MasterData").Range("A" & Rows.Count). _
End(xlUp).Row
NewRow = MasterLastRow + 1

'Copy visible cells from UpdateData sheet to Master Sheet
'Replace formulas with values
UpdateRows.SpecialCells(xlCellTypeVisible).Copy
Sheets("MasterData").Range("A" & NewRow).PasteSpecial _
Paste:=xlPasteValues

'Unfilter UpdateData sheet
.ShowAllData
End With
End Sub


"Forest" wrote:

Sub UpdateMaster()
'
'

'
Sheets("UpdateData").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range(Selection, Selection.End(xlToRight)).Select
Range("A2").Select
Selection.End(xlToRight).Select
Selection.AutoFilter Field:=23, Criteria1:="0"
Selection.End(xlToLeft).Select
Range("A157").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("MasterData").Select
Range("A2").Select
Selection.End(xlDown).Select
Range("A10936").Select <===This is where I attempted to go to the first
available blank line.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("UpdateData").Select
Range("A2").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Sheets("MasterData").Select
End Sub


"Joel" wrote:

Post the recorded macro and we wil make changes as required. It is easier to
get it right by modifying the recorded code rather than send new code.

"Forest" wrote:

I have a Master Worksheet with historical data that I do all of my reporting
and charting from. It is rows of various data by create date with a unique
record identifier (Ticket Number). Each weekday, I get a new list of records
in and Update Worksheet with the past 7 days of records using MSQuery through
ODBC to external data. Some of the records repeat and others of them are new.
I would like to append only the NEW unique records (based on Ticket Number)
from the Update Worksheet into the Master Worksheet.

This what I tried and got stuck on --
The Master data is sorted so I can do a vlookup on the new record set. If
the record is new, I set the flag to 0, else 1. I created a macro to
autofilter for 0. In my macro, I selected Cell A1. I moved the cursor down 1
to eliminate the header row and then selected all the filtered data. This
would work fine if the first filtered row was always row 2 but as it turns
out, the macro recorded Row 54 as the first filtered row under the header
row. Unfortunately, this will not always be the case.

Can you please help me automate this task?

Append Unique Records to Master Worksheet
 

I found an error . Make change below. You can't copy a row object onto a
range object. The source and destination must be of the same type.

from
Sheets("MasterData").Range("A" & NewRow).PasteSpecial _
Paste:=xlPasteValues
to
Sheets("MasterData").Rows(NewRow).PasteSpecial _
Paste:=xlPasteValues

"Joel" wrote:

This should work. I simplified your code. The macro recorder really makes
the code much more complicated than required. I used the specialcells method
to copy the visible rows.

Sub UpdateMaster()
'
'

'
With Sheets("UpdateData")
.Range("A2").QueryTable.Refresh BackgroundQuery:=False

'get range of cells onUpdateData sheets
UpdateLastRow = .Range("A" & Rows.Count). _
End(xlUp).Row
Set UpdateRows = .Rows("2:" & UpdateLastRow)

'filter on column W
.Range("W2").AutoFilter Field:=1, Criteria1:="0"

'find where first blank row is on Master Data sheet
MasterLastRow = Sheets("MasterData").Range("A" & Rows.Count). _
End(xlUp).Row
NewRow = MasterLastRow + 1

'Copy visible cells from UpdateData sheet to Master Sheet
'Replace formulas with values
UpdateRows.SpecialCells(xlCellTypeVisible).Copy
Sheets("MasterData").Range("A" & NewRow).PasteSpecial _
Paste:=xlPasteValues

'Unfilter UpdateData sheet
.ShowAllData
End With
End Sub


"Forest" wrote:

Sub UpdateMaster()
'
'

'
Sheets("UpdateData").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range(Selection, Selection.End(xlToRight)).Select
Range("A2").Select
Selection.End(xlToRight).Select
Selection.AutoFilter Field:=23, Criteria1:="0"
Selection.End(xlToLeft).Select
Range("A157").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("MasterData").Select
Range("A2").Select
Selection.End(xlDown).Select
Range("A10936").Select <===This is where I attempted to go to the first
available blank line.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("UpdateData").Select
Range("A2").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Sheets("MasterData").Select
End Sub


"Joel" wrote:

Post the recorded macro and we wil make changes as required. It is easier to
get it right by modifying the recorded code rather than send new code.

"Forest" wrote:

I have a Master Worksheet with historical data that I do all of my reporting
and charting from. It is rows of various data by create date with a unique
record identifier (Ticket Number). Each weekday, I get a new list of records
in and Update Worksheet with the past 7 days of records using MSQuery through
ODBC to external data. Some of the records repeat and others of them are new.
I would like to append only the NEW unique records (based on Ticket Number)
from the Update Worksheet into the Master Worksheet.

This what I tried and got stuck on --
The Master data is sorted so I can do a vlookup on the new record set. If
the record is new, I set the flag to 0, else 1. I created a macro to
autofilter for 0. In my macro, I selected Cell A1. I moved the cursor down 1
to eliminate the header row and then selected all the filtered data. This
would work fine if the first filtered row was always row 2 but as it turns
out, the macro recorded Row 54 as the first filtered row under the header
row. Unfortunately, this will not always be the case.

Can you please help me automate this task?

Append Unique Records to Master Worksheet
 

Thank you so much! I wound up needing to make a few tweaks to fit reality but
this was great help and a very quick turn around of information.

Forest

"Joel" wrote:

This should work. I simplified your code. The macro recorder really makes
the code much more complicated than required. I used the specialcells method
to copy the visible rows.

Sub UpdateMaster()
'
'

'
With Sheets("UpdateData")
.Range("A2").QueryTable.Refresh BackgroundQuery:=False

'get range of cells onUpdateData sheets
UpdateLastRow = .Range("A" & Rows.Count). _
End(xlUp).Row
Set UpdateRows = .Rows("2:" & UpdateLastRow)

'filter on column W
.Range("W2").AutoFilter Field:=1, Criteria1:="0"

'find where first blank row is on Master Data sheet
MasterLastRow = Sheets("MasterData").Range("A" & Rows.Count). _
End(xlUp).Row
NewRow = MasterLastRow + 1

'Copy visible cells from UpdateData sheet to Master Sheet
'Replace formulas with values
UpdateRows.SpecialCells(xlCellTypeVisible).Copy
Sheets("MasterData").Range("A" & NewRow).PasteSpecial _
Paste:=xlPasteValues

'Unfilter UpdateData sheet
.ShowAllData
End With
End Sub


"Forest" wrote:

Sub UpdateMaster()
'
'

'
Sheets("UpdateData").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range(Selection, Selection.End(xlToRight)).Select
Range("A2").Select
Selection.End(xlToRight).Select
Selection.AutoFilter Field:=23, Criteria1:="0"
Selection.End(xlToLeft).Select
Range("A157").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("MasterData").Select
Range("A2").Select
Selection.End(xlDown).Select
Range("A10936").Select <===This is where I attempted to go to the first
available blank line.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("UpdateData").Select
Range("A2").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Sheets("MasterData").Select
End Sub


"Joel" wrote:

Post the recorded macro and we wil make changes as required. It is easier to
get it right by modifying the recorded code rather than send new code.

"Forest" wrote:

I have a Master Worksheet with historical data that I do all of my reporting
and charting from. It is rows of various data by create date with a unique
record identifier (Ticket Number). Each weekday, I get a new list of records
in and Update Worksheet with the past 7 days of records using MSQuery through
ODBC to external data. Some of the records repeat and others of them are new.
I would like to append only the NEW unique records (based on Ticket Number)
from the Update Worksheet into the Master Worksheet.

This what I tried and got stuck on --
The Master data is sorted so I can do a vlookup on the new record set. If
the record is new, I set the flag to 0, else 1. I created a macro to
autofilter for 0. In my macro, I selected Cell A1. I moved the cursor down 1
to eliminate the header row and then selected all the filtered data. This
would work fine if the first filtered row was always row 2 but as it turns
out, the macro recorded Row 54 as the first filtered row under the header
row. Unfortunately, this will not always be the case.

Can you please help me automate this task?