VBA - Copying specific data to open workbook.

[noveske]

I am trying to copy data from workbook to another.
So far I was able to get the script to open the other workbook.
I thought that the issue was it couldn't locate the headers since they were in row 2.
That's just how I receive them.
I was able to set a condition to check if certain cells were empty to delete the first row.

wb1 sheet "Input", the opened workbook has headers Block, ID and Name.
wb2 "Service 1" sheet "list", the one being opened has 8 headers. I only need to copy 3 columns of data. Number, Name and Unit

So I need help getting data from wb2 to wb1.
Number to ID.
Name to Name.
Unit to Block.

Option Explicit

Sub OpenAndDeleteEmptyRows()
    Dim mainWorkbook As Workbook
    Dim dataWorkbook As Workbook
    Dim workerListSheet As Worksheet
    Dim rowToDelete As Range
    
    Set mainWorkbook = ThisWorkbook
    
    Dim dataFolderPath As String
    dataFolderPath = mainWorkbook.Path & "\Data\"
    
    Dim fileName As String
    fileName = Dir(dataFolderPath & "Service 1*")
    
    If fileName = "" Then
        MsgBox "No matching workbook found. Ensure the naming convention is correct.", vbExclamation
        Exit Sub
    End If
    
    Dim dataWorkbookName As String
    dataWorkbookName = dataFolderPath & fileName
    
    Set dataWorkbook = Workbooks.Open(dataWorkbookName)
    
    Set ListSheet = dataWorkbook.Sheets("List")
    
    If Application.WorksheetFunction.CountA(ListSheet.Range("C1:H1").Offset(0, 2)) = 0 Then
        ' If all cells are empty, delete row 1
        Set rowToDelete = ListSheet.Rows(1)
        rowToDelete.Delete
    Else
        MsgBox "Cells C1 to H1 in List' sheet are not all empty. No action taken.", vbInformation
    End If
End Sub

Initital wb:

When Service wb is opened:

What I'm trying to do:

 

[eiloken]

try this:

Option Explicit

Sub OpenAndDeleteEmptyRows()
    Dim mainWorkbook As Workbook
    Dim dataWorkbook As Workbook
    Dim ListSheet As Worksheet
    Dim rowToDelete As Range
    Set mainWorkbook = ThisWorkbook
    Dim dataFolderPath As String
    dataFolderPath = mainWorkbook.Path & "\Data\"
    Dim fileName As String
    fileName = Dir(dataFolderPath & "Service 1*")
    If fileName = "" Then
        MsgBox "No matching workbook found. Ensure the naming convention is correct.", vbExclamation
        Exit Sub
    End If
    Dim dataWorkbookName As String
    dataWorkbookName = dataFolderPath & fileName
    Set dataWorkbook = Workbooks.Open(dataWorkbookName)
    Set ListSheet = dataWorkbook.Sheets("List")
    If Application.WorksheetFunction.CountA(ListSheet.Range("C1:H1").Offset(0, 2)) = 0 Then
        ' If all cells are empty, delete row 1
        Set rowToDelete = ListSheet.Rows(1)
        rowToDelete.Delete
    Else
        MsgBox "Cells C1 to H1 in List' sheet are not all empty. No action taken.", vbInformation
    End If
    Call GetData(ListSheet, mainWorkbook.Sheets("Input"))
End Sub

Private Sub GetData(ByVal wsi As Worksheet, ByVal wso As Worksheet)
    Dim i As Long, j As Long
    Dim rng As Range, cll As Range
    i = wsi.Cells(Rows.Count, 2).End(xlUp).Row
    Set rng = wsi.Range("B1:B" & i)
    For Each cll In rng
        If Not IsEmpty(cll) And Not cll.Value = "Number" Then
            j = wso.Cells(Rows.Count, 1).End(xlUp).Row
            wso.Cells(j + 1, 2).Value = cll.Value
            wso.Cells(j + 1, 1).Value = cll.Offset(, 4).Value
            wso.Cells(j + 1, 3).Value = cll.Offset(, 1).Value
        End If
    Next cll
End Sub