G
gjfeng
As you can see the code below, everything is the same, except
template(x).xls
where x is 1,2,3 and so on.
Can someone help me to modify this code so it'll look through every
template(x).xls workbook in the same folder?
as an additonal question, when I use the below below on a new
worksheet, it starts at line 2, is there a way to start at line1 or
line 5?
Private Sub CommandButton1_Click()
Dim wb As Workbook, ws As Worksheet, i As Integer
Set wb = Workbooks.Open(ThisWorkbook.Path & "\template1.xls")
Set ws = wb.Sheets("Sheet1")
Dim intRow As Integer
intRow = 1
Do While ws.Cells(4 + intRow, 1).Value <> ""
i = i + 1
proid = ws.Cells(4 + intRow, 1).Value
pro = ws.Cells(4 + intRow, 2).Value
uom = ws.Cells(4 + intRow, 3).Value
qty = ws.Cells(4 + intRow, 4).Value
ThisWorkbook.Sheets("GrandTotal").Range("a" & 1 +
i).End(xlUp).Offset(1, 0).Resize(, 4) = Array(proid, pro, uom, qty)
intRow = intRow + 1
Loop
wb.Close savechanges:=False
Set wb = Workbooks.Open(ThisWorkbook.Path & "\template2.xls")
Set ws = wb.Sheets("Sheet1")
intRow = 1
Do While ws.Cells(4 + intRow, 1).Value <> ""
i = i + 1
proid = ws.Cells(4 + intRow, 1).Value
pro = ws.Cells(4 + intRow, 2).Value
uom = ws.Cells(4 + intRow, 3).Value
ThisWorkbook.Sheets("GrandTotal").Range("a" & 1 +
i).End(xlUp).Offset(1, 0).Resize(, 4) = Array(proid, pro, uom, qty)
intRow = intRow + 1
Loop
wb.Close savechanges:=False
Set wb = Workbooks.Open(ThisWorkbook.Path & "\template3.xls")
Set ws = wb.Sheets("Sheet1")
intRow = 1
Do While ws.Cells(4 + intRow, 1).Value <> ""
i = i + 1
proid = ws.Cells(4 + intRow, 1).Value
pro = ws.Cells(4 + intRow, 2).Value
uom = ws.Cells(4 + intRow, 3).Value
ThisWorkbook.Sheets("GrandTotal").Range("a" & 1 +
i).End(xlUp).Offset(1, 0).Resize(, 4) = Array(proid, pro, uom, qty)
intRow = intRow + 1
Loop
wb.Close savechanges:=False
End Sub
template(x).xls
where x is 1,2,3 and so on.
Can someone help me to modify this code so it'll look through every
template(x).xls workbook in the same folder?
as an additonal question, when I use the below below on a new
worksheet, it starts at line 2, is there a way to start at line1 or
line 5?
Private Sub CommandButton1_Click()
Dim wb As Workbook, ws As Worksheet, i As Integer
Set wb = Workbooks.Open(ThisWorkbook.Path & "\template1.xls")
Set ws = wb.Sheets("Sheet1")
Dim intRow As Integer
intRow = 1
Do While ws.Cells(4 + intRow, 1).Value <> ""
i = i + 1
proid = ws.Cells(4 + intRow, 1).Value
pro = ws.Cells(4 + intRow, 2).Value
uom = ws.Cells(4 + intRow, 3).Value
qty = ws.Cells(4 + intRow, 4).Value
ThisWorkbook.Sheets("GrandTotal").Range("a" & 1 +
i).End(xlUp).Offset(1, 0).Resize(, 4) = Array(proid, pro, uom, qty)
intRow = intRow + 1
Loop
wb.Close savechanges:=False
Set wb = Workbooks.Open(ThisWorkbook.Path & "\template2.xls")
Set ws = wb.Sheets("Sheet1")
intRow = 1
Do While ws.Cells(4 + intRow, 1).Value <> ""
i = i + 1
proid = ws.Cells(4 + intRow, 1).Value
pro = ws.Cells(4 + intRow, 2).Value
uom = ws.Cells(4 + intRow, 3).Value
ThisWorkbook.Sheets("GrandTotal").Range("a" & 1 +
i).End(xlUp).Offset(1, 0).Resize(, 4) = Array(proid, pro, uom, qty)
intRow = intRow + 1
Loop
wb.Close savechanges:=False
Set wb = Workbooks.Open(ThisWorkbook.Path & "\template3.xls")
Set ws = wb.Sheets("Sheet1")
intRow = 1
Do While ws.Cells(4 + intRow, 1).Value <> ""
i = i + 1
proid = ws.Cells(4 + intRow, 1).Value
pro = ws.Cells(4 + intRow, 2).Value
uom = ws.Cells(4 + intRow, 3).Value
ThisWorkbook.Sheets("GrandTotal").Range("a" & 1 +
i).End(xlUp).Offset(1, 0).Resize(, 4) = Array(proid, pro, uom, qty)
intRow = intRow + 1
Loop
wb.Close savechanges:=False
End Sub