Loop through for WS names LIKE "AAA_####A" not working and throwing a runtime error 9

J

juscuz419

Board Regular
Joined
Apr 18, 2023
Messages
57
Office Version
  1. 2019
Platform
  1. Windows
I have a worksheet that can contain up to 40 worksheets with a name LIKE "AAA_####A" where A can be any letter and # can be any number. I am trying to loop through each one in turn, extract data, paste it on another worksheet then go to the next worksheet. The code below is where I am, but it does not work. It throws an error code on the row in red font that the subscript is out of range. If I replace (sourcewsname) with an actual name of one of the sheets (ex MRC_4699A) the code works. Why wont the loop through work?

Sub CopyValuesBetweenWorksheets()

Dim sourceWS As Worksheet
Dim targetWS As Worksheet
Dim ws As Worksheet
Dim WSName As String
Dim wsDailySummary As Worksheet
Dim sourceWSName As String

For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "???_?????" Then
sourceWSName = ws.Name
Exit For
End If

Set sourceWS = ThisWorkbook.Worksheets(sourceWSName)
Set targetWS = ThisWorkbook.Worksheets("Daily Summary")

'My code that works

Next ws
End Sub
 
Sort by date Sort by votes
1706581831789.png


They are created by another macro that uses a template. When i look at the VBA Project they have the name(s) template1SXC_4698A, template2JCP_4698D, etc. When I run a list WS names macro, I just get SXC_4698A, etc. AT any rate, I have tried all of those names and patterns in the code. This should not be this hard!
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
I modified the code to check the 4th position of the sheet name, if it is equal to "_" then it processes the sheet.

VBA Code: 
Sub CopyValuesBetweenWorksheets()
  Dim sourceWS As Worksheet
  Dim targetWS As Worksheet
  Dim ws As Worksheet
  Dim WSName As String
  Dim wsDailySummary As Worksheet
  Dim sourceWSName As String
  Dim datevalueDailySummary As Date
  Dim datevaluesourcews As Date
  Dim lastRow As Long
  Dim i As Long

  For Each ws In ThisWorkbook.Worksheets
    If Mid(ws.Name, 4, 1) = "_" Then
      sourceWSName = ws.Name
      Set sourceWS = ThisWorkbook.Worksheets(sourceWSName)
      Set targetWS = ThisWorkbook.Worksheets("Daily Summary")
    
      ' Set references to the worksheets
      Set wsDailySummary = ThisWorkbook.Worksheets("Daily Summary")
      'Set sourceWS = ThisWorkbook.Worksheets(sourceWSName)
      Set targetWS = ThisWorkbook.Worksheets("Daily Summary")
      
      ' Get the dates from the specified cells
      datevalueDailySummary = wsDailySummary.Range("N14").Value
      datevaluesourcews = sourceWS.Range("C18").Value
      
      ' Compare the dates
      If datevalueDailySummary = datevaluesourcews Then
        ' Fill in the first row on the Daily Summary Sheet
        ' Copy values from specific cells in the source sheet to specific cells in the target sheet
        
        targetWS.Range("B21").Value = sourceWS.Range("f4").Value
        targetWS.Range("c21").Value = sourceWS.Range("m4").Value
        targetWS.Range("d21").Value = sourceWS.Range("q1").Value
        
        ' Find the last row with data in the source worksheet
        lastRow = 32 'sourcews.Cells(sourcews.Rows.Count, "C").End(xlUp).Row
        
        ' Loop through each row in the source worksheet
        For i = 18 To lastRow
          ' Check if the date value in column C matches datevalueDailySummary
          If sourceWS.Cells(i, 3).Value = datevalueDailySummary Then
            ' Copy relevant data to the next available row in the target worksheet
            targetWS.Cells(targetWS.Rows.Count, "F").End(xlUp).Offset(1, 0).Value = sourceWS.Cells(i, 5).Value
            targetWS.Cells(targetWS.Rows.Count, "G").End(xlUp).Offset(1, 0).Value = sourceWS.Cells(i, 6).Value
            targetWS.Cells(targetWS.Rows.Count, "H").End(xlUp).Offset(1, 0).Value = sourceWS.Cells(i, 7).Value
            targetWS.Cells(targetWS.Rows.Count, "I").End(xlUp).Offset(1, 0).Value = sourceWS.Cells(i, 8).Value
            
            ' Check other conditions and update targetws.Range("E") accordingly
            If sourceWS.Range("AD3").Value Or sourceWS.Range("AD4").Value = True Then
              targetWS.Cells(targetWS.Rows.Count, "E").End(xlUp).Offset(1, 0).Value = "Pickup/SUV"
            End If
            
            targetWS.Cells(targetWS.Rows.Count, "K").End(xlUp).Offset(1, 0).Value = sourceWS.Cells(i, 21).Value
            targetWS.Cells(targetWS.Rows.Count, "L").End(xlUp).Offset(1, 0).Value = sourceWS.Cells(i, 22).Value
          End If
        Next i
      End If
      
    End If
  Next ws
