Ongoing Row Count

E

eric.fader

Hi:

I have a script (listed below) that takes selected data from multiple
workbooks and worksheets and pastes them into one.

How can I have the script append the additional data appended to the
end of data within my consolidated sheet?

The area in question is between each of the for next portions of the
code.

Sub CountFilesinFolder()

Dim wsk As Worksheet
Dim i As Long ' counter for files
Dim t As Long ' counter for rows
Dim j As Long ' counter for sheets
Dim FileName As String 'filename
Dim wsTarget As Worksheet 'make a pointer to our active sheet(target)
Set wsTarget = ActiveSheet


'Count the number of files within the recipe folder
With Application.FileSearch
.NewSearch
.LookIn = "C:\recipe"
.FileType = msoFileTypeExcelWorkbooks
.FileName = "*.xls"
.Execute
Files_Count = .FoundFiles.Count
'MsgBox.FoundFiles.Count


For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
For j = 1 To Worksheets.Count
Worksheets(j).Activate
wsTarget.Range("A2").Offset(t) = Range("A2") 'Item Number
'Get flower information
fVal1 = Range("I3").Value 'Set starting row number
fVal2 = Range("J3").Value 'Set number of rows
fVal3 = fVal1 + fVal2 - 1 'Set row number to use
For k = fVal1 To fVal3 'Get Flower Information
wsTarget.Range("b2").Offset(t) = Range("B" & k) 'Item Id
wsTarget.Range("c2").Offset(t) = "Flowers" 'Item type
wsTarget.Range("d2").Offset(t) = Range("A" & k) 'Qty
wsTarget.Range("e2").Offset(t) = Range("E" & k) 'Cost
wsTarget.Range("f2").Offset(t) = Range("B" & k) 'Unit of
measure
wsTarget.Range("g2").Offset(t) = Range("C" & k) 'Name
wsTarget.Range("h2").Offset(t) = Range("D" & k) 'Unit
price
wsTarget.Range("i2").Offset(t) = Range("G" & k) 'Function
of flower
Next k

'Get foliage information
folVal1 = Range("I4").Value 'Set starting row number
folVal2 = Range("J4").Value 'Set number of rows
folVal3 = folVal1 + folVal2 - 1 'Set row number to use
For l = folVal1 To folVal3 'Get Foliage Information
wsTarget.Range("b2").Offset(t) = Range("B" & l) 'Item Id
wsTarget.Range("c2").Offset(t) = "Foliage" 'Item type
wsTarget.Range("d2").Offset(t) = Range("A" & l) 'Qty
wsTarget.Range("e2").Offset(t) = Range("E" & l) 'Cost
wsTarget.Range("f2").Offset(t) = Range("B" & l) 'Unit of
measure
wsTarget.Range("g2").Offset(t) = Range("C" & l) 'Name
wsTarget.Range("h2").Offset(t) = Range("D" & l) 'Unit
price
wsTarget.Range("i2").Offset(t) = Range("G" & l) 'Function
of flower
Next l

'Get hard goods information
Oval = j + j
hgVal1 = Range("I6").Value 'Set starting row number
hgVal2 = Range("J6").Value 'Set number of rows
hgVal3 = hgVal1 + hgVal2 - 1 'Set row number to use
For m = hgVal1 To hgVal3 'Get Hard Goods Information
wsTarget.Range("b2").Offset(t) = Range("B" & m) 'Item Id
wsTarget.Range("c2").Offset(t) = "Hard Goods" 'Item type
wsTarget.Range("d2").Offset(t) = Range("A" & m) 'Qty
wsTarget.Range("e2").Offset(t) = Range("E" & m) 'Cost
wsTarget.Range("f2").Offset(t) = Range("B" & m) 'Unit of
measure
wsTarget.Range("g2").Offset(t) = Range("C" & m) 'Name
wsTarget.Range("h2").Offset(t) = Range("D" & m) 'Unit
price
wsTarget.Range("i2").Offset(t) = Range("G" & m) 'Function
of flower
Next m


'wsTarget.Range("F2").Offset(t) =
Application.ActiveWorkbook.Name
'wsTarget.Range("G2").Offset(t) = Application.ActiveSheet.Name
Application.DisplayAlerts = False
t = t + 1 ' increment row counter
Next j
ActiveWorkbook.Close
Application.DisplayAlerts = True
Next i


End With
End Sub
 
J

Joel

I modified variable T in the code below. I initialized t to 0 and then
changged the code to increment t at the end of the loop to look for the last
row of data in column A. You had
t = t + 1.

I have
t = Cells(Rows.Count, 1).End(xlUp).Row

