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?
|
Similar Threads