End Sub

Notes to improve the code:
1. All variable declarations at the beginning of the code
2. Observe the indentation for each For-Next, If-End if, etc. structure. That way the code is more understandable.
3. Finally, check the following to put the code inside the code tags.
In future please use code tags when posting code.​
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.​

😇
 
Upvote 0
Wow. This does the trick. I still have one mistake that I'll work on trying to fix, I think I need to put another loo[p in to put

targetWS.Range("B21").Value = sourceWS.Range("f4").Value
targetWS.Range("c21").Value = sourceWS.Range("m4").Value
targetWS.Range("d21").Value = sourceWS.Range("q1").Value

in the appropriate places on the summary page for EACH of the timesheets.

I'll also read the How to post link above. Really appreciate the help.
 
Upvote 0
I reviewed your code and removed some lines that are not necessary. Try the following:

VBA Code: 
Sub CopyValuesBetweenWorksheets()
  Dim sourceWS As Worksheet, targetWS As Worksheet, ws As Worksheet
  Dim WSName As String, sourceWSName As String
  Dim datevalueDailySummary As Date, datevaluesourcews As Date
  Dim lastRow As Long, lr As Long, i As Long

  For Each ws In ThisWorkbook.Worksheets
    If Mid(ws.Name, 4, 1) = "_" Then
      sourceWSName = ws.Name
      Set sourceWS = ThisWorkbook.Worksheets(sourceWSName)
      Set targetWS = ThisWorkbook.Worksheets("Daily Summary")
          
      ' Get the dates from the specified cells
      datevalueDailySummary = targetWS.Range("N14").Value
      datevaluesourcews = sourceWS.Range("C18").Value
      
      ' Compare the dates
      If datevalueDailySummary = datevaluesourcews Then
        ' Fill in the first row on the Daily Summary Sheet
        ' Copy values from specific cells in the source sheet to specific cells in the target sheet
        
        targetWS.Range("B21").Value = sourceWS.Range("f4").Value
        targetWS.Range("c21").Value = sourceWS.Range("m4").Value
        targetWS.Range("d21").Value = sourceWS.Range("q1").Value
        
        ' Find the last row with data in the source worksheet
        lastRow = 32 'sourcews.Cells(sourcews.Rows.Count, "C").End(xlUp).Row
        
        ' Loop through each row in the source worksheet
        For i = 18 To lastRow
          ' Check if the date value in column C matches datevalueDailySummary
          If sourceWS.Cells(i, 3).Value = datevalueDailySummary Then
            ' Copy relevant data to the next available row in the target worksheet
            lr = targetWS.Cells(targetWS.Rows.Count, "F").End(xlUp).Row + 1
            targetWS.Cells(lr, "F").Value = sourceWS.Cells(i, 5).Value
            targetWS.Cells(lr, "G").Value = sourceWS.Cells(i, 6).Value
            targetWS.Cells(lr, "H").Value = sourceWS.Cells(i, 7).Value
            targetWS.Cells(lr, "I").Value = sourceWS.Cells(i, 8).Value
            ' Check other conditions and update targetws.Range("E") accordingly
            If sourceWS.Range("AD3").Value Or sourceWS.Range("AD4").Value = True Then
              targetWS.Cells(lr, "E").Value = "Pickup/SUV"
            End If
            targetWS.Cells(lr, "K").Value = sourceWS.Cells(i, 21).Value
            targetWS.Cells(lr, "L").Value = sourceWS.Cells(i, 22).Value
          End If
        Next i
      End If
      
    End If
  Next ws
