VBA Code - Sort by date, then copy paste data into another sheet

[axen]

Hey everyone.. first time poster here so i might be missing some useful info you need. Please let me know if you need more info.
I have a range of data which i need sorted by date, then copied and pasted into another sheet. The data will continually be added to so it needs to copy from sheet 1, then paste into sheet 2 but in the next blank cell.
Range = C11:AF20000
Step by step:
1. Sort data by date column (oldest to newest) <-- Starts at Column N11
2. Copy data from "drop" sheet in ranges C11:N20000
2. Paste sorted data into next available cell in Column D (Starting at D15 initially). - On sheet "Raw Data"

Ref Below.

OrdNo	AccCode	SecCode	Exch	Dest	SubDest	B/S	Volume	Price	Value	FXRate	Date
26502748		ZIP	ASX	DMA		S	1	590	5.9	1	18/11/2020 10:32
26503021		ZIP	ASX	DMA		B	1	592.5	5.925	1	18/11/2020 10:36
26503102		ZIP	ASX	DMA		S	1	591	5.91	1	18/11/2020 10:37

 

[Joe4]

Welcome to the Board!

Try this:

Sub MyDataCopy()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr As Long
    Dim nr As Long
    
'   Set worksheet variables
    Set ws1 = Sheets("drop")
    Set ws2 = Sheets("Raw Data")
    
'   Find last row of data in column N on "drop" sheet
    lr = ws1.Cells(Rows.Count, "N").End(xlUp).Row
    
'   Sort data on "drop" sheet
    ws1.Activate
    ws1.Range("C11:AF" & lr).Sort key1:=Range("N11"), order1:=xlAscending, Header:=xlNo
    
'   Find next row to paste to on "Raw Data" sheet
    nr = ws2.Cells(Rows.Count, "D").End(xlUp).Row + 1
    If nr < 15 Then nr = 15
    
'   Copy data to "Raw Data" sheet
    ws1.Range("C11:AF" & lr).Copy ws2.Range("D" & nr)
   
End Sub

 

[axen]

This is great! Thank You. can i add one more thing and have the code remove all data in ws "drop" in range C11:AF20000?
Thank You.

 

[Joe4]

This is great! Thank You. can i add one more thing and have the code remove all data in ws "drop" in range C11:AF20000?
Thank You.

Sure thing.

Try:

Sub MyDataCopy()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr As Long
    Dim nr As Long
    
'   Set worksheet variables
    Set ws1 = Sheets("drop")
    Set ws2 = Sheets("Raw Data")
    
'   Find last row of data in column N on "drop" sheet
    lr = ws1.Cells(Rows.Count, "N").End(xlUp).Row
    
'   Sort data on "drop" sheet
    ws1.Activate
    ws1.Range("C11:AF" & lr).Sort key1:=Range("N11"), order1:=xlAscending, Header:=xlNo
    
'   Find next row to paste to on "Raw Data" sheet
    nr = ws2.Cells(Rows.Count, "D").End(xlUp).Row + 1
    If nr < 15 Then nr = 15
    
'   Copy data to "Raw Data" sheet
    ws1.Range("C11:AF" & lr).Copy ws2.Range("D" & nr)

'  Delete data to "drop" sheet
    ws1.Range("C11:AF" & lr).ClearContents

End Sub

 

[axen]

This code works.. but i forgot to mention this data is being copied into a table if that makes a difference? Its called "raw data table" on "raw data" sheet. Its also copying the formatting from the "drop" sheet into the "raw data" sheet (i.e. white background) etc. Is there a way for it to paste values ONLY.
Thanks

 

[Joe4]

This code works.. but i forgot to mention this data is being copied into a table if that makes a difference? Its called "raw data table" on "raw data" sheet. Its also copying the formatting from the "drop" sheet into the "raw data" sheet (i.e. white background) etc. Is there a way for it to paste values ONLY.
Thanks

Yes, that makes a big difference! Working with data tables can be a real pain, as you usually need to find the last row in the table, and insert enough blank rows in the table to accomodate the number of rows you are copying in, then copy them in (I really am not a fan of Excel tables and hardly ever use them - they serve a purpose, but if I really need tables, I will usually use a database program like Microsoft Access first).

Here is a link that describes way to copy the values over with no formatting: How to Use VBA to Paste Values Only with No Formatting in Excel

 

[axen]

Thank You! no worries. I have the last row above the table with this formula: =MIN(ROW(RawDataTable1[ORDNO]))+COUNTA(RawDataTable1[ORDNO])