If you want to have an extra space between the data then add 1
t = Cells(Rows.Count, 1).End(xlUp).Row + 1

Sub CountFilesinFolder()

Dim wsk As Worksheet
Dim i As Long ' counter for files
Dim t As Long ' counter for rows
Dim j As Long ' counter for sheets
Dim FileName As String 'filename
Dim wsTarget As Worksheet 'make a pointer to our active sheet(target)
Set wsTarget = ActiveSheet


'Count the number of files within the recipe folder
With Application.FileSearch
.NewSearch
.LookIn = "C:\recipe"
.FileType = msoFileTypeExcelWorkbooks
.FileName = "*.xls"
.Execute
Files_Count = .FoundFiles.Count
'MsgBox.FoundFiles.Count


t = 0
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
For j = 1 To Worksheets.Count
Worksheets(j).Activate
wsTarget.Range("A2").Offset(t) = Range("A2") 'Item Number
'Get flower information
fVal1 = Range("I3").Value 'Set starting row number
fVal2 = Range("J3").Value 'Set number of rows
fVal3 = fVal1 + fVal2 - 1 'Set row number to use
For k = fVal1 To fVal3 'Get Flower Information
wsTarget.Range("b2").Offset(t) = Range("B" & k) 'Item Id
wsTarget.Range("c2").Offset(t) = "Flowers" 'Item type
wsTarget.Range("d2").Offset(t) = Range("A" & k) 'Qty
wsTarget.Range("e2").Offset(t) = Range("E" & k) 'Cost
wsTarget.Range("f2").Offset(t) = _
Range("B" & k) 'Unit of measure
wsTarget.Range("g2").Offset(t) = Range("C" & k) 'Name
wsTarget.Range("h2").Offset(t) = _
Range("D" & k) 'Unit price
wsTarget.Range("i2").Offset(t) = _
Range("G" & k) 'Function of flower
Next k

'Get foliage information
folVal1 = Range("I4").Value 'Set starting row number
folVal2 = Range("J4").Value 'Set number of rows
folVal3 = folVal1 + folVal2 - 1 'Set row number to use
For l = folVal1 To folVal3 'Get Foliage Information
wsTarget.Range("b2").Offset(t) = Range("B" & l) 'Item Id
wsTarget.Range("c2").Offset(t) = "Foliage" 'Item type
wsTarget.Range("d2").Offset(t) = Range("A" & l) 'Qty
wsTarget.Range("e2").Offset(t) = Range("E" & l) 'Cost
wsTarget.Range("f2").Offset(t) = _
Range("B" & l) 'Unit of measure
wsTarget.Range("g2").Offset(t) = Range("C" & l) 'Name
wsTarget.Range("h2").Offset(t) = _
Range("D" & l) 'Unit price
wsTarget.Range("i2").Offset(t) = _
Range("G" & l) 'Function of flower
Next l

'Get hard goods information
Oval = j + j
hgVal1 = Range("I6").Value 'Set starting row number
hgVal2 = Range("J6").Value 'Set number of rows
hgVal3 = hgVal1 + hgVal2 - 1 'Set row number to use
For m = hgVal1 To hgVal3 'Get Hard Goods Information
wsTarget.Range("b2").Offset(t) = Range("B" & m) 'Item Id
wsTarget.Range("c2").Offset(t) = "Hard Goods" 'Item type
wsTarget.Range("d2").Offset(t) = Range("A" & m) 'Qty
wsTarget.Range("e2").Offset(t) = Range("E" & m) 'Cost
wsTarget.Range("f2").Offset(t) = _
Range("B" & m) 'Unit of measure
wsTarget.Range("g2").Offset(t) = Range("C" & m) 'Name
wsTarget.Range("h2").Offset(t) = _
Range("D" & m) 'Unit price
wsTarget.Range("i2").Offset(t) = _
Range("G" & m) 'Function of flower
Next m


'wsTarget.Range("F2").Offset(t) =
'Application.ActiveWorkbook.Name
'wsTarget.Range("G2").Offset(t) = Application.ActiveSheet.Name
Application.DisplayAlerts = False

t = Cells(Rows.Count, 1).End(xlUp).Row' increment row counter
Next j
ActiveWorkbook.Close
Application.DisplayAlerts = True
Next i


End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Runtime Error 1004 on Linest 7
Runtime Error 1004 on Linest in VBA 1
Runtime Error 1004 2
vlookup issue 2
Inaccurate count with code 6
Macro for compare between 2 workbooks and copy adjacent cells 1
Pictures not being sorted in VBA 2
Format Code - Joel 1

Top