End Sub

🧙‍♂️
 
Upvote 0
On closer inspection this morning when I was not so cross-eyed....
The cleaned up code in post #14 only puts the last row of the last timesheet on the summary sheet.
The code in post# 12, puts the vehicle, work status, start, stop, hours, meal count, and lodging fields from each timesheet on the summary sheet correctly but puts the first name, last name, and employee ID from the last timesheet on the first row (row 21) of the summary sheet. I did have to add a DIM and SET back to the code to get it to work. The "fix" I reference in post #13 probably fixes this but my efforts thus far are not getting me there. The code I have is below (hopefully it is in the pretty format that the "post VBA code tutorial explains". I have added a bit of descriptive comments to help in identifying things. I swear I feel like such an 80 year old dummy but it seems learning a computer language is harder than learning a foreign language. I eill follow this post with a post with a picture of how I would like the summary page to look for the first timesheet.

VBA Code: 
[CODE=rich]
[CODE=vba]Sub CopyValuesBetweenWorksheets()
  Dim sourceWS As Worksheet, targetWS As Worksheet, ws As Worksheet, wsDailySummary As Worksheet
  Dim WSName As String, sourceWSName As String
  Dim datevalueDailySummary As Date, datevaluesourcews As Date
  Dim lastRow As Long, lr As Long, i As Long

  For Each ws In ThisWorkbook.Worksheets
    If Mid(ws.Name, 4, 1) = "_" Then
      sourceWSName = ws.Name
      ' Set references to the worksheets
      Set wsDailySummary = ThisWorkbook.Worksheets("Daily Summary")
      Set sourceWS = ThisWorkbook.Worksheets(sourceWSName)
      Set targetWS = ThisWorkbook.Worksheets("Daily Summary")
   
      ' Get the dates from the Daily Summary sheet and the first tow with time in it of the first timesheet
      datevalueDailySummary = wsDailySummary.Range("N14").Value
      datevaluesourcews = sourceWS.Range("C18").Value
     
      ' Compare the dates
      If datevalueDailySummary = datevaluesourcews Then
        ' Fill in the first row on the Daily Summary Sheet with first and last names and the employee ID
        ' Copy values from specific cells in the source sheet to specific cells in the target sheet
       
            ' These are the Daily Summary Emplyee identifier fields
                targetWS.Range("B21").Value = sourceWS.Range("f4").Value 'First Name
                targetWS.Range("c21").Value = sourceWS.Range("m4").Value 'Last Name
                targetWS.Range("d21").Value = sourceWS.Range("q1").Value 'Employee IDDaily Summary sheet and the first r
               
                ' Find the last row with data in the source worksheet
                lastRow = 32
               
                ' Loop through each row in the source worksheet
                For i = 18 To lastRow
                  ' Check if the date value in column C matches datevalueDailySummary
                  If sourceWS.Cells(i, 3).Value = datevalueDailySummary Then
                    ' Copy relevant data to the next available row in the target worksheet
                    targetWS.Cells(targetWS.Rows.Count, "F").End(xlUp).Offset(1, 0).Value = sourceWS.Cells(i, 5).Value 'Status
                    targetWS.Cells(targetWS.Rows.Count, "G").End(xlUp).Offset(1, 0).Value = sourceWS.Cells(i, 6).Value 'Start Time
                    targetWS.Cells(targetWS.Rows.Count, "H").End(xlUp).Offset(1, 0).Value = sourceWS.Cells(i, 7).Value 'End Time
                    targetWS.Cells(targetWS.Rows.Count, "I").End(xlUp).Offset(1, 0).Value = sourceWS.Cells(i, 8).Value 'Hours
                   
                    ' Checks to see if the employee is providing a vehicle and update targetws.Range("E") accordingly
                    If sourceWS.Range("AD3").Value Or sourceWS.Range("AD4").Value = True Then
                      targetWS.Cells(targetWS.Rows.Count, "E").End(xlUp).Offset(1, 0).Value = "Pickup/SUV"
                    End If
                   
                    targetWS.Cells(targetWS.Rows.Count, "K").End(xlUp).Offset(1, 0).Value = sourceWS.Cells(i, 21).Value '# of Meals
                    targetWS.Cells(targetWS.Rows.Count, "L").End(xlUp).Offset(1, 0).Value = sourceWS.Cells(i, 22).Value 'Lodging
                  End If
                Next i
      End If
    End If
  Next ws
