Macro Not Working as Expected 1

[shelby55]

Hello

With a lot of help from Skip, the following macro was created to extract data from multiple worksheets to consolidate into one worksheet(Master_NewA):

Sub Consolidate_NewA()
'this assumes
' Master sheet for combining data from all other sheets
' HEADINGS in Master in Row 1 starting in column A
' Mapping sheet with NAMED RANGES
'  Source & MasterCOL
    Dim ws As Worksheet, r As Range, wsMSTR As Worksheet, lRow As Long
    
    Set wsMSTR = Sheets("Master_NewA")
    
    For Each ws In Worksheets
        With ws
            Select Case .Name
                Case "Master_NewA", "Mapping_NewA"
                Case Else
                    lRow = wsMSTR.[A1].CurrentRegion.Rows.Count + 1
                    For Each r In [SourceNewA]
                        wsMSTR.Cells(lRow, r.Offset(0, 2).Value) = .Range(r.Value)
                    Next
            End Select
        End With
    Next
End Sub

I have named values for MasterNewA which is the column heading on the Master_NewA worksheet that the data will be copied to and SourceNewA which is where the data sits within the singular worksheets.

The code is working except for the fact that on Master_NewA, a field of instto is mapping to A2 of Master_NewA and then all other worksheets and their the data are copying as expected starting at A3. Even the initial record still has instto showing up where it should be in the line of data. If I remove the instto value in the abstract of the "many worksheets" (the first one) then the instto value still shows up in Master_NewA so it's like it's hard coded somewhere.

In the SourceNewA, instto is mapping from J21 of the many worksheets and mapping to the InstTo column (M) of Master_NewA.

Any help greatly appreciated - thanks.

 

[shelby55]

Got it, thanks Skip, works great now.

Just as an FYI, I thought I did "step thru" the data but didn't see where this was occurring.

 

[SkipVought]

It was "perfectly clear." ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Another technique that you could have used: FIND > within WORKBOOK--FIND ALL.

That would show you exactly where that value is located in the workbook



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Good to know, thanks Skip!!