Hi Guys,
i was just wondering if anyone can help I am using the below code to sort values from column b & c sheet 1 into named ranges defined in column a on sheet 1 on ranges in sheet2 but for some reason keeps on coming up with a runtime error 1004 i have checked through all the references and everything seems to be okay. can anyone offer anyhelp please?
[Sub CopySheet1A1toSheet2NextCellInColumnA()
Dim lRow As Long, l As Long
Dim sRangeName As String
Dim lLastRow As Long 'Last used row in Column A on Sheet1
'Last Used Row
lLastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
'Loop through all used cells on column A with a value
For l = 1 To lLastRow
'Copy Cells B & C of the current row On sheet 1
Sheets("Sheet1").Range("B" & 2 & ":C" & 2).Copy
'get the name range
sRangeName = Sheets("Sheet1").Range("A" & 2).Value
'replace any spaces with underscore:
sRangeName = Replace(sRangeName, " ", "_")
'get next empty row in named range
lRow = 1
With Sheets("Sheet2").Range(sRangeName).Cells(lRow, 1)
Do Until .Value = ""
lRow = lRow + 1
Loop
'Paste Data
.PasteSpecial xlPasteAll
End With
Next l
End Sub]
Bookmarks