Results 1 to 4 of 4

Thread: Copying from only select worksheets within multiple workbooks
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copying from only select worksheets within multiple workbooks

    Hi,

    I've got some code that is looping through multiple workbooks (with multiple worksheets) to copy data however it's pulling ALL the worksheets within these workbooks and i'd like to limit it the copying to specific ones. Is there a way to list the worksheets that i only want the code to copy from? (similar to SQL where you would use the IN ('XYZ','ABC') code).

    Do Until Value = ""
    If Value = "." Or Value = ".." Then
    Else
    If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then
    On Error Resume Next
    Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz"
    If Err.Number > 0 Then
    Else
    On Error GoTo 0
    For Each sht In ActiveWorkbook.Worksheets

    'If sht.Range("D37:J52") <> "" Then
    Lrow = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
    sht.Range("D37:J52").Copy Destination:=WS.Range("A" & Lrow)
    'End If

    Next sht

    End If
    Workbooks(Value).Close False
    On Error GoTo 0
    End If
    End If
    Value = Dir
    Loop

    Thanks!

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,465
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Copying from only select worksheets within multiple workbooks

    You can adapt this to limit which sheets your code will act upon;
    Code:
    Sub OnlyActOnCertainSheetsInWb()
    Dim CertainSheets As Variant, Sht As Worksheet
    CertainSheets = Array("Sheet1", "Sheet2", "Sheet4") 'Change sheet names to suit
    For Each Sht In Sheets
        For i = LBound(CertainSheets) To UBound(CertainSheets)
            If Sht.Name = CertainSheets(i) Then
                'do something to this sheet
            End If
        Next i
    Next Sht
    
    
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying from only select worksheets within multiple workbooks

    Thank you JoeMo. Worked Perfectly!

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,465
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Copying from only select worksheets within multiple workbooks

    Quote Originally Posted by liketohear View Post
    Thank you JoeMo. Worked Perfectly!
    You are welcome - thanks for the reply.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •