Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ongoing Row Count
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ongoing Row Count
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 " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ongoing question re hyperlink | Excel Discussion (Misc queries) | |||
ongoing hyperlink problem | Excel Discussion (Misc queries) | |||
Ongoing Average Calculation | Excel Discussion (Misc queries) | |||
how do i stop rows from being ongoing | Excel Discussion (Misc queries) | |||
Adding an ongoing value to an Average | Excel Worksheet Functions |