Excel Macro Copy from one worksheet and paste to other worksheet and sort the data pasted.

[Ben AFF]

Hi, I have a Macro that allows to select a file from an open workbook, copy the data from the selected workbook into the open workbook.
The copied / pasted data has multiple columns.
This macro is working well but I need the data pasted to be also sorted in descending order according to a specific column A (which has dates)
Please can you help me with what coding needs to be modified for the data pasted to be also sorted based on column A values dates? Thank you.

Sub OpenFile()

Dim FileToOpen As Variant
Dim OpenBook As Workbook

Application.ScreenUpdating = False


FileToOpen = Application.GetOpenFilename(Title:="Select Workbook", FileFilter:="Excel Files (*.xlsx*), *xlsx*")
'& "Excel Files(*xlsx*), *xlsx*")
If FileToOpen <> False Then

Set OpenBook = Application.Workbooks.Open(FileToOpen)

OpenBook.Sheets("Sheet1").UsedRange.Copy
ThisWorkbook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues

OpenBook.Close False
Else
MsgBox "No file was selected!"


End If

Application.ScreenUpdating = True


End Sub

 

[RobP]

Hi - maybe this could help you :

Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:S" & LastRow).Sort Key1:=Range("A2:A" & LastRow), Order1:=xlAscending, Header:=xlNo

The code finds the last row of data, then sorts column A as Ascending. You can use Descending also if you prefer ?

I dont know how big your data is, so I've assume its in columns "A to S" for example, starting row 2. But you can amend that

cheers
Rob

 

[Ben AFF]

Hi - maybe this could help you :

Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:S" & LastRow).Sort Key1:=Range("A2:A" & LastRow), Order1:=xlAscending, Header:=xlNo

The code finds the last row of data, then sorts column A as Ascending. You can use Descending also if you prefer ?

I dont know how big your data is, so I've assume its in columns "A to S" for example, starting row 2. But you can amend that

cheers
Rob

Thank you Rob, much appreciated I pasted this code just under this line
ThisWorkbook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:S" & LastRow).Sort Key1:=Range("A2:A" & LastRow), Order1:=xlAscending, Header:=xlNo


When I run the macro, its working as expected but the data is still not sorted. Please would you advise? Thank you

 

[RobP]

Can you tell me a little more about the data thats pasted, in terms of how many columns, and whats in each column ? I assume your dates are in Col A already ?
thanks
Rob

 

[Ben AFF]

Can you tell me a little more about the data thats pasted, in terms of how many columns, and whats in each column ? I assume your dates are in Col A already ?
thanks
Rob

Hi Rob yes, the range of columns is from A:BE (is a long data set and I adapted that in your line of code). The dates are in column A. The format of the dates is MM/DD/YYYY. Thank you.

 

[RobP]

Cant really see why - try putting it here instead - as my tests appear to work. I used this code : (sorry, jut amended it to your data size BE and Descending)

Sub OpenFile()

Dim FileToOpen As Variant
Dim OpenBook As Workbook

Application.ScreenUpdating = False


FileToOpen = Application.GetOpenFilename(Title:="Select Workbook", FileFilter:="Excel Files (*.xlsx*), *xlsx*")
'& "Excel Files(*xlsx*), *xlsx*")
If FileToOpen <> False Then

Set OpenBook = Application.Workbooks.Open(FileToOpen)

OpenBook.Sheets("Sheet1").UsedRange.Copy
ThisWorkbook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues

OpenBook.Close False

Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:BE" & LastRow).Sort Key1:=Range("A2:A" & LastRow), Order1:=xlDescending, Header:=xlNo
Else
MsgBox "No file was selected!"

End If



Application.ScreenUpdating = True


End Sub

 

[Ben AFF]

Cant really see why - try putting it here instead - as my tests appear to work. I used this code : (sorry, jut amended it to your data size BE and Descending)

Sub OpenFile()

Dim FileToOpen As Variant
Dim OpenBook As Workbook

Application.ScreenUpdating = False


FileToOpen = Application.GetOpenFilename(Title:="Select Workbook", FileFilter:="Excel Files (*.xlsx*), *xlsx*")
'& "Excel Files(*xlsx*), *xlsx*")
If FileToOpen <> False Then

Set OpenBook = Application.Workbooks.Open(FileToOpen)

OpenBook.Sheets("Sheet1").UsedRange.Copy
ThisWorkbook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues

OpenBook.Close False

Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:BE" & LastRow).Sort Key1:=Range("A2:A" & LastRow), Order1:=xlDescending, Header:=xlNo
Else
MsgBox "No file was selected!"

End If



Application.ScreenUpdating = True


End Sub

Perfect. It worked now. Thank you so Much Rob!

 

[RobP]

Great - I guess there was a little confusion between both your open files, so one had to be closed first.

Thanks for the feedback

Rob

 

[Ben AFF]

Great - I guess there was a little confusion between both your open files, so one had to be closed first.

Thanks for the feedback

Rob

Thank you Rob.

 

[MayraBotla]

Hi, I have a Macro that allows to select a file from an open workbook, copy the data from the selected workbook into the open workbook.
The copied / pasted data has multiple columns.
This macro is working well but I need the data pasted to be also sorted in descending order according to a specific column A (which has dates)
Please can you help me with what coding needs to be modified for the data pasted to be also sorted based on column A values dates? Thank you.

Sub OpenFile()

Dim FileToOpen As Variant
Dim OpenBook As Workbook

Application.ScreenUpdating = False


FileToOpen = Application.GetOpenFilename(Title:="Select Workbook", FileFilter:="Excel Files (*.xlsx*), *xlsx*")
'& "Excel Files(*xlsx*), *xlsx*")
If FileToOpen <> False Then

Set OpenBook = Application.Workbooks.Open(FileToOpen)

OpenBook.Sheets("Sheet1").UsedRange.Copy
ThisWorkbook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues

OpenBook.Close False
Else
MsgBox "No file was selected!"


End If

Application.ScreenUpdating = True


End Sub

Hey hi, so this code worked for me for one of my project, but I had quick question and wanted to see if we copy first set of data and move to next row. From next row the next set of data should be copied from another file. Could you hep me with that please