Macro is ignoring a line of data when not used on original spreadsheet

[rvelt1213]

I have 7 macros that I have combined to accomplish what I am trying to do. When ran on the original spreadsheet they were created on, it works flawlessly. When I copied the macros into the new spreadsheet for this month (it is updated quantities of different products for an order.), there were some problems. Header column is different products to be ordered, rows begin with a location name followed by order quantities of said products. Here is a quick synopsis on what happens:

macro 1: creates a new tab to be named each location name in column A
macro 2: copies the top 2 rows of product information and pastes them onto each new tab
macro 3: takes each individual location and copies just that row onto the corresponding tab
* this is where there is a problem. The first location - named DC's - will not copy onto its tab. Even if I adjust which row this location is in, it still will not copy. There are 90 locations total and this is the only one. This location copied fine on the original spreadsheet.

This is my code for macro 3:

Sub Run_Third()
Application.ScreenUpdating = False
Dim rs As Worksheet
Set rs = Worksheets("DATA")
For r = 1 To rs.Range("A" & Rows.Count).End(xlUp).Row
wsName = rs.Cells(r, "A")
If WorksheetFunction.IsErr(Evaluate("'" & wsName & "'!A1")) = "False" Then
wr = Worksheets(wsName).Range("A" & Rows.Count).End(xlUp).Row + 2
rs.Rows(r).Copy Destination:=Worksheets(wsName).Range("A" & wr)
End If
Next r
Application.ScreenUpdating = True
End Sub

 

[bastoe]

So if it works in the original, did you combine the code, or keep them all separate like they were? If combined, maybe that broke em'
You can also link the macro's together by calling them at the end of each one.
In the original was it a button action on the DATA page?

'//call the next macro//
Call Run_Third

 

[Alex Blakenburg]

The first location - named DC's - will not copy onto its tab

You IsErr function If WorksheetFunction.IsErr(Evaluate("'" & wsName & "'!A1")) = "False" Then is not happy about the apostrophe ('), how important is that name to you ?

If you try an type it into a formula you get:
(notice the double up on the apostrophe)

='DC''s'!A1

 

[rvelt1213]

So if it works in the original, did you combine the code, or keep them all separate like they were? If combined, maybe that broke em'
You can also link the macro's together by calling them at the end of each one.
In the original was it a button action on the DATA page?

'//call the next macro//
Call Run_Third

I have a "MASTER" code that calls them all in order. I literally copy/pasted all individual and Master codes from the original. It does appear that something "broke" even though I didn't change anything. I'm rather new to VBA's and have learned a lot on this adventure so far!

 

[rvelt1213]

You IsErr function If WorksheetFunction.IsErr(Evaluate("'" & wsName & "'!A1")) = "False" Then is not happy about the apostrophe ('), how important is that name to you ?

If you try an type it into a formula you get:
(notice the double up on the apostrophe)

='DC''s'!A1

I tried removing the apostrophe and it worked perfectly! Macros sure are particular... I am trying very hard to learn to "read" the code, but I could not figure this one out. Thank you so much for your input!!

 

[bastoe]

Glad that Alex caught that, I was under the impression DC's was also in the original book and had worked lol.

 

[rvelt1213]

Glad that Alex caught that, I was under the impression DC's was also in the original book and had worked lol.

I just checked the original and it is DCs in the original without the apostrophe... Is there a way to write the code to recognize it, without having to specifically call out the name? This code runs for approximately 90 rows.

 

[Alex Blakenburg]

Is there a way to write the code to recognize it, without having to specifically call out the name? This code runs for approximately 90 rows.

You can loop through all the sheets and then Exclude the one that you do not want to update.
Here the first case statement is the list of Sheet names that are not to be included in our 90+ sheets to update.

Sub LoopAndExclude()
    Dim sht As Worksheet
    
    For Each sht In Sheets
        Select Case sht.Name
            ' List sheets to Exclude
            Case "Sheet1", "Sheet2", "Sheet3"
                ' Do nothing
        
            Case Else
                ' Put code to update the other sheets here
                sht.Range("A1").Value = "Updated"
                
        End Select
    Next sht
End Sub