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

[Shizo]

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!

 

[Logit]

You can try the following :

Change this :
ActiveSheet.Range("A2").Select
Application.CutCopyMode = False
Sheets("LD By Day").Paste

To this :
ActiveSheet.Range("A2").Select
Sheets("LD By Day").Paste
Application.CutCopyMode = False

And you should be able to change this :
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

To this :
Columns("A:I").EntireColumn.AutoFit

 

[Shizo]

I tried that but now its saying the table range is having issues... I think maybe you can help me with a better solution....

I have a schedule for a course in a table "A2:I480" headers in are row 2. Column D is left blank on my source schedule worksheet (Schedule by Date). I have a macro copying and pasting the table from (Schedule by Date) and sorting it into a new worksheet (Schedule by LD) based off certain criteria. On (Schedule by LD) I want to input data into Column D that will auto populate back into the corresponding cells in Column D on (Schedule by Date).... The reason for this is I need to show when LD 1 is used on the first day of instruction Column D will have a "1.01" and for day 2 it will be "1.02". I have multiple LDs and will need to do the same for those. If I use the sorted table to enter these values, it makes it easier to see on which day the LD was taught so I can put in the LD day taught values much quicker. I was trying to make a third worksheet that would put the schedule back to the original format of being sorted by date with the added 1.01 etc values entered into it so I would have a completed Schedule by Date with the LD info included... not sure of the best way to accomplish this with all the tables being linked.

Unfortunately I am unable to post images of the information due to confidentiality issues....

 

[Logit]

What error code/s are you receiving and what code line/s are being highlighted ?

 

[Shizo]

Lets go step by step....

Currently I am trying to fill blanks in Column D (D3:D480) with "999".... I am getting an error when no blanks are found.... I want to correct this and if changes are made to table causing a cell in Column D to become blank, I want the 999 to autofill back in.....

Once this is corrected we can get into the table sorting above

Here is the code:

Sub FillBlankCells()
'
' FillBlankCells Macro
'

'
ActiveWindow.SmallScroll Down:=-102
Range("Table1[LD]").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveCell.FormulaR1C1 = "999"
Range("D98").Select
End Sub


Error shows when no cells found blank...

 

[Logit]

I was able to populate Col A using the following macro.

Your macro gives the Table Name as : Table1[LD]
Initially that created an error code (don't recall what it was now, sorry).
I believe using the close/open brackets causes the error.

As you can see in this macro version the Table name was changed to something
more acceptable to Excel.

Sub FillBlankCells()
'
' FillBlankCells Macro
'

'
ActiveWindow.SmallScroll Down:=-11
Range("Table4").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveCell.FormulaR1C1 = "=999"
Range("F11").Select
End Sub

Also you were using this as the formula : ActiveCell.FormulaR1C1 = "999"
All formulas begin with the EQUAL sign, so after changing that Excel was
able to insert the 999 into Col A.

I hope this helps ?

 

[Shizo]

What do I need to add so if no blanks are found, I do not get the error of no blanks found?

 

[Logit]

Try this macro version. Let me know if it works for you.

Option Explicit

Sub FillBlankCells()
    '
    ' FillBlankCells Macro
    '

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim cell As Range
    
    ' Set the worksheet and table
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Adjust the sheet name if necessary
    Set tbl = ws.ListObjects("Table4")
    
    ' Loop through each cell in the table's data body range
    For Each cell In tbl.DataBodyRange
        If IsEmpty(cell) Then
            cell.FormulaR1C1 = "=999"
        End If
    Next cell
End Sub

 

[Shizo]

This code works great if there is a blank in Column D.

Sub FillBlankCells()
'
' FillBlankCells Macro

Range("D3:D480").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveCell.FormulaR1C1 = "999"
Range("D3").Select
End Sub

If there are no blanks - I get the no blanks found error.

The code you provided above puts "999" into all blanks in the table and I only want the 999 in the blanks in Column D

 

[Logit]

Ok .... so the macro with your edits is fine ?