Doug,
So i added your Code to mine...
My Code...which is in Sheet1 (Blank)
Sub Copy_Sheet()
If Range("A2").Value >= Range("A1").Value Then
MsgBox "This Date you Selected has passed...Please Click Undo, and delete the Tab Below Labeled " & Sheets(1).Range("X12").Text
Else
End If
Dim wSht As Worksheet
Dim shtName As String
shtName = Sheets(1).Range("X12")
For Each wSht In Worksheets
If wSht.Name = shtName Then
MsgBox "Sheet already exists...Use the Button " & _
"on the TAB titled BLANK!" & _
" **Remember to select a New Date!"
Exit Sub
End If
Next wSht
Sheets(1).Copy Before:=Sheets(1)
Sheets(1).Name = shtName
Sheets(shtName).Move After:=Sheets(Sheets.Count)
End Sub
Your Code:
Sub sort()
Dim strnames As String
Dim varnames As Variant
Dim varnamesandrows()
Dim rnganchor As Range
Dim rngtarget As Range
Dim lngOffset As Long
Set rnganchor = Worksheets(1).Range("C12")
Dim i As Long, j As Long, k As Long, n As Long
i = 0
strnames = ""
Do While rnganchor.Offset(11 * i + 1, 0) <> ""
strnames = strnames & "|" & rnganchor.Offset(11 * i + 1, 0)
i = i + 1
Loop
strnames = Mid(strnames, 2)
varnames = Split(strnames, "|")
j = Val(Format(UBound(varnames)))
ReDim varnamesandrows(j, 1)
For i = LBound(varnames) To UBound(varnames)
varnamesandrows(i, 0) = varnames(i)
varnamesandrows(i, 1) = 11 * i + 1
Next i
Call BubbleSort(varnamesandrows)
Sheets("Sheet1").Copy Before:=Sheets(2)
Set rngtarget = Worksheets("Sheet1 (2)").Range("C12")
For i = LBound(varnames) To UBound(varnames)
rngtarget.Offset(11 * i + 1, 0).Value = varnamesandrows(i, 0)
n = varnamesandrows(i, 1)
For j = 1 To 7
For k = 1 To 6
rngtarget.Offset(11 * i + 1 + j, k).Value = rnganchor.Offset(n + j, k).Value
Next k
Next j
Next i
End Sub
Sub BubbleSort(arr)
Dim strTemp1 As String
Dim strTemp2 As String
Dim i As Long
Dim j As Long
Dim lngMin As Long
Dim lngMax As Long
lngMin = LBound(arr)
lngMax = UBound(arr)
For i = lngMin To lngMax - 1
For j = i + 1 To lngMax
If arr(i, 0) > arr(j, 0) Then
strTemp1 = arr(i, 0)
strTemp2 = arr(i, 1)
arr(i, 0) = arr(j, 0)
arr(i, 1) = arr(j, 1)
arr(j, 0) = strTemp1
arr(j, 1) = strTemp2
End If
Next j
Next i
End Sub
soooo...
what my code does is this...
when the workbook is opened theres a worksheet called "Blank".
on that work sheet theres pull down (data validation) in C2 (which is referenced to X12 in format MMM dd, yyyy) that has every 1st Sunday in the year...when my code above is activated based on the date in the pull down window, my code above creates a new worksheet based on the value in X12 (the MMM dd, YYYY) value in X12. can you make your code work with mine....make sense sir...ohhh and thank you!!!
when putting your code in with mine it errors out with
Compile error:
Member already exists in an object module from which this object module derives....
Is it possible for you to try your code by doing the following...
Create a workbook, name one worksheet Blank
delete sheet2 and 3
Put My code in ALT-F11 of the Blank worksheet...
In cell X12 put the date of Mar 10, 2013 in that format...
run my code.....
it should have created a new work sheet entitled Mar 10, 2013 keeping the Blank intact (thats basically the template)
Now delete the work sheet entitles Mar 10, 2013 and put your code after mine...and run the codes...
Reply With Quote
