Populatin cells in different sheet based on list in sheet 1

[lman715]

Hello,

Background... In sheet 1 of my macro workbook is a list of server names in column A. The list constantly changes so I developed a loop macro to perform most of what I need it to do. The macro loads log files based on the server name.

Column A
Server1
Server2
Server3
.
Server20

Once the logs are loaded, I then use a separate macro to populate a "Counts" worksheet with data generated during the load. I have been modifying this manually, but it's a nightmare when I need to make a change. My "Counts" macro contains mulitple lines like this:

Sheet("Counts").Range("A11") = "Server1"
Sheet("Counts").Range("A12") = "Server2"
Sheet("Counts").Range("A13") = "Server3"
.
Sheet("Counts").Range("A20") = "Server20"
------------------------------------------------------------------
Sheet("Counts").Range("B11") = Sheets("Server1").Range(C1")
Sheet("Counts").Range("B12") = Sheets("Server2").Range(C1")
Sheet("Counts").Range("B13") = Sheets("Server3").Range(C1")
.
Sheet("Counts").Range("B20") = Sheets("Server20").Range(C1")
------------------------------------------------------------------
Sheet("Counts").Range("C11") = Sheets("Server1").Range(C2")
Sheet("Counts").Range("C12") = Sheets("Server2").Range(C2")
Sheet("Counts").Range("C13") = Sheets("Server3").Range(C2")
.
Sheet("Counts").Range("C20") = Sheets("Server20").Range(C2")


I am trying to incorporate this into my loop so that I can remove these lines and the necessity to update the server name in multiple places.

I would like to add this to the load section of my loop, however, I am not sure how to get the macro to go to the destination cell in the "Counts" sheet, enter the data, and incriment to the next cell. Additionally, the number of server names changes as well, so that is why I do not use a constant number.

Any help would be greatly appreciated.

Here is the code I use to load the log files and create the worksheets:

Sub Server_Log_Load()
'
' Create primary Log file
    Dim SerCell As Range
    Dim SerRange As Range
    Set SerRange = ActiveWorkbook.Sheets(1).Range("A2")
    Set SerRange = Range(SerRange, SerRange.End(xlDown))
    Workbooks.Add
    Application.DisplayAlerts = False
    Sheets(1).Name = "Log Combined"
    Sheets(2).Name = "Counts"
    Sheets(3).Delete
'
' Create Worksheets
    For Each SerCell In SerRange
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = SerCell.Value  ' adds and renames the new worksheet
    Next SerCell
    Dim WBMain
    ActiveWorkbook.SaveAs Filename:="C:\Processed\Log.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    WBMain = ActiveWorkbook.Name
'
' Load Log files in Directory
    For Each SerCell In SerRange
    If Dir("C:\logs\auditdata_" & SerCell.Value & "*.log") <> "" Then
        Workbooks.OpenText Filename:="C:\logs\auditdata_" & SerCell.Value & "*.log", _
            StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Comma:=True, TrailingMinusNumbers:=True
        Range("C1") = WorksheetFunction.CountA(Range("A:A"))
        Range("A:E", Range("A" & Rows.Count).End(xlUp)).Copy Destination:=(Workbooks(WBMain).Sheets(SerCell.Value).Range("A1"))
    End If
    Next SerCell
'
End Sub