Extracting values from another worksheet doesn´t lead to the expected result (still blank spaces)

Question

Basically i want to extract values from the first worksheet in my workbook (=data export, name: "dataset") and use these values to automatically fill out a formular, which gets copied multiple times throughout a loop.

I´ve tried several approaches, the first one was to use a simple string, as a placeholder value --> the cells are overriden with the placeholder, everything okay.

As soon as i try to extract the values from the other table, the cells in the new, copied sheets don´t get filled any more / are replaced by blank space.

I tried several positions inside the script, but the functionality still stays broken. Do i need to set the active worksheet to "dataset" in some part of the script again? Or do i need to switch to another function (lookup or index-function)?

If someone is able to see the missing link - please tell me.

Sub dataExtraction()

Dim user_state As Integer
Dim index As Integer
Dim nums As Integer
Dim i As Integer
Dim x As Integer

Dim status As String
Dim billingMethod As String

Dim val_1 As String

status = "inaktiv"
billingMethod = "Projektverrechnung"
index = 0

With ThisWorkbook.Worksheets("dataset")

    For i = 1 To 500
            
            index = index + 1
            val_1 = Cells(i, 4).Value
            
        If .Cells(i, 13).Value = status Then

            ThisWorkbook.Sheets("TEMPLATE").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            
            'Cells(4, 3) = "TEST" == WORKING (but only test-value)
            'Cells(4, 3) = Cells(i, 4).Value == NOT WORKING, empty cell
            'Cells(4, 3) = val_1 == NOT WORKING, empty cell
        
            If index < 10 Then
            
            ActiveSheet.Name = "00" & index
            
            Else: ActiveSheet.Name = "0" & index

            End If
                    
        End If
    Next i
End With

MsgBox (index)

End Sub

Answer 1

Use Variables to Reference Worksheets

Sub ExtractInactive()

    ' Not used!?
    'Dim user_state As Long ' by default '= 0'!
    'Dim nums As Long ' by default '= 0'!
    'Dim x As Long ' by default '= 0'!

    ' Define constants.
    Const CRITERIA_STATUS As String = "inaktiv"
    Const BILLING_METHOD As String = "Projektverrechnung"
    
    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Using the workbook reference ('wb'), reference the worksheets.
    Dim sws As Worksheet: Set sws = wb.Sheets("Dataset") ' source sheet
    Dim tws As Worksheet: Set tws = wb.Sheets("Template") ' template sheet
    
    Dim dws As Worksheet ' destination sheet; by default '= Nothing'!
    Dim Index As Long ' by default '= 0'!
    Dim i As Long ' by default '= 0'!
    Dim CurrentStatus As String ' by default '= ""'!
    Dim SomeString As String ' give it a descriptive name; by default '= ""'!
    
    ' By using the correct worksheet references ('sws', 'tws', 'dws'),
    ' reference the correct cells (ranges).
    
    For i = 1 To 500
        SomeString = CStr(sws.Cells(i, "D").Value)
        CurrentStatus = CStr(sws.Cells(i, "M").Value)
        If StrComp(CurrentStatus, CRITERIA_STATUS, vbTextCompare) = 0 Then
            tws.Copy After:=wb.Sheets(wb.Sheets.Count) ' copy
            Set dws = wb.Sheets(wb.Sheets.Count) ' reference
            Index = Index + 1 ' increase index
            dws.Name = Format(Index, "0#") ' rename
            
            ' Now copy to the destination worksheet ('dws'):
            'dws.Range("C4").Value = "TEST"
            ' or:
            dws.Range("C4").Value = sws.Cells(i, "D").Value ' or SomeString
            ' or:
            'dws.Range("C4").Value = CurrentStatus
            ' etc.
        
        End If
    Next i
    
    MsgBox Index & " sheet" & IIf(Index = 1, "", "s") & " created.", _
        vbInformation

End Sub