I am trying to build out a schedule system where I sort by duration :30 1:00 2:00 etc... then insert content cells from 3 different sheets into the slots, insert msg 1 from sheet one then msg 1 from sheet 2 then msg 1 from sheet 3 then going back to sheet 1 to get msg 2 etc until there is no more content then repeating.
Attached is a sheet with a macro I have which sorts and creates a number system, and inserts content but I could only get the one piece of content inserted and not the variety from the different sheets. There is a a sheet with the final resuts. I do have a loop going on which I have this code inserted , not sure if I need to do the second part outside of the loop or not.
Thanks
Dale
Sub AUTOSCHEDULER()
'
Sheets("SCHEDULE").Select
'Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
Dim sPass As String
Dim rng As Range
Dim A As Variant
Dim duration As Variant
Dim c As Variant
Dim d As Variant
Dim PRSPEC As Long
Dim PRMOV As Long
Dim PRNEW As Long
Dim p As Variant
With ActiveSheet
LR = .Cells(.Rows.Count, "L").End(xlUp).Row
PRSPEC = Sheets("SPECIALS").Cells(.Rows.Count, "A").End(xlUp).Row
PRMOV = Sheets("MOVIES").Cells(.Rows.Count, "A").End(xlUp).Row
PRNEW = Sheets("PROMOTABLES").Cells(.Rows.Count, "A").End(xlUp).Row
Dim sA As Worksheets
Dim sB As Worksheets
Dim sC As Worksheets
Columns("U:U").Select
Selection.NumberFormat = "0.00"
For A = 2 To LR
c = A + 2
d = A + 3
e = A + 4
f = A + 6
g = A + 8
h = A + 10
'this is for half hour program line duplication and segment setup
If Range("U" & A).Value < 0.03 Then
' Rows([A]).Select
' Selection.Copy
' Selection.Insert Shift:=xlDown
Range("N" & A).Value = "2"
p = 3
Range("Q" & A).Value = Sheets("SPECIALS").Range("A" & p).Text
Range("R" & A).Value = Sheets("SPECIALS").Range("B" & p).Text & " " & Sheets("SPECIALS").Range("C" & p).Text
Range("S" & A).Value = Sheets("SPECIALS").Range("D" & p).Text
Range("T" & A).Value = Sheets("SPECIALS").Range("E" & p).Text & " " & Sheets("SPECIALS").Range("F" & p).Text
Range("W" & A).Value = Sheets("SPECIALS").Range("G" & p).Text
Range("X" & A).Value = Sheets("SPECIALS").Range("H" & p).Text
p = p + 1
A = A + 1
Rows([A]).Select
Selection.Copy
End If
'this is for hour program line duplication and segment setup
If Range("U" & A).Value > 0.03 And Range("U" & A).Value < 0.08 Then
' Rows([A]).Select
' Selection.Copy
' Selection.Insert Shift:=xlDown
' Selection.Copy
' Selection.Insert Shift:=xlDown
' Selection.Copy
' Selection.Insert Shift:=xlDown
Range("N" & A).Value = "2"
p = 3
Range("Q" & A).Value = Sheets("PROMOTABLES").Range("A" & p).Text
Range("R" & A).Value = Sheets("PROMOTABLES").Range("B" & p).Text & " " & Sheets("PROMOTABLES").Range("C" & p).Text
Range("S" & A).Value = Sheets("PROMOTABLES").Range("D" & p).Text
Range("T" & A).Value = Sheets("PROMOTABLES").Range("E" & p).Text & " " & Sheets("PROMOTABLES").Range("F" & p).Text
Range("W" & A).Value = Sheets("PROMOTABLES").Range("G" & p).Text
Range("X" & A).Value = Sheets("PROMOTABLES").Range("H" & p).Text
p = p + 1
Range("N" & c).Value = "4"
p = 4
Range("Q" & A).Value = Sheets("PROMOTABLES").Range("A" & p).Text
Range("R" & A).Value = Sheets("PROMOTABLES").Range("B" & p).Text & " " & Sheets("PROMOTABLES").Range("C" & p).Text
Range("S" & A).Value = Sheets("PROMOTABLES").Range("D" & p).Text
Range("T" & A).Value = Sheets("PROMOTABLES").Range("E" & p).Text & " " & Sheets("PROMOTABLES").Range("F" & p).Text
Range("W" & A).Value = Sheets("PROMOTABLES").Range("G" & p).Text
Range("X" & A).Value = Sheets("PROMOTABLES").Range("H" & p).Text
p = p + 1
Range("N" & d).Value = "5"
p = 5
Range("Q" & A).Value = Sheets("PROMOTABLES").Range("A" & p).Text
Range("R" & A).Value = Sheets("PROMOTABLES").Range("B" & p).Text & " " & Sheets("PROMOTABLES").Range("C" & p).Text
Range("S" & A).Value = Sheets("PROMOTABLES").Range("D" & p).Text
Range("T" & A).Value = Sheets("PROMOTABLES").Range("E" & p).Text & " " & Sheets("PROMOTABLES").Range("F" & p).Text
Range("W" & A).Value = Sheets("PROMOTABLES").Range("G" & p).Text
Range("X" & A).Value = Sheets("PROMOTABLES").Range("H" & p).Text
p = p + 1
A = A + 3
Rows([A]).Select
Selection.Copy
End If
Next
Columns("U:U").Select
Selection.NumberFormat = "h:mm;@"
End With
End Sub
TESTSCHEDULER.xls
Bookmarks