Vba - 为什么在运行此代码时将excel关闭以将数据复制/粘贴到多个工作表中？ [英] Vba - why is excel closing when running this code for copy/paste data into multiple sheets?

问题描述

美好的一天！我有以下VBA代码，我正在尝试从多个工作簿（XLSX和XLSM）复制数据并将其粘贴到我的MasterFile中的多个工作表中。问题是，当我执行VBA代码时，我的excel突然停止并退出。请参阅下面的代码。谢谢！



我的尝试：

Good Day!I have this below VBA code which I'm trying to copy the data from multiple workbooks (XLSX and XLSM) and paste it into multiple sheets into my MasterFile. The problem is that when I am executing the VBA code my excel suddenly stop and exit. See my code below. Thank you!

What I have tried:

Public Sub Data()
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Dim sht, msht As Worksheet
Dim lRowFile, lRowMaster As Long
Dim FirstDataSet As Integer

On Error Resume Next

Path = "C:\Users\VBA\"
FirstDataSet = 2 'First Data Set in File

'First Workbook  
Filename = "Brand1.xlsx"

Set wbk = Workbooks.Open(Path & Filename)
Set sht = Workbooks(Filename).Worksheets("Raw") 'First Sheet in File
Set msht = ThisWorkbook.Worksheets("msh_Brand1") 'First Sheet in Master

lrF = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row 'Last Row in File
lRM = msht.Cells(Rows.Count, 2).End(xlUp).Row 'Last Row in Master

For i = FirstDataSet To lrF
    lRM = msht.Cells(Rows.Count, 2).End(xlUp).Row 'Last Row in Master
    msht.Range("B" & lRM + 1).Value = sht.Range("A" & i).Value 'ID
    msht.Range("C" & lRM + 1).Value = sht.Range("B" & i).Value 'Name
    msht.Range("E" & lRM + 1).Value = sht.Range("C" & i).Value 'Store
    msht.Range("F" & lRM + 1).Value = sht.Range("D" & i).Value 'Product Code
    msht.Range("I" & lRM + 1).Value = sht.Range("F" & i).Value 'Brand
    msht.Range("J" & lRM + 1).Value = sht.Range("G" & i).Value 'Form
    msht.Range("K" & lRM + 1).Value = sht.Range("H" & i).Value 'Days
    msht.Range("L" & lRM + 1).Value = sht.Range("I" & i).Value 'Category
Next
    sht.Range("K2:AZZ" & lrF).Copy _
Destination:=msht.Range("M2")
wbk.Close True

'Second Workbook
Filename = "Brand2.xlsm"

Set wbk = Workbooks.Open(Path & Filename)
Set sht = Workbooks(Filename).Worksheets("Raw") 'First Sheet in File
Set msht = ThisWorkbook.Worksheets("msh_Brand2") 'Second Sheet in Master

lrF = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row 'Last Row in File
lRM = msht.Cells(Rows.Count, 2).End(xlUp).Row 'Last Row in Master

For i = FirstDataSet To lrF
    lRM = msht.Cells(Rows.Count, 2).End(xlUp).Row 'Last Row in Master
    msht.Range("A" & lRM + 1).Value = sht.Range("B" & i).Value 'ID
    msht.Range("G" & lRM + 1).Value = sht.Range("A" & i).Value 'Name
Next
    sht.Range("C2:AZZ" & lrF).Copy _
Destination:=msht.Range("N2")
wbk.Close True

End Sub

推荐答案

尝试调试t他在Excel vba编辑器中的代码。

打开excel。按Alt + F11。 vba编辑器将打开。将您的代码粘贴到Thisworkbook中。要执行代码，请将光标放在此子项中并按F8。



应修改（启用）宏设置以在Excel中运行宏。如果存在路径和示例文件，则不会遇到任何问题。

Try to debug this code in Excel vba editor.
Open excel. Press Alt + F11. vba editor will open. Paste your code in Thisworkbook. To execute the code, put the cursor in this sub and press F8.

Your Macro setting should be modified (enabled) to run a macro in excel. If the path and sample file exist, you will not face any issue.

确保工作簿中提供了您提到的名称的工作表，并且数据范围具有正确的数据。而不是在循环中运行代码，首先尝试复制粘贴一个特定的单元格值。 （从代码复制单元格值后，只需在记事本应用程序中粘贴。如果数据被复制，它会将其粘贴到记事本中）如果它有效，那么复制粘贴循环也应该有效。您的单元格值（范围）应该在代码中正确。

Make sure the work sheet with the name mentioned by you is available in workbook, and data range has proper data. Instead of running the code in loop, first try to copy paste one particular cell value. (After copying cell value from code, simply do a paste in notepad application. If data is copied, it will paste that in notepad) If it works, then copy paste in loop also should work. Your cell value (Range) should be correct in code.

这篇关于Vba - 为什么在运行此代码时将excel关闭以将数据复制/粘贴到多个工作表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！