Refresh Open Works and Copy to New Workbooks 2

[briesen]

I'd like to setup a macro so that Excel will refresh the query in each sheet in an open workbook and then copy everything to a new blank workbook. With that, I'd like it to be able to do this for all open workbooks.

Here's the code I currently have, but all it does is copy the first workbook several times for the amount of other workbooks that are open.

Sub ASInvRefresh()
 
    Dim qt As QueryTable
    Dim ws As Worksheet
    Dim wb As Workbook
    
    For Each wb In Workbooks
        For Each ws In wb.Worksheets
        If StrComp(wb.name, "personal.xls", vbTextCompare)          <> 0 Then   'skip the personal wb
        For Each qt In ws.QueryTables
                qt.Refresh False
                Next qt
                ws.Cells.EntireColumn.AutoFit
            End If
        Next ws
        Worksheets.Copy
        ActiveWorkbook.Colors = wb.Colors
    Next wb
 
End Sub

 

[PHV]

Replace this:
Worksheets.Copy
with this:
wb.Worksheets.Copy

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

1. your personal.xls test is not at the correct loop level

2. reference the worksheets.copy to the wb that you are working on...

    For Each wb In Workbooks
        If StrComp(wb.Name, "personal.xls", vbTextCompare) <> 0 Then            'skip the personal wb
            For Each ws In wb.Worksheets
                For Each qt In ws.QueryTables
                    qt.Refresh False
                Next qt
                ws.Cells.EntireColumn.AutoFit
            Next ws
            wb.Worksheets.Copy
            ActiveWorkbook.Colors = wb.Colors
        End If
    Next wb

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]