PDA

View Full Version : Sorting then send



MikeChch
08-12-2009, 02:53 PM
I am trying to create a fairly complicated (for me anyway) spreadsheet which sorts out names by start time(column D) and allocates them to different sheets according to the time.

I have attached a sample of the spreadsheet with a current macro which sorts by column. I need this to possibly be incorporated into the new macro if possible, as I need this info sent with the name to the new sheet.

I am just starting out with macros and would love it if any solution had some explanations so I can start to get my head around this new language. I am using Excel 2007.

MaximS
08-13-2009, 05:15 AM
try this one, see attached for details.

MikeChch
08-13-2009, 12:00 PM
Thanks for the reply MaximS. I tried running the macro but it came up with an error and when I went to debug it highlighted:

Set temp = wb.Worksheets("Template")

Any ideas?

MaximS
08-13-2009, 11:36 PM
I cannot see any issues with that - code works fine for me.
The only thing I can suggest is to check if you have "Template" worksheet and unhide it.

Template is a blank copy of the table.

MikeChch
08-14-2009, 01:24 PM
That was definately the problem, it works perfectly now.

One last thing. Which part of the code would I need to change to have it send data from various times? So that Shift 1 would receive entries that started at 0400 & 1000, sheet 2 would have 1000, 1200, 1400 & 1800 and sheet 3 would have 1400, 1800, 2000 & 2200.

I have updated my example sheet.

MaximS
08-16-2009, 03:23 AM
sorted, see attachment for details.

mdmackillop
08-16-2009, 03:51 AM
Please add a heading to Cell (E4) on EG1 (Space character will suffice), before running the code.

Option Explicit
Option Base 1
Sub Macro1()
Dim Arr1, Arr2, Arr3, ArrTimes
Dim Sht As Worksheet
Dim Tgt As Range
Dim i As Long, x As Long, y As Long
Arr1 = Array("Time", "400", "1000")
Arr2 = Array("Time", "1000", "1200", "1400", "1800")
Arr3 = Array("Time", "1400", "1800", "2000", "2200")
ArrTimes = Array(Arr1, Arr2, Arr3)
Application.ScreenUpdating = False
For i = 1 To 3
Set Sht = Sheets("Shift " & i)
Set Tgt = Sht.Range("C5")
x = UBound(ArrTimes(i))
Range("P1:P" & x).Value = Application.Transpose(ArrTimes(i))
Range("A4:L14").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("P1:P" & x), CopyToRange:=Range("Q1"), Unique:=False
y = Cells(Rows.Count, "S").End(xlUp).row - 1
Tgt.Resize(y, 9).Value = Range("S2").Resize(y, 9).Value

Range("P1:AB10").ClearContents
Range("P1:AB10").Borders.LineStyle = xlNone
Next
Application.ScreenUpdating = True
End Sub