vba - 跟踪工作表

Question

我目前正在尝试将大约 150 个工作表合并为一张工作表。我正在使用以下代码

Sub Consolidate()
Dim sh1 As Worksheet, current As Worksheet, lr As Long, rng As Range, sh As Worksheet
Set sh1 = Sheets("Sheet1")
For Each current In Worksheets
   Set sh = Sheets(current.Name)
   lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
   Set rng = sh.Range("A9:A" & lr)
   rng.EntireRow.Copy sh1.Cells(Rows.Count, 1).End(xlUp)(2)
Next
End Sub

我想要做的是在每行的末尾添加一个条目，以便跟踪它来自哪个文件。每个工作表上都提供了该特定信息。

例如

AAA 1 Worksheet1
BBB 2 Worksheet2

任何帮助将不胜感激。

Answer 1

包括@Christmas007的评论:

Sub Consolidate()

    Dim sh1 As Worksheet,  lr As Long, rng As Range, sh As Worksheet
    Set sh1 = Sheets("Sheet1")

    For Each sh In Worksheets
       If sh.Name <> sh1.Name Then
       lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
       Set rng = sh.Range("A9:A" & lr)
       with sh1.Cells(Rows.Count, 1).End(xlUp).offset(1, 0)
           .Resize(rng.rows.count,1).Value = sh.Name
           rng.resize(rng.rows.count, _
                      rng.EntireRow.columns.count-1).Copy .offset(0,1)
       End with

       End If
    Next

End Sub