Trying to use a with statement to open a specific page

Question

I've tried looking for this everywhere and been unable to find it anywhere.

I have a code where I use a with statement to loop through data, my question is how to loop through while looking only at a specific page called "Report" it's always the first sheet in the worksheet. Here is the coding I have it is not working.

Set TargetFiles = Application.FileDialog(msoFileDialogOpen)
With TargetFiles
.Application.Worksheets(1).Activate
.AllowMultiSelect = True
.Title = "Multi-select target data files:"
.ButtonName = ""
.Filters.Clear
.Filters.Add ".xlsm files", "*.xlsm"
.Show
End With

The part I put in

 .Application.Worksheets(1).Activate

isn't doing what I want. Basically this is part of a bigger set of code that loops through a user selected range and copies and pastes to a new workbook, that only works if they were all saved on the correct sheet. I learned the overall coding from this question thread: Copying worksheets from multiple workbooks into current workbook

So there were 2 options as answers (I upvoted both) and both work but I'd like to combine parts of each. One the user picks the sheets but for it to work they all have to be on the correct sheet, on the other you have to have the files saved to a specific folder. I'd like to have the user select option for files but still have them combined based off a specific sheet name "Report" or location like worksheets(1).

Can anyone help with that?

The file that is selected will not be active until after the dialog is gone. You will probably have to loop through workbooks setting the activeheet. — MatthewD, Sep 09 '15 at 19:43
Are these workbooks already open? The `msoFileDialogOpen` provides a list of names; it does NOT actually open the workbooks into the current application environment. — , Sep 09 '15 at 19:53

MatthewD · Answer 1 · 2015-09-09T19:56:32.330

After you have your workbooks open, loop through each workbook setting the active ws.

Public Sub LoopOpenWorkbooks()
    Dim wb As Workbook
    Dim ws As Excel.Worksheet

    'Loop though each workbook
    For Each wb In Application.Workbooks

        'Activate the workbook
        wb.Activate

        'Set the worksheet and activate it.
        Set ws = ActiveWorkbook.Sheets("Report")
        ws.Activate

        'Now here you can loop through a user selected range and copies and pastes to a new workbook

    Next wb

End Sub

Or you can set the worksheet by index.

Set ws = Application.Worksheets(1)

score 0 · Answer 2 · answered Sep 09 '15 at 19:59

You seem to want to select multiple workbooks and work on the first worksheet in each one. The msoFileDialogOpen provides a list of names; it does NOT actually open the workbooks into the current application environment.

Dim f As Long, TargetFiles As Object
Set TargetFiles = Application.FileDialog(msoFileDialogOpen)
With TargetFiles
    .AllowMultiSelect = True
    .Title = "Multi-select target data files:"
    .ButtonName = ""
    .Filters.Clear
    .Filters.Add ".xlsm files", "*.xlsm"
    .Show

    For f = 1 To .SelectedItems.Count
        Debug.Print .SelectedItems(f)
        With Workbooks.Open(.SelectedItems(f))
            With .Worksheets(1)
                'work with the first worksheet in the open workbook
            End With
            .Close
        End With
    Next f

End With

See With ... End With statement for more on using it for the parentage level(s) of the workbook/worksheet/cell range so you can avoid the Worksheet.Activate method.

Trying to use a with statement to open a specific page

2 Answers2