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

Jed Shields

Active Member
Joined
Sep 7, 2011
Messages
281
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

Code:
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Puertorekinsam

Active Member
Joined
Oct 8, 2005
Messages
287
Re: I need help modifying code to look at open workbooks rather than in a location.

Code:
dim OWB as Workbook
for each OWB in application.workbooks


Next ' OWB
 

Jed Shields

Active Member
Joined
Sep 7, 2011
Messages
281
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?

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,132,671
Messages
5,654,655
Members
418,146
Latest member
Shnn028

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top