Subscript out of range

[Sjon1974]

All, i need a huge help,

I found this script and made it work, but now i added in the file all the other periods, but now i receive the out of range error (9)
It is stopping at the following line

Set ws_B = Worksheets("MAYO 1QNA")
Wich is weird cause it was working before.

What i am doing wrong. Btw it does not matter wich period i try, it always stops in this line but with a different worksheet name.

Sub PERIODO9A()


Dim ws_A As Worksheet
Dim ws_B As Worksheet
Dim HeaderRow_A As Long
Dim HeaderLastColumn_A As Long
Dim TableColStart_A As Long
Dim NameList_A As Object
Dim SourceDataStart As Long
Dim SourceLastRow As Long
Dim Source As Variant
Dim i As Long
Dim ws_B_lastCol As Long
Dim NextEntryline As Long
Dim SourceCol_A As Long
Set WB = ActiveWorkbook
Set ws_A = Worksheets("TMPQNA")
Set ws_B = Worksheets("MAYO 1QNA")
Set NameList_A = CreateObject("Scripting.Dictionary")

With ws_A
SourceDataStart = 2
HeaderRow_A = 1 'set the header row in sheet A
TableColStart_A = 1 'Set start col in sheet A
HeaderLastColumn_A = .Cells(HeaderRow_A, Columns.Count).End(xlToLeft).Column  'Get number of NAMEs you have

For i = TableColStart_A To HeaderLastColumn_A
If Not NameList_A.Exists(UCase(.Cells(HeaderRow_A, i).Value)) Then  'check if the name exists in the dictionary
NameList_A.Add UCase(.Cells(HeaderRow_A, i).Value), i 'if does not exist record name as KEY and Column number as value in dictionary
End If
Next i

End With

With ws_B  'worksheet you want to paste data into
ws_B_lastCol = .Cells(HeaderRow_A, Columns.Count).End(xlToLeft).Column ' Get number of DATA you have in sheet B
For i = 1 To ws_B_lastCol   'for each data
SourceCol_A = NameList_A(UCase(.Cells(4, i).Value))  'get the column where the name is in Sheet A from the dictionaary

If SourceCol_A <> 0 Then  'if 0 means the name doesnt exists
SourceLastRow = ws_A.Cells(Rows.Count, SourceCol_A).End(xlUp).Row
Set Source = ws_A.Range(ws_A.Cells(SourceDataStart, SourceCol_A), ws_A.Cells(SourceLastRow, SourceCol_A))
NextEntryline = .Cells(Rows.Count, i).End(xlUp).Row + 1 'get the next entry line of the particular name in sheet A

.Range(.Cells(NextEntryline, i), _
.Cells(NextEntryline, i)) _
.Resize(Source.Rows.Count, Source.Columns.Count).Cells.Value = Source.Cells.Value
End If

Next i
End With
ThisWorkbook.Worksheets("MAYO 1QNA").Select
ActiveWorkbook.RefreshAll
Call VLOOKUPQNA
End Sub

 

[QuietRiot]

That error message always means it can't find the sheet. Make sure you actually have a sheet named "MAYO 1QNA" spelled properly in activeworkbook where it's being run.
I don't think this is necessary, but maybe:
Set ws_B = WB.Worksheets("MAYO 1QNA")

 

[Sjon1974]

That error message always means it can't find the sheet. Make sure you actually have a sheet named "MAYO 1QNA" spelled properly in activeworkbook where it's being run.
I don't think this is necessary, but maybe:
Set ws_B = WB.Worksheets("MAYO 1QNA")

I was trying with the name and put everywhere a underscore bewteen month and 1qna Set ws_B = WB.Worksheets("MAYO_1QNA") and now no errors anymore. I suck hahahaha

So it was exactly what you said spelling somewhere