Results 1 to 6 of 6

Extract multi excel file into a single file

Threaded View

  1. #1
    Registered User
    Join Date
    07-03-2015
    Location
    Jakarta
    MS-Off Ver
    2010
    Posts
    12

    Extract multi excel file into a single file

    hello, I am trying to extract many profiles & product files into single file.
    on sheet "Merchant" had succeeded but for sheet "Produk" has not been successful.
    here it is my code
    Sub Button1_Click()
    Dim vaFiles As Variant
        Dim i As Long
        Dim WB As Workbook
        
        vaFiles = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", _
                  Title:="Select files", MultiSelect:=True)
        
        If IsArray(vaFiles) Then
            For i = LBound(vaFiles) To UBound(vaFiles)
                Set WB = Workbooks.Open(Filename:=vaFiles(i))
    
                   
      NextFree = ThisWorkbook.Sheets("Merchant").Range("B2:B" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
     ThisWorkbook.Sheets("Merchant").Range("B" & NextFree).Value = WB.Sheets("Merchant").Range("C4").Value
     ThisWorkbook.Sheets("Merchant").Range("B" & NextFree).Offset(0, 1).Value = WB.Sheets("Merchant").Range("C5").Value
     ThisWorkbook.Sheets("Merchant").Range("B" & NextFree).Offset(0, 2).Value = WB.Sheets("Merchant").Range("C6").Value
     ThisWorkbook.Sheets("Merchant").Range("B" & NextFree).Offset(0, 3).Value = WB.Sheets("Merchant").Range("C7").Value
     ThisWorkbook.Sheets("Merchant").Range("B" & NextFree).Offset(0, 4).Value = WB.Sheets("Merchant").Range("C8").Value
     ThisWorkbook.Sheets("Merchant").Range("B" & NextFree).Offset(0, 5).Value = WB.Sheets("Merchant").Range("C9").Value
     ThisWorkbook.Sheets("Merchant").Range("B" & NextFree).Offset(0, 6).Value = WB.Sheets("Merchant").Range("C10").Value
     ThisWorkbook.Sheets("Merchant").Range("B" & NextFree).Offset(0, 7).Value = WB.Sheets("Merchant").Range("C11").Value
     ThisWorkbook.Sheets("Merchant").Range("B" & NextFree).Offset(0, 8).Value = WB.Sheets("Merchant").Range("C12").Value
     ThisWorkbook.Sheets("Merchant").Range("B" & NextFree).Offset(0, 9).Value = WB.Sheets("Merchant").Range("C13").Value
     
     NextFree2 = ThisWorkbook.Sheets("Produk").Range("B2:B" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
     Dim k As Long
        For k = 3 To 251
            If Not IsEmpty(WB.Sheets("Produk").Range("C" & k)) Then _
            ThisWorkbook.Sheets("Produk").Range("B" & (NextFree2 + k - 3)) = WB.Sheets("Produk").Range("C" & k)
            ThisWorkbook.Sheets("Produk").Range("B" & (NextFree2 + k - 3)).Offset(0, 1) = WB.Sheets("Produk").Range("c" & k).Offset(0, 1)
            ThisWorkbook.Sheets("Produk").Range("B" & (NextFree2 + k - 3)).Offset(0, 2) = WB.Sheets("Produk").Range("c" & k).Offset(0, 2)
            ThisWorkbook.Sheets("Produk").Range("B" & (NextFree2 + k - 3)).Offset(0, 3) = WB.Sheets("Produk").Range("c" & k).Offset(0, 3)
            ThisWorkbook.Sheets("Produk").Range("B" & (NextFree2 + k - 3)).Offset(0, 4) = WB.Sheets("Produk").Range("c" & k).Offset(0, 4)
            ThisWorkbook.Sheets("Produk").Range("B" & (NextFree2 + k - 3)).Offset(0, 5) = WB.Sheets("Produk").Range("c" & k).Offset(0, 5)
            ThisWorkbook.Sheets("Produk").Range("B" & (NextFree2 + k - 3)).Offset(0, 6) = WB.Sheets("Produk").Range("c" & k).Offset(0, 6)
            ThisWorkbook.Sheets("Produk").Range("B" & (NextFree2 + k - 3)).Offset(0, 7) = WB.Sheets("Produk").Range("c" & k).Offset(0, 7)
            ThisWorkbook.Sheets("Produk").Range("B" & (NextFree2 + k - 3)).Offset(0, 8) = WB.Sheets("Produk").Range("c" & k).Offset(0, 8)
            ThisWorkbook.Sheets("Produk").Range("B" & (NextFree2 + k - 3)).Offset(0, 9) = WB.Sheets("Produk").Range("c" & k).Offset(0, 9)
            ThisWorkbook.Sheets("Produk").Range("B" & (NextFree2 + k - 3)).Offset(0, 10) = WB.Sheets("Produk").Range("c" & k).Offset(0, 10)
        Next k
                WB.Close savechanges:=False
            Next i
        End If
    End Sub

    any help would be appreciated
    and here is my files
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel VBA to scan a PDF for keyword and extract PDF Pages to single PDF File
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2014, 02:00 AM
  2. [SOLVED] Wanted to extract data from a file which is already an excel macro file
    By Raju Radhakrishnan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2013, 11:43 AM
  3. Multi Pages from MSN Money (StockScouter) to One Single Excel File
    By SimonChow in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-19-2013, 04:19 AM
  4. [SOLVED] Extract a few values from several .csv-files and bundle them in a single output-file
    By Jeroen606 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-14-2013, 01:05 AM
  5. How to extract Information from an Excel File and entering those data in a text file?
    By bikash.nitk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2012, 12:13 PM
  6. convert multi-sheets file to single-sheet files
    By yahata in forum Excel General
    Replies: 3
    Last Post: 04-22-2007, 06:54 AM
  7. [SOLVED] Saving multi-tab excel file created from comma delimited text file
    By Marcus Aurelius in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-19-2005, 01:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1