Looping & Consolidating
 

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

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