Link to home
Start Free TrialLog in
Avatar of "Abys" Wallace
"Abys" WallaceFlag for United States of America

asked on

Copy Data from one Workbook to another Workbook that has a table Until Blank row

Hi everyone ...

I'm working a project where I'm creating a userform for a data entry environment where the end user submits data into a worksheet within their workbook.  Once they're done they'll have an update button that I would like to COPY data from their workbook's worksheet to a Master Workbook's worksheet that has a table.

After all the data is copied from the end users workbook I would like the VBA to close and save the Master workbook and then delete the data from the end user's workbook.

I've researched high and low and found pieces of code that I placed together to seem to get the concept but when I hit UPDATE it just copies cell (A3) data until it reaches the end of the Master workbook's worksheet...  

Private Sub cmdUpdate_Click()
    
    Dim ThisWorkbook As Workbook
    Dim MasterWB As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lRow As Long
    Dim NextRow As Long
    Dim Group As Range, Mat As Range
    Dim CurCell_1 As Range, CurCell_2 As Range
     
    Application.ScreenUpdating = False
     
    Set MasterWB = Application.Workbooks.Open("H:\Projects\Master Process Tracker for Rayanne\test\MPT_Data_UPDATE.xlsx")
    Set ThisWorkbook = Application.Workbooks("MPT_EndUser.xlsm")
    Set ws1 = ThisWorkbook.Sheets("MAR")
    Set ws2 = MasterWB.Sheets("MAR")
     
NextRow = WorksheetFunction.CountA(MasterWB.Worksheets("MAR").Range("A:A")) + 1
MasterWB.Worksheets("MAR").Cells(NextRow, 1).Value = ThisWorkbook.Worksheets("MAR").Range("a3").Value
MasterWB.Worksheets("MAR").Cells(NextRow, 2).Value = ThisWorkbook.Worksheets("MAR").Range("b3").Value
MasterWB.Worksheets("MAR").Cells(NextRow, 3).Value = ThisWorkbook.Worksheets("MAR").Range("c3").Value
MasterWB.Worksheets("MAR").Cells(NextRow, 4).Value = ThisWorkbook.Worksheets("MAR").Range("d3").Value
MasterWB.Worksheets("MAR").Cells(NextRow, 5).Value = ThisWorkbook.Worksheets("MAR").Range("e3").Value
MasterWB.Worksheets("MAR").Cells(NextRow, 6).Value = ThisWorkbook.Worksheets("MAR").Range("f3").Value
MasterWB.Worksheets("MAR").Cells(NextRow, 7).Value = ThisWorkbook.Worksheets("MAR").Range("g3").Value
MasterWB.Worksheets("MAR").Cells(NextRow, 8).Value = ThisWorkbook.Worksheets("MAR").Range("h3").Value
MasterWB.Worksheets("MAR").Cells(NextRow, 9).Value = ThisWorkbook.Worksheets("MAR").Range("i3").Value
MasterWB.Worksheets("MAR").Cells(NextRow, 10).Value = ThisWorkbook.Worksheets("MAR").Range("j3").Value
MasterWB.Worksheets("MAR").Cells(NextRow, 11).Value = ThisWorkbook.Worksheets("MAR").Range("k3").Value
MasterWB.Worksheets("MAR").Cells(NextRow, 12).Value = ThisWorkbook.Worksheets("MAR").Range("l3").Value

'~~> Loop until blank cell found in users workbook

'<~~ Change as required
For Each Group In ws1.Range("A3:L3")
         '~~> Why this?
         Set CurCell_2 = ws2.Range("a3:l3")
         For Each Mat In ws1.Range("A3:l3")
             Set CurCell_1 = ws1.Cells(3, 1)
             If Not IsEmpty(CurCell_1) Then
                 CurCell_2.Value = CurCell_1.Value
                 Set CurCell_2 = CurCell_2.Offset(1)
             End If
         Next
     Next


End Sub

Open in new window


Any help ....  would be greatly appreciated!  :)   Thank you in Advance!
MPT-EndUser.zip
MPT-Data-UPDATE.zip
ASKER CERTIFIED SOLUTION
Avatar of andrewssd3
andrewssd3
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of "Abys" Wallace

ASKER

Thank You!  works perfect!  :)
@andrewssd3 is it possible to use this with more than 1 sheet... I have 3 others: PMG, EMT, AAR ... that'll go to the same size table in the Master:  PMG, EMT, AAR...  the trackers are:  EMTTracker, PMGTracker, AARTracker