Data Transfer

[Risque666]

I have the Spreadsheet with Sheet 2 called Codes & Sheet 1 Called Inserts.
This is the macro I was given to transfer the data to the Inserts sheet but it has the following error as highlighted.

The Inserts where I need the Codes to go are Merged & Centered - Is this an issue ?
See below:

The spreadsheet is almost there but I am getting stuck just on the transfer of Data.
Can anyone help it shows that the cell is S23 for this example .. is there an easier way to do this.?

 

[maabadi]

For error you should Check your Inserts Sheet. Are you have correct Data on it? Hover mouse pointer on i and after show value check that cell at Inserts Sheet.
for S23 change code to This:

Sub TransferData()
Dim i As Long, j As Long, Lr1 As Long, K As Long, Sh1 As Worksheet, Sh2 As Worksheet
Set Sh1 = Sheets("Inserts")
Set Sh2 = Sheets("Codes")
Lr1 = Sh1.Cells(Rows.Count, 1).End(xlUp).Row
For i = Lr1 To 1 Step -1
If Len(Trim(Sh1.Range("A" & i).Value)) = 11 And IsNumeric(Left(Trim(Sh1.Range("A" & i).Value), 4) * 1) Then
Else
Rows(i).Delete
End If
Next i
Lr1 = Sh1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lr1 Step 9
Sh2.Range("C10").Value = Sh1.Range("A" & i).Value
Sh2.Range("K10").Value  = Sh1.Range("A" & i + 1).Value
Sh2.Range("S10").Value = Sh1.Range("A" & i + 2).Value
Sh2.Range("C23").Value = Sh1.Range("A" & i + 3).Value
Sh2.Range("K23").Value = Sh1.Range("A" & i + 4).Value
Sh2.Range("S23").Value = Sh1.Range("A" & i + 5).Value
Sh2.Range("C36").Value = Sh1.Range("A" & i + 6).Value
Sh2.Range("K36").Value = Sh1.Range("A" & i + 7).Value
Sh2.Range("S36").Value = Sh1.Range("A" & i + 8).Value
K = K + 1
Sh2.ExportAsFixedFormat Type:=xlTypePDF, Filename:="D:/PrintPDFs/Print" & K & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next i
End Sub