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

Shizo

New Member
Joined
Jan 5, 2025
Messages
28
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!
 
I believe if you edit your table 'Source10' to include columns A:N the existing macro code will copy that range to the last sheet.

Let me know how you get along.
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
I believe if you edit your table 'Source10' to include columns A:N the existing macro code will copy that range to the last sheet.

Let me know how you get along.
The above suggestion did not work but I made some adjustment and I think we are getting very close!

Please see the attached updated document in the dropbox link.

1. Module 1 copies the table from "Schedule by Date" and pastes it into the "Schedule by LD" then sorts the data appropriately
2. Sheet code for "Schedule by Date" has the adding "999" to blank cells and the activate code when a cell is blank and this also works appropriately
3. Both "Schedule by Date" and "Schedule by LD" have conditional formatting to fill cells when certain numbers are entered into certain columns which works appropriately
4. Module 2 copies the table from "Schedule by LD" and pastes it into "LD by Day". It also copies the table in column N ("DataEntry") and pastes it into column E leaving the conditional formatting appropriately.

****The problem I am running into now is that when a change is made to "Schedule by Date" table, it does not transfer to the "Schedule by LD" table. Also, when I make a change to the "DataEntry" table in column N on "Schedule by LD" it does not transfer to the table on "LD by Day".
~ When I try to do a worksheet change code or activate call code on the sheets themselves to activate the codes in the modules after a change, I get error codes like subscript out of range or something different
~ Please look at the modules and sheet codes and advise what I need to add to make the codes dynamic and update automatically whenever a change is made to the data in the tables.

Thank you!
 
Upvote 0
I removed all the code I had and replaced it with the code from your #39 post and it does not work. Unfortunately, it will not dynamically add new data to the tables when entered on the "Schedule by Date" page and therefore does not populate to the "LD by Day" page. Can you look at the code on the recent dropbox and see what needs to be added to that to make it work. When I input data then go to the macro and select run it will update but i want it to do it automatically.
 
Upvote 0
I don't understand why it doesn't work there. In A97 I entered the term "test". Then pressed ENTER and it auto transferred that to all sheets as an update.

Be certain in the sheet level mdule for SCHEDULE BY DATE and SCHEDULE BY LD you have the following macro entered :

VBA Code:
Option Explicit

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

That insures any changes to the Tables on both sheets are automatically updated to all sheets.
That auto macro may be too much for the average user. It may be necessary to NOT have that macro in both SHEET LEVEL modules but rather
have a command button on the SCHEDULE BY DATE sheet that runs the main macro.
 
Upvote 0
Your last suggestion is not working with my files for some reason. I think the issue may be that the vba has the pastespecial as pasteall and I wonder if it will work if we change it to pastelink?

' Copy Table1 from "Schedule by Date" to "Schedule by LD"
Sheets("Schedule by Date").ListObjects("Table1").Range.Copy
wsLD.Range("A1").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
wsLD.Columns("A:I").EntireColumn.AutoFit
 
Upvote 0
You've lost me there. Only thing I can recommend is to try what you believe will work and see if it does.
 
Upvote 0
Hello, I am almost done bothering you.... lol

I believe I have my workbook doing all I want it to do now but I just need help with one thing that seems to be a problem for many people.

I have a range copied and pasted to a worksheet. It is then sorted based on two criteria - This all works perfectly.
Problem now is the blank cells appear at the top of the sorted range and I need to move them to the bottom WITHOUT deleting the rows or clearing the formatting. I simply want to move the rows that don't have the information to the bottom of the newly sorted range. Here is the code I currently have: (What should I add to do the desired move?)

Sub CopyAndSortForPOST()
'
' CopyAndSortForPOST Macro

Sheets("LD by Day").Select
Range("A2:I480").Select
Selection.Copy
Sheets("Schedule for POST").Select
Range("A2").Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False

Dim cell, rng As Range
Set rng = Range("D2:D480")

For Each cell In rng
If cell.Value = "999" Then
cell.Value = "-"
Else
End If
Next cell

Range("A2:I480").Select
ActiveWorkbook.Worksheets("Schedule for POST").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Schedule for POST").Sort.SortFields.Add2 Key:= _
Range("B3:B480"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("Schedule for POST").Sort.SortFields.Add2 Key:= _
Range("C3:C480"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Schedule for POST").Sort
.SetRange Range("A2:I480")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub
 
Upvote 0
Taking a stab at this ... untested here.

VBA Code:
Sub CopyAndSortForPOST()
'
' CopyAndSortForPOST Macro

Sheets("LD by Day").Select
Range("A2:I480").Select
Selection.Copy
Sheets("Schedule for POST").Select
Range("A2").Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False

'Identify and move blank rows
Dim lastRow As Long, firstBlankRow As Long, i As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
firstBlankRow = lastRow + 1

For i = 2 To lastRow
    If WorksheetFunction.CountA(Rows(i)) = 0 Then 'Check if the row is blank
        Rows(i).Copy Destination:=Rows(firstBlankRow) 'Move blank row to below data
        firstBlankRow = firstBlankRow + 1
        Rows(i).Delete Shift:=xlUp 'Delete the blank row at the top
        i = i - 1 'Adjust the counter since we deleted a row
        lastRow = lastRow - 1 'Adjust the last row since we deleted a row
    End If
Next i

Dim cell, rng As Range
Set rng = Range("D2:D480")

For Each cell In rng
    If cell.Value = "999" Then
        cell.Value = "-"
    End If
Next cell

Range("A2:I480").Select
ActiveWorkbook.Worksheets("Schedule for POST").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Schedule for POST").Sort.SortFields.Add2 Key:= _
    Range("B3:B480"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
ActiveWorkbook.Worksheets("Schedule for POST").Sort.SortFields.Add2 Key:= _
    Range("C3:C480"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
With ActiveWorkbook.Worksheets("Schedule for POST").Sort
    .SetRange Range("A2:I480")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Let me know if it works.
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,136
Members
453,642
Latest member
jefals

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