Master to Report Tabs
 

As I've said, this is above my programming knowledge. I'm just tryin
to create the new tabs, and here's what I have so far. I'm sure I'
wasting lots of time and many steps, but I think I may be on the righ
track. I'm pretty sure there are ways to do this without all th
goto's and my loops seem to be spinning me in circles. This code i
just for a two columns. Column A is File Name and Column B is Date.
know I don't need all the "activate" statements, but it helps me whil
stepping through the code. Any suggestions on how to fix this up?

Side note: Any suggestions on a book to self-teach how to do thi
better so I stop bugging everyone on here with simple questions?



Code
-------------------
Option Explicit
Dim i, r, j, v As Integer
Dim APV As String
Dim TabName
Dim yy, mm, dd
Dim FileName
Dim APVDate
Dim Sht As Object


Sub CreateNewTab2()
Workbooks("Reports.xls").Worksheets("Master").Acti vate
i = 2
Do
r = 1
Do Until IsEmpty(Cells(i, 2))
Cells(i, 2).Select
APV = Selection
yy = Year(APV)
mm = Month(APV)
dd = Day(APV)
TabName = "Report " & mm & "-" & dd & "-" & yy
Workbooks("APV Reports.xls").Activate
For r = 1 To Workbooks("APV Reports.xls").Worksheets.Count
Worksheets(r).Select
If TabName < ActiveSheet.Name Then
ActiveSheet.Next.Select
On Error GoTo 10
ElseIf TabName = ActiveSheet.Name Then
GoTo 20
End If
Next r
20: i = i + 1
Workbooks("Reports.xls").Worksheets("Master").Acti vate
Loop
10: Sheets.Add After:=ActiveSheet
ActiveSheet.Name = TabName
Workbooks("Reports.xls").Activate
i = i + 1
Loop
End Su
-------------------





This code is what I was messing with for transfering the file names t
the new tabs before I decided it was a lost cause and went to the ta
naming first. What a mess!



Code
-------------------
' Workbooks("APV Reports.xls").Activate
' Worksheets(r).Select
'10: If TabName = ActiveSheet.Name Then
' Workbooks("Reports.xls").Worksheets("Master").Acti vate
' FileName = Cells(i, 1)
' Workbooks("APV Reports.xls").Activate
' Worksheets(TabName).Select
' Range("A1").Select
' ActiveCell.FormulaR1C1 = "File Type"
' Range("B1").Select
' ActiveCell.FormulaR1C1 = "File #"
' v = 2
' Do
' If FileName = Cells(v, 1) Then
' GoTo 20
' Else
' v = Application.WorksheetFunction.CountA(Worksheets(Ta bName).Range("A:A"))
' Cells(v + 1, 1) = FileName
' Workbooks("Reports.xls").Worksheets("Master").Acti vate
' APVDate = Cells(i, 2)
' Workbooks("APV Reports.xls").Worksheets(TabName).Activate
' Cells(v + 1, 2) = APVDate
' v = v + 1
' End If
' Loop
' Else
'20: Next
-------------------

--
hyyft
-----------------------------------------------------------------------
hyyfte's Profile: http://www.excelforum.com/member.php...fo&userid=1318
View this thread: http://www.excelforum.com/showthread.php?threadid=26283