VBA PasteSpecial does Partial Paste

[KWHAT]

thank you in advance for taking your time to help me out,

I currently have a loop running which selects a range, copies and then pastes the values in the required columns.

 Set wks6 = wkb.Sheets.Add
                    With wks6

                        Dim rstCP As Recordset
                        Dim firstCP As Boolean
                        Dim previousPot As Integer

                        Set rstCP = CurrentDb.OpenRecordset("FUT_CR_Capacity_Count")
                        firstCP = False
                        previousPot = 0

                        Do While Not rstCP.EOF
                             If firstCP = False Then
                                .Range("N3", "AA3").Copy
                                .Range("AB" & rstCP![COuntWC] + 3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                :=False, Transpose:=False
                                previousPot = previousPot + rstCP![COuntWC] + 3
                                firstCP = True

                            Else
                                .Range("N" & previousPot + 1 & ", AA" & previousPot + 1 & "").Copy
                                Debug.Print "N" & previousPot + 1 & ",AA" & previousPot + 1 & ""
                                .Range("AB" & rstCP![COuntWC] + previousPot + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                :=False, Transpose:=False
                                  Debug.Print "AB" & rstCP![COuntWC] + previousPot + 1
                                 previousPot = previousPot + rstCP![COuntWC] + 1
                            End If
                             rstCP.MoveNext
                        Loop
                       End With

So the first part of the loop (If firstCP = False Then) runs perfectly fine it copies the range N3 to AA3 further pasting the values in the range AA13 to AO13 automatically. However when the loop continues for all other increments (the else part of the if statment), it partially pastes the values from columns AA15 to AB15


NOTE: IVe tried specifying the range in the paste

    .Range(AA#,AO#).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                :=False, Transpose:=False

This Does not work either. Can someone help me out.

 

[Isskint]

The issue is probably caused by the way you reference ranges and the string you build in the Else section.

.Range("xx","yy").select will select a continuous block of cells
where as
.Range("N" & previousPot + 1 & ", AA" & previousPot + 1 & "").select will only select the first and last cells in the range. Replace the comma with a colon, such:
.Range("N" & previousPot + 1 & ": AA" & previousPot + 1)

 

[KWHAT]

The issue is probably caused by the way you reference ranges and the string you build in the Else section.

.Range("xx","yy").select will select a continuous block of cells
where as
.Range("N" & previousPot + 1 & ", AA" & previousPot + 1 & "").select will only select the first and last cells in the range. Replace the comma with a colon, such:
.Range("N" & previousPot + 1 & ": AA" & previousPot + 1)

Amazing it worked thanks for such a fast reply.

 

[KWHAT]

If you can i am trying to inset a new line right after the insert is done ,

Range("AB").Offset(1).EntireRow.Insert

however this crashes do you know why ? Error 1004 ive also tired

.Range("AB").EntireRow.Offset(1, 0).Insert