End Sub
Rich (BB code):
[/CODE][/CODE]
 
Upvote 0
Would like it to look like this for the first timesheet and then the next sheet would go in the row below, and so on, and so forth

1706627275560.png
 
Upvote 0
Some notes:

1. It is not necessary to set the same sheet to 2 different objects, in fact it is confusing.
Set wsDailySummary = ThisWorkbook.Worksheets("Daily Summary")
Set targetWS = ThisWorkbook.Worksheets("Daily Summary")

2. Your code compares the first date of row 18 against the date of the source sheet, that means that if that date is not equal, then it no longer checks the dates of rows 19 to 32.

Try the following code:
VBA Code: 
Sub CopyValuesBetweenWorksheets()
  Dim sourceWS As Worksheet, targetWS As Worksheet, ws As Worksheet
  Dim datevalueDailySummary As Date, datevaluesourcews As Date
  Dim nRow As Long, i As Long

  ' Set references to the worksheets
  Set targetWS = ThisWorkbook.Worksheets("Daily Summary")

  nRow = 21                                             ' Start row in Daily Summary sheet
  datevalueDailySummary = targetWS.Range("N14").Value   ' Get the date from the Daily Summary sheet
  
  For Each ws In ThisWorkbook.Worksheets
    If Mid(ws.Name, 4, 1) = "_" Then
      Set sourceWS = ws
      datevaluesourcews = sourceWS.Range("C18").Value   ' First time in timesheet
     
      ' Compare the dates
      If datevalueDailySummary = datevaluesourcews Then
        ' Fill in the first row on the Daily Summary Sheet with first and last names and the employee ID
        ' Copy values from specific cells in the source sheet to specific cells in the target sheet
        
        ' These are the Daily Summary Emplyee identifier fields
        targetWS.Range("B" & nRow).Value = sourceWS.Range("F4").Value 'First Name
        targetWS.Range("C" & nRow).Value = sourceWS.Range("M4").Value 'Last Name
        targetWS.Range("D" & nRow).Value = sourceWS.Range("Q1").Value 'Employee IDDaily Summary sheet and the first r
        
        ' Loop through each row in the source worksheet
        For i = 18 To 32
          ' Check if the date value in column C matches datevalueDailySummary
          If datevalueDailySummary = sourceWS.Range("C" & i).Value Then
            ' Copy relevant data to the next available row in the target worksheet
            ' Checks to see if the employee is providing a vehicle and update targetws.Range("E") accordingly
            If sourceWS.Range("AD3").Value Or sourceWS.Range("AD4").Value = True Then
              targetWS.Range("E" & nRow).Value = "Pickup/SUV"
            End If
            targetWS.Range("F" & nRow).Value = sourceWS.Cells(i, 5).Value   'Status
            targetWS.Range("G" & nRow).Value = sourceWS.Cells(i, 6).Value   'Start Time
            targetWS.Range("H" & nRow).Value = sourceWS.Cells(i, 7).Value   'End Time
            targetWS.Range("I" & nRow).Value = sourceWS.Cells(i, 8).Value   'Hours
            targetWS.Range("K" & nRow).Value = sourceWS.Cells(i, 21).Value  '# of Meals
            targetWS.Range("L" & nRow).Value = sourceWS.Cells(i, 22).Value  'Lodging
            
            nRow = nRow + 1
          End If
        Next i
      End If
    End If
  Next ws
