I have an input worksheet with set data input locations. The goal of the VB Script is to look at the range C6:C17 on the input worksheet; create a tab (named as the location) for each cell that has data associated, on each tab create generic text as headers then create a date series based on start and stop dates given on the input worksheet.

The next part of the process needs to be adding week numbers in column B that correspond to the date given in column A.

The code I am using is shown below (the ****** area is where I am stuck trying to figure how to code the logic with the variable cell references.):

Sub CreateLocationTabs()
    
    Dim rng As Range
    Dim rng2 As Range
    Dim cell As Range
    Dim FirstDate As Date
    Dim LastDate As Date
    Dim NextDate As Date
    Dim LastRow As Long
    Dim r As Long
                    
    Set rng = Worksheets("Input").Range("C6:C17")
    
    For Each cell In rng
    
    If cell <> "" Then
        
    Sheets.Add(After:=Sheets(Sheets.count)).Name = CStr(cell.Offset(0, -1).Value)
                           
    Range("A1").Value = "Date"
    Range("B1").Value = "Week"
    Range("C1").Value = "Weight (f)"
    Range("D1").Value = "Weight (t)"
        
    FirstDate = Worksheets("Input").Range("C2").Value
    LastDate = Worksheets("Input").Range("C3").Value
    r = 2

    Do
 
    Cells(r, 1) = FirstDate
    FirstDate = FirstDate   1
    r = r   1

    Loop Until FirstDate = LastDate
    
    Set rng2 = Range(Range("A2"), Range("A" & Rows.count).End(xlUp))
    
    For Each cell In rng2
    
    If cell <> "" Then
    
    ***********
            
    End If
    
Next cell

End Sub

Input Worksheet

Data Tab

CodePudding user response：

Something like this - see comments inline:

Sub CreateLocationTabs()
    
    Dim cell As Range, FirstDate As Date, LastDate As Date, dateCell As Range
    Dim r As Long, wsInput As Worksheet, wb As Workbook, ws As Worksheet
                    
    Set wb = ThisWorkbook '<< always specify a workbook
    Set wsInput = wb.Worksheets("Input") '<< avoid repeating the sheet name...
    
    For Each cell In wsInput.Range("C6:C17").Cells
    
        If cell.Value <> "" Then
            'get a reference to the added worksheet...
            Set ws = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
            ws.Name = CStr(cell.Offset(0, -1).Value)
            ws.Range("A1:D1").Value = Array("Date", "Week", "Weight (f)", "Weight (t)")
            
            FirstDate = wsInput.Range("C2").Value
            LastDate = wsInput.Range("C3").Value
            Set dateCell = ws.Range("A2")  '<< first cell to populate with date
            
            Do While FirstDate <= LastDate
                dateCell.Value = FirstDate
                'dateCell.Offset(0, 1).Value = Application.WeekNum(FirstDate) 'hard-coded value
                'or
                dateCell.Offset(0, 1).formula = "=WEEKNUM(" & dateCell.Address(False, False) & ")"  'or using a formula
                FirstDate = FirstDate   1         'next date
                Set dateCell = dateCell.Offset(1) 'next cell down
            Loop
        End If
    
    Next cell

End Sub