Looping & Consolidating
Its oK - I Have an answer -- thanks
"teresa" wrote:
Ive got workbooks in a folder,
wkb1 worksheets("proposals")
I have values 1 and 2
wkb2 worksheets("proposals")
I have values 3 and 4
The following code should loop through the worksheets
and dump all values: 1,2,3,4 in current wks.
Dumps 3 out of 4 values - the code needs slight tweaking,
help much appreciated
Sub SubGetMyData3d()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim owb As Workbook
Dim j As Long
Dim RngToCopy, Rng2ToCopy As Range
Dim intNumRows As Integer, c As Range, lngCellTotal As Long
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\My Documents\Career")
j = 1: k = 1: l = 1
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Set owb = Workbooks.Open(Filename:=objFolder.Path _
& "\" & objFile.Name)
With owb.Worksheets("Proposals")
Set RngToCopy = .Range("B1:B" _
& .Cells(.Rows.Count, "B").End(xlUp).Row)
End With
RngToCopy.EntireRow.Copy _
Destination:=Worksheets("Proposals").Cells(j, 1)
j = Worksheets("Proposals") _
.Cells(Rows.Count, "A").End(xlUp).Row + 1
intNumRows = Cells(50, "B").End(xlUp).Row
End If
Next objFile
For Each c In Worksheets("Proposals").Range("B1:B" & intNumRows)
lngCellTotal = lngCellTotal + c.Value
Next
'With Worksheets("Proposals05").Range("B" & intNumRows + 1)
' .Borders(xlEdgeLeft).Weight = xlMedium
' .Borders(xlEdgeTop).Weight = xlMedium
' .Value = lngCellTotal
'End With
End Sub
|