Click here to Skip to main content
15,889,877 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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:

VB
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
Posted
Updated 7-Jun-17 0:46am
v2

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.
 
Share this answer
 
v2
Comments
jhovyn 7-Jun-17 5:28am    
When I'm trying to debug, it shows that my first workbook "Brand1.xlsx" is successfully copying the data and paste it to the MasterFile sheet "msh_Brand1" but my other workbook "Brand2.xlsm" is not. Any help?
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.
 
Share this answer
 
Comments
CHill60 7-Jun-17 8:02am    
When suggesting alternatives to your original solution it's best to use the "Improve Solution" widget to update your solution rather than posting a new one. That way, all of your stuff is kept together and it is easier to follow the thread of *your* thinking. Alternatively reply to the OP's comment rather than posting multiple solutions (which are confusing for everyone)
Member 1403302 7-Jun-17 8:17am    
Thanks. This is the first time that I have posted a solution and I didn't notice the reply option.
CHill60 9-Jun-17 5:53am    
Welcome to the world of QA :-)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900