Last Col, Row and Range Copy Paste from multiple sheets to one sheet

Question

I have multiple sheets that pull data from ODBC, and it gets updated on a regular basis. Each of these sheet track different thing. For instance, "Check" tab tracks all the check transactions, "CreditCardCharge" tab tracks all the CC transactions...etc. I have created identical labels for all the sheets. e.g. Date, Ref Num, Account Name, Memo, Amount...etc I need all these sheet to be mapped to one master data dump sheet whenever I update the file.

This is the work flow:

Clear all data in "LP_DataDump" tab starting from A2
Copy "Check" tab data from A3 to Col J end row
Paste copied data to "LP_DataDump" starting from A2
Copy "CreditCardCharge" tab data from A3 to Col J end row
Paste copied data to "LP_DataDump" starting from last empty row

I started with this below, but it's not working.

Can anyone advice?

Sub CopyCheckValue()

Dim Check As Worksheet
Dim LastRow As Long

Set Check = Sheets("Check")
LastRow = Check.Cells(Check.Rows.Count, "A").End(x1Up).Row

Check.Range("A3:J" & LastRow).Copy _
        Destination:=Worksheets("LP_DataDump").Range("A2")

End Sub

what is not working there ? your code look fine to me (for the first 3 points you mentioned in your workflow). Did you try to implement the last 2 ? — Shai Rado

Impuls3H Impuls3H · Accepted Answer · 2017-04-04T09:53:53

Does sheet "Check" contain only your data? Can I make a guess, is it due to the .End method skipping your data? If the last row in the "Check" sheet is your data, the .End method will find the end above it.

LastRow = Check.Cells(Check.Rows.Count, "A").End(xlUp).Row

To this:

LastRow = Check.Rows.Count

If your columns A and B have formulas that are empty while columns C to J do not have formulas but only data, I would recommend the code below, assuming there will always be data from C3 onwards and no empty cells in between C3 and the end of the table.

LastRow = Check.Cells(3, "C").End(xlDown).Row

If there are empty cells in between or formulas in other columns as well, use the LastRow as the first suggestion. Then use a loop to assign the values into the other sheet instead of copying and pasting the whole range.

PasteRow = 2
For RowCounter = 3 to LastRow
    If Check.Cells(RowCounter, "A") <> "" Then
        Check.Range(Cells(RowCounter, "A"),Cells(RowCounter, "J")).Copy
        Worksheets("LP_Datadump").Cells(PasteRow,"A").PasteSpecial Paste:= xlPasteValues
        PasteRow = PasteRow+1
    End If
Next

If you have plenty of rows, I would recommend filtering before copying, that way you can avoid looping. Here's the code assuming you wish to filter it based on column A.

LastRow = Check.Rows.Count
Check.Range("A3:J" & LastRow).AutoFilter Field:=1, Criteria:="<>"
Check.Range("A3:J" & LastRow).Copy
Worksheets("LP_Datadump"_.Cells(2, "A").PasteSpecial Paste:= xlPasteValues
Check.Range("A3:J" & LastRow).AutoFilter Field:=1

Last Col, Row and Range Copy Paste from multiple sheets to one sheet

1 Answers