CODEWITHSUNDEEP
vbaexcel
jhovyn
jhovyn

Reputation: 265

VBA - How to copy the data from workbook to masterfile in VBA?

Good Day!Please help me on this..

  1. I want to copy the data from these columns ID Code,Name,Store,Product Code, Brand,Form,Days,Category and Sales Using VBA RawData

  2. Paste these values to "Master file"(template) to their desired column. Master File Template

Please help me I am new to VBA.

Upvotes: 0

Views: 458

Answers (1)

Plagon
Plagon

Reputation: 3138

Put this in ThisWorkbook, modify path and filename and it should do, what you wanted. It will take ALL dataset of the first file. To modify the range of data you you could link the var "FirstDataSet" to a cell and it will start from there or save the last row it copied. This Question was asked and answered like hundreds of time on this site.

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\User\Desktop\Files\"
Filename = "1.xlsx"

    Set wbk = Workbooks.Open(Path & Filename)

    Set sht = Workbooks(Filename).Worksheets(1) 'First Sheet in File
    Set msht = ThisWorkbook.Worksheets(1) '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
    FirstDataSet = 2 'First Data Set in File

    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
        'msht.Range("M" & lRM + 1).Value = sht.Range("K" & i).Value 'Sales
    Next i
    'This
    sht.Range("K2:AV" & lrF).Copy _ 
    Destination:= msht.Range("K2")
    wbk.Close True
End Sub

Upvotes: 1

Related Questions