Copy, paste, then sort using VBA

[Burrgogi]

Every once in a while Fanatical (website) runs a bundle sale. It's listed in this format:


When I copy & paste this manually into Excel, (Ctrl + C, then Ctrl + V), it looks just fine. See 2nd screenshot. I tried to write up a quick VBA solution to this repetitive process and I've run into a slight problem. The VBA routine tries to cram everything into cell A1 which is incorrect. I'm not versed enough with VBA to correct this problem. Also if someone can help me sort the table from highest to lowest using the Steam user rating column shown in percentage, I'd really appreciate it! EDIT: I know how to sort in Excel, I'm asking how to incorporate that into my current VBA routine.

Here's my code:

Sub Paste_newSale()
   Sheets("Sheet").Copy , Sheets(Sheets.Count)
   ActiveSheet.Name = Format(Date, "dd_mm_yyyy")
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard
SText = DataObj.GetText(1)
ActiveSheet.Range("A2").Value = SText
End Sub

 

[Burrgogi]

​

We have a winner ‼ Same player read again post #6 but well this time as never the workbook module was stated​

​

Spoiler

but the worksheet module …

So each time I want to do the same, I need to insert a new worksheet in my workbook and save your code there? Unless I've misunderstood something, this seems a bit awkward to do.....

 

[Marc L]

​

Well read as I never stated each time ! Again the code just must be pasted in the destination / result worksheet.​

​

Once pasted it will stay in place if only the workbook is saved as macro (.xlsm) or better as binary format (.xlsb) …​

​

 

[Burrgogi]

​

Well read as I never stated each time ! Again the code just must be pasted in the destination / result worksheet.​

​

Once pasted it will stay in place if only the workbook is saved as macro (.xlsm) or better as binary format (.xlsb) …​

​

That is not the solution I was looking for but I appreciate your efforts.

 

[Marc L]

​

Explain at least what you are looking for !​