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.

 

[SkipVought]

hi S,

Really do not understand. The values in [SourceNewA] in EVERY SHEET other than "Master_NewA", "Mapping_NewA" is placed in Column C on sheet Master_NewA. I don't know anything about instto???

I do see one important thing that I would add in this portion of your code...

                    lRow = wsMSTR.[A1].CurrentRegion.Rows.Count + 1
                    For Each r In [SourceNewA]
                        wsMSTR.Cells(lRow, r.Offset(0, 2).Value) = .Range(r.Value)
                        [highlight][b]lRow = lRow + 1[/b][/highlight]
                    Next

Skip,

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

 

[shelby55]

Hi Skip

What I expected the macro to do was copy the data from each singular worksheet (called abstracts) to one sheet:
Abstract# ChartNo Coder...etc.
1 12345 09
2 67890 09
3 55667 07


But what it is doing is:
54666
1 12345 09
2 67890 09
3 55667 07

This "54666" is an institution number and it's in the abstract in institution to so I assumed that was the issue. But if I remove all entries 54666 in all abstracts then this 54666 still shows up in the consolidation.

Also, note that your addition to the code above made each data element skip down one line instead of keeping all on the same row i.e. for abstract #1, case number copies to cell A2, chart number copies to cell B3, coder copies to C4 etc. which isn't how I want it.

Thanks.

 

[SkipVought]

STEP thru your code and observe exactly WHAT is happening WHERE and WHEN.

Skip,

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

 

[shelby55]

Hi Skip

When I go into Debug, Step Into, and have a watch window open but nothing is showing up in the window...what am I doing wrong?

 

[SkipVought]

In your VB Editor and selected in the macro, F8 will perform one step. Repeated F8 keystrokes, will move thru the macro.

Skip,

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

 

[shelby55]

Hi

Thanks.

lRow = 2
r.Offset(0,2).Value = 1
.Range(r.Value) = J5

I've even deleted Master_NewA and created again but it is still showing the value of 54666 in it even when that doesn't exist anywhere in the abstract.

This isn't making sense.....

 

[shelby55]

Hi Skip

I took all entries of instto out of the rawdata worksheet (which is used to create the separate worksheets), ran to create separate worksheets and then when I ran the macro to put back into as single worksheet, it worked as expected.

But how does this explain why when there is data in that field that it causes issues?

 

[SkipVought]

WHEN in the process, does 54666 appear on the sheet in A1?

What Statement does it occur on?

Skip,

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

 

[shelby55]

I don't see that it does. I checked all lines as the macro fired and I don't see anywhere that 54666 shows up there. Note that I changed the information so that only abstract #5 had an entry in the inst to field (53444) and then when I ran consolidate it showed up in B2 and all abstracts copying normally from A3.

 

[SkipVought]

I don't know what all that means!

So??????

Skip,

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

 

[shelby55]

What it means is that if there is data in InstTo (J21 on the abstract) then it is causing problems with the copying of the data to Master_NewA and if no data is that field then all data from all abstracts copy as expected.

 

[SkipVought]

Is this J21 in a sheet with a range [SourceNewA] and what column is [SourceNewA] in?

You may need to send this workbook too.

Skip,

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

 

[shelby55]

Hi

The document Mapping_NewA has the named ranges for SourceNewA and MasterNewA.

SourceNewA is the named range of A2 to A247 and InstTo shows up in row 14 with the target of J21 in the abstracts...is that what you were asking?

 

[SkipVought]

neew workbook!

Skip,

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

 

[shelby55]

HI

Sorry not understanding...so create a new mapping document and recreate the named ranges?

 

[SkipVought]

Typing too fast.

Need 2C your workbook.

Skip,

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

 

[SkipVought]

Consolidate_NewA is looking at EVERY WORKSHEET in the workbook as source like Values, Template, Mappings (Various), Masters (other than NewA), Raw and then the individual 1,2,3 etc that WERE mapped.

So, if you would have stepped thru the data as I suggested, you would have seen that 54666 comes from RawData_A.



Skip,

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

 

[shelby55]

Thanks....so how do I correct that?

 

[SkipVought]

Exclude that sheet and any other just as [highlight]these[/highlight] have been excluded...

            Select Case .Name
                Case [highlight]"Master_NewA", "Mapping_NewA"[/highlight]
                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

Skip,

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