Copying a table to new worksheet after it has been sorted from a previous worksheet

Shizo

New Member
Joined
Jan 5, 2025
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I have a table of a course schedule on worksheet 1 (Schedule By Date). I have a macro that copies the table and pastes it into a new sheet worksheet (Schedule by LD) the macro then sorts the table based on LD #, then by date. If I make any changes to the source table, it adds those changes correctly to the destination page. I am trying to copy and paste the newly sorted table from (Schedule by LD) to another worksheet (LD By Day). Everything is working fine up to this point but when I try to dynamically copy and paste the sorted table to (LD By Day) I run into a "paste method of worksheet class failed" error and any changes to the sorted table do not automatically transfer to the (LD By Day) worksheet. Here is the macro. Can you help me figure out why this is happening?

Sub TransferSortedTable()
'
' TransferSortedTable Macro
' transfer sorted table from schedule by LD to LD by Day

Sheets("Schedule by LD").Select
Range("Table14[#All]").Select
Selection.Copy
Sheets("LD By Day").Select
Sheets("LD By Day").Activate
ActiveSheet.Range("A2").Select
Application.CutCopyMode = False
Sheets("LD By Day").Paste
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit
End Sub

I will need help with a SUM macro as well once this is figured out.
Thank you for any assistance you may be able to provide!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Ok. That sorts things great!.... Little tweaks I need now .. Lets take them one at a time..
1. the auto replace "999" in the LD column on Schedule by Date no longer re-inputs the "999" if the cell is blank like we set it up before

Sub FillBlankCells()
' FillBlankCells Macro
On Error GoTo NoBlanks
Dim rng As Range
Set rng = Range("D3:D480").SpecialCells(xlCellTypeBlanks)

rng.Value = 999

NoBlanks:
'MsgBox "Press "OK"."
Exit Sub
End Sub
----------------------------------------------------------------------------------
Private Sub Worksheet_Activate()

Call FillBlankCells

End Sub
 
Upvote 0
Actually the "999" activate is working again when I switch from one sheet to the next.... Before if I changed the cell and made it blank then hit the tab button it would automatically change the cell to "999"... its just different but works.

Now I just need the transferandsorttable macro to work automatically or activate when the sheet is selected after changes in data are made in the source table...
It sorted the information correctly but does not add and sort newly inputted information when added.
 
Upvote 0
Paste the following in the sheet code module for "Schedule By Date" sheet :

VBA Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
TransferAndSortTable
End Sub
 
Upvote 0

Forum statistics

Threads
1,225,601
Messages
6,185,924
Members
453,333
Latest member
BioCoder84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top