用原始文件中的数据更新主工作簿的VBA代码

Question

我正在尝试编写一个代码，使我能够从原始文件中复制数据，并针对原始文件a列中的每个值更新主工作簿中的各个工作表。

背景：原始文件的A列中提到了许多唯一id，其他列包含每个唯一id的相应数据。每个唯一id在主工作簿中有一个单独的工作表。

要求：

1. 删除原始文件中待删除页中提到的不需要的唯一ID

问题：

1. 我遇到的代码无法在主控形状中找到正确的工作表，它无法找到该工作表，当它使用名称创建新工作表时，它会给出一个错误，即工作表名称已经存在

请帮帮我。。。。我已经尝试解决这个问题一段时间了。

代码：

    Sub unique_ids()
    Dim NewFN As String, MasterFN As String
    Dim lrow As Long, i As Long, drow As Long, j as Long
    Dim rngf As Range, rngv As Range
    Dim SName As Variant
    Dim FoundDup As Range

    'Open the Master file
    proceed:
    MasterFN = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*",       Title:="Please open the Master File")
    If MasterFN = "" Then
    MsgBox "You have not selected a file."
    GoTo proceed
    Else
    Workbooks.Open Filename:=MasterFN
    End If
    MasterFN = ActiveWorkbook.Name

    'Open the raw file
    proceed1:
    NewFN = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the raw File")
    If NewFN = "" Then
    MsgBox "You have not selected a file."
    GoTo proceed1
    Else
    Workbooks.Open Filename:=NewFN
    End If

    'Save backup file
    ActiveWorkbook.SaveAs Filename:="D:\Counts-" & Format(Date, "dd-mmm-yy") & ".xlsx", FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
    Workbooks("Counts-" & Format(Date, "dd-mmm-yy") & ".xlsx").Close
    Workbooks.Open Filename:=NewFN
    NewFN = ActiveWorkbook.Name

    'Delete the "to be removed" IDs
    Sheets("counts").Select  
    For Row = Range("A65536").End(xlUp).Row To 2 Step -1 

    Set FoundDup = Sheets("To be deleted").Range("A:A").Find(Cells(Row, 1), LookIn:=xlValues, lookat:=xlWhole) 

    If Not FoundDup Is Nothing Then 
        Cells(Row, 1).EntireRow.Delete 
    End If 

    Next Row

    ‘Update Data

    For j = 2 To lrow
    SName = Workbooks(NewFN).Worksheets("counts").Range("K" & j).Value
    On Error GoTo new_tab
    Workbooks(NewFN).Worksheets("Counts").Range("A" & j & ":I" & j).Copy     Workbooks(MasterFN).Worksheets(SName).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
    Workbooks(MasterFN).Worksheets(SName).Range("A" & Rows.Count).End(xlUp).Value = Format(Date, "dd-mmm-yy")
    drow = Workbooks(MasterFN).Worksheets(SName).Range("K" & Rows.Count).End(xlUp).Offset(-1, 0).Row
    Workbooks(MasterFN).Worksheets(SName).Range("K" & drow - 1 & ":S" & drow - 1).Copy Workbooks(MasterFN).Worksheets(SName).Range("K" & drow & ":S" & drow)
    Next j

    new_tab:
    MsgBox "New ID encountered", vbCritical
   Workbooks(MasterFN).Sheets.Add(after:=Workbooks(MasterFN).Sheets(Worksheets.Count)).Name = SName
    Workbooks(NewFN).Worksheets("counts").Range("A" & j & ":I" & j).Copy       Workbooks(MasterFN).Worksheets(SName).Range("B" & Rows.Count).End(xlUp)
    Workbooks(MasterFN).Worksheets(SName).Range("A" & Rows.Count).End(xlUp).Value = Format(Date, "dd-mmm-yy")
    drow = Workbooks(MasterFN).Worksheets(SName).Range("K" & Rows.Count).End(xlUp)
    Workbooks(MasterFN).Worksheets(SName).Range("K" & drow - 1 & ":S" & drow - 1).Copy  Workbooks(MasterFN).Worksheets(SName).Range("K" & drow & ":S" & drow)

    MsgBox "This work is now complete, new sheet added - " & SName

    End Sub        

Answer 1

我在这里看到的第一个潜在问题是j=2到lrow的循环，你一直引用变量I，而不是我假设的j。我看不到变量是否已在任何地方初始化？