Can anyone help please, struggling to understand - If I run this code independently it works fine but when I incorporate it into a wider routine it is returning a runtime error 5 on the red highlighted section?
Dim rawSht As Worksheet
Dim procSht As Worksheet
Dim headers As Collection
Dim c As Long
Dim v As Variant
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
Set rawSht = ThisWorkbook.Worksheets("Raw Data")
Set procSht = ThisWorkbook.Worksheets("Output Sheet")
Set headers = New Collection
For c = 1 To rawSht.Cells(1, Columns.Count).End(xlToLeft).Column
headers.Add c, rawSht.Cells(1, c).Text
Next
For c = 1 To 47
rawCol = headers(procSht.Cells(6, c).Text)
v = rawSht.Range(rawSht.Cells(2, rawCol), rawSht.Cells(Rows.Count, rawCol).End(xlUp)).Value2
procSht.Cells(7, c).Resize(UBound(v, 1)).value = v
Next
End With
Dim rawSht As Worksheet
Dim procSht As Worksheet
Dim headers As Collection
Dim c As Long
Dim v As Variant
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
Set rawSht = ThisWorkbook.Worksheets("Raw Data")
Set procSht = ThisWorkbook.Worksheets("Output Sheet")
Set headers = New Collection
For c = 1 To rawSht.Cells(1, Columns.Count).End(xlToLeft).Column
headers.Add c, rawSht.Cells(1, c).Text
Next
For c = 1 To 47
rawCol = headers(procSht.Cells(6, c).Text)
v = rawSht.Range(rawSht.Cells(2, rawCol), rawSht.Cells(Rows.Count, rawCol).End(xlUp)).Value2
procSht.Cells(7, c).Resize(UBound(v, 1)).value = v
Next
End With