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!

 

[Shizo]

Yes it is working. I just need it to not return a no blanks found error if the macro runs and no cells in Column D are blank. I can't find any codes that do that... all the ones I find are On Error GoTo or something weird which does not make my error stop and I dont want a message box...

I just want it to do nothing if there are no blanks in Column D and insert "999" if there is a blank.

 

[Logit]

Sub FillBlankCells()
'
' FillBlankCells Macro

Range("D3:D480").Select

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

 

[Logit]

Sorry ... try this version :

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

    rng.Value = 999
    
NoBlanks:
'MsgBox "There were no blank cells to populate."
Exit Sub
End Sub

 

[Shizo]

That worked. You're awesome!

Next question:

So I have my table from Schedule By Date copying and pasting to a new worksheet Schedule By LD. On that new worksheet (Schedule By LD), a macro auto sorts the data by criteria. Column E is blank on Schedule By Date and therefore is also blank on Schedule By LD. What is the best way for me to be able to input data into Column E on my sorted table which will have that data auto-populate into Schedule by Date Column E....

The reason for this is I need to show when LD 1 is taught on a day of instruction Column E will have a "1.01" and for the LD 1 and day 1, for day 2 it will be "1.02". I have multiple LDs and will need to do the same for those. I do not mind adding these values in manually. 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 values much quicker.

I was trying to make a third worksheet that would put the schedule back to the original format after 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. Since Schedule by LD is pulling its info from Schedule by Date, how do I make data populate into Schedule by Date from the sorted table in Schedule by LD.. not sure of the best way to accomplish this with all the tables being linked.

Any thoughts?

 

[Logit]

Help me understand.

What is an LD ?

What is your though process for using two or even three sheets to accomplish your goal. Is it possible to keep everything on one sheet ?

Would it be possible to maintain everything on a single worksheet, in a single table and perform the sort on the data there ? With the ability
to return the data to it's original layout when needed ?

Without passing along confidential information is it possible for your to post a mockup of your workbook on a download site for review ? You
would post the link here for me to download it. DropBox.com or something similar would suffice for a download site.

Please understand it is very important for me to have an answer to each of those questions I've posted above. Having each answer helps me
understand. Please don't overlook each question. Thank you so much.

 

[Shizo]

An LD is a Learning Domain.... It is what is used to teach a certain topic in the course material.

I can't keep it all on one sheet because I need to have each schedule separate. The approving agency wants to see the daily schedule and the sorted break down of the LDs course material.

I am at work today but will try to get a copy of the file in a dropbox link that you can look at this evening when I get home.

Thank you.

 

[Logit]

Gotcha

 

[Shizo]

Gotcha

Dropbox

www.dropbox.com

Here is the dropbox link for the document. You will see 3 worksheets. First is the schedule by date, second is the schedule sorted by LD #, third is the schedule sorted back to schedule by date..... Wanting to get the data I put into the "LD/Day" column on sheet 2 to autopopulate into sheet 1 or sheet 3. Not sure how to do this with the tables being copied and linked to the next sheet. Don't know if the looping will cause it to not work.

Have a look and let me know what you think. Thanks!

 

[Logit]

Working on it. Will get back soon.

 

[Shizo]

Working on it. Will get back soon.

sounds great