Code to look at open workbooks rather than in a location.

[Jed Shields]

Hello all,

I've got some code that opens a number of speadsheetsand copies and pastes the data to a master list. Currently the code needs the files in a specific folder. How do I change it so it doesn't matter where they're stored, but just needs the files to be open?

Ceers,

Jed

Sub Combine()

Dim LRow As Variant
Dim SRow As Variant
Dim Partners As Range
Dim AWB As Workbook
Dim NWB As Workbook
'Application.ScreenUpdating = False

Set Partners = Sheets("All Data Headcount").Range("p2:p6")
Set AWB = ActiveWorkbook
For Each Name In Partners
    Sheets("All Data Headcount").Select

    With ActiveSheet
        LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

Workbooks.Open "H:\My Documents\Partner Management\Data Source SOW\" & Name & ".xlsx"
Set NWB = ActiveWorkbook
'Workbooks("" & AWB).Activate
Sheets("Headcount").Select
    With ActiveSheet
         SRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    ActiveSheet.Range("$A$2:$M" & SRow).SpecialCells _
        (xlCellTypeVisible).Copy
    AWB.Activate
    
    Sheets("All Data Headcount").Range("A" & LRow + 1).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False 'Doesn't add data to clipboard

NWB.Close
Next
    Columns("D:E").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("M:M").Select
    Selection.NumberFormat = "0.00%"
    Selection.NumberFormat = "0.0%"

End Sub

 

[Puertorekinsam]

Re: I need help modifying code to look at open workbooks rather than in a location.

dim OWB as Workbook
for each OWB in application.workbooks


Next ' OWB

 

[Jed Shields]

Re: I need help modifying code to look at open workbooks rather than in a location.

Thanks for the pointer but I'm struggling to add it in correctly... any ideas?

Sub Combine()
Dim LRow As Variant
Dim SRow As Variant
Dim Partners As Range
Dim AWB As Workbook
Dim NWB As Workbook
Dim OWB As Workbook

Set AWB = ActiveWorkbook

Sheets("All Data Headcount").Select
    With ActiveSheet
        LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

For Each OWB In Application.Workbooks

Set OWB = ActiveWorkbook
Sheets("Headcount").Select
With ActiveSheet
    SRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
    
    ActiveSheet.Range("$A$2:$M" & SRow).SpecialCells _
        (xlCellTypeVisible).Copy
    
AWB.Activate
Sheets("All Data Headcount").Range("A" & LRow + 1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False 'Doesn't add data to clipboard
Next OWB
    Columns("D:E").Select
    Selection.NumberFormat = "m/d/yyyy"
    
    Columns("M:M").Select
    Selection.NumberFormat = "0.00%"
    Selection.NumberFormat = "0.0%"
End Sub