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
Bookmarks