Sjon1974
New Member
- Joined
- Apr 1, 2020
- Messages
- 38
- Office Version
- 365
- Platform
- Windows
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.
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.
VBA Code:
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