Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying cell value to master report file which is closed? | Excel Worksheet Functions | |||
Roll-up data into master report | Excel Discussion (Misc queries) | |||
Master to Report Tabs | Excel Programming | |||
Master to Report Tabs | Excel Programming | |||
Master to Report Tabs | Excel Programming |