End Sub

😇
 
Upvote 0
Works like a charm. You be da MAN. Saves me about two hours a day on a storm doing this manually for what can be up to four teams of 30 or 40 people. I tested it with 40 timesheets and it rocks. Cannot thank you enough
 
Upvote 0
I know you are probably tired of me and bored with this, but if I fill in a whole time sheet and try to get a summary for any date on the source sheets(s) I get nothing because I point to C18 specifically. Need to be able to specify a date on the from the summary sheet (N14) and be able to produce the summary sheet for any rows on any of the source sheets that match that date. Does that make sense?
 
Upvote 0
juscuz419 said:
I get nothing because I point to C18 specifically
Click to expand...
I had already noticed that, I mentioned it in post #17, because that was your logic from the beginning of the post.
DanteAmor said:
2. Your code compares the first date of row 18 against the date of the source sheet, that means that if that date is not equal, then it no longer checks the dates of rows 19 to 32.
Click to expand...

Try the following:

VBA Code: 
Sub CopyValuesBetweenWorksheets()
  Dim sourceWS As Worksheet, targetWS As Worksheet, ws As Worksheet
  Dim datevalueDailySummary As Date
  Dim nRow As Long, i As Long
  Dim new_sheet As Boolean

  ' Set references to the worksheets
  Set targetWS = ThisWorkbook.Worksheets("Daily Summary")

  nRow = 21                                             ' Start row in Daily Summary sheet
  datevalueDailySummary = targetWS.Range("N14").Value   ' Get the date from the Daily Summary sheet
  
  For Each ws In ThisWorkbook.Worksheets
    If Mid(ws.Name, 4, 1) = "_" Then
      Set sourceWS = ws
      new_sheet = True
        
      ' Loop through each row in the source worksheet
      For i = 18 To 32
        
        If new_sheet = True Then
          targetWS.Range("B" & nRow).Value = sourceWS.Range("F4").Value 'First Name
          targetWS.Range("C" & nRow).Value = sourceWS.Range("M4").Value 'Last Name
          targetWS.Range("D" & nRow).Value = sourceWS.Range("Q1").Value 'Employee IDDaily Summary sheet and the first r
          new_sheet = False
        End If
        
        ' Check if the date value in column C matches datevalueDailySummary
        If datevalueDailySummary = sourceWS.Range("C" & i).Value Then
          ' Copy relevant data to the next available row in the target worksheet
          ' Checks to see if the employee is providing a vehicle and update targetws.Range("E") accordingly
          If sourceWS.Range("AD3").Value Or sourceWS.Range("AD4").Value = True Then
            targetWS.Range("E" & nRow).Value = "Pickup/SUV"
          End If
          targetWS.Range("F" & nRow).Value = sourceWS.Cells(i, 5).Value   'Status
          targetWS.Range("G" & nRow).Value = sourceWS.Cells(i, 6).Value   'Start Time
          targetWS.Range("H" & nRow).Value = sourceWS.Cells(i, 7).Value   'End Time
          targetWS.Range("I" & nRow).Value = sourceWS.Cells(i, 8).Value   'Hours
          targetWS.Range("K" & nRow).Value = sourceWS.Cells(i, 21).Value  '# of Meals
          targetWS.Range("L" & nRow).Value = sourceWS.Cells(i, 22).Value  'Lodging
          
          nRow = nRow + 1
        End If
      Next i
      
    End If
  Next ws
End Sub

:cool:
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

N
  • Solved
VBA to select last 2 sheets created that are named in mm-dd-yy format
Replies
4
Views
234
Peter_SSs
Peter_SSs
R
  • Question Question
Runtime Error 91
2
Replies
15
Views
668
rtr1811
R
J
  • Question Question
Change specific values (not all) in a VBA sub
2
Replies
16
Views
786
juscuz419
J
C
  • Question Question
username access to different worksheet VBA
Replies
4
Views
307
cilantro00
C
L
  • Question Question
VBA - Run time error '9': subscript out of range
Replies
3
Views
271
RoryA
RoryA

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top