0

I looked at several solutions to this problem and tried a few of them including prefixing period "." to the keyword cells but did not work

Following is my sub 

Sub GJK()
Dim port_total_periodic_rows As Worksheet

Dim Target As Worksheet
Dim i As Integer
Dim J As Integer
Dim DTCPYCTR As Integer
Dim DTPSTCTR As Integer
Dim FNDPSTCTR As Integer>
Dim FNDCPYCTR As Integer

DTCPYCTR = 5
FNDCPYCTR = 7
DTPSTCTR = 2
FNDPSTCTR = 2

For i = 1 To 138
    For J = 1 To 113
    Sheets("port_total_periodic_rows").Cells(DTCPYCTR, 3).Copy
    Sheets("Target").Cells(DTPSTCTR, 1).PasteSpecial Paste:=xlPasteValues
    Sheets("port_total_periodic_rows").Range(Cells(FNDCPYCTR, 2), _
                                               Cells(FNDCPYCTR, 4)).Copy
    Sheets("Target").Range(Cells(FNDPSTCTR, 2), _
                 Range(FNDPSTCTR,4)).PasteSpecial Paste:=xlPasteValues

    FNDPSTCTR = FNDPSTCTR + 1
    FNDCPYCTRT = FNDCPYCTR + 1
    DTPSTCTR = DTPSTCTR + 1

    Next

    DTCPYCTR = DTCPYCTR + 1 
Next 

End Sub
Community
  • 1
  • 1
George Kalathoor
  • 1
  • 1
  • 1
    "Prefixing a period" won't magically work. The period in VBA (and many other languages) is used for accessing a member of some object. Go back to the solutions you've seen "prefixing a period", you'll notice there's a `With` block a few lines above - the period doesn't come out of the blue, it literally accesses the object specified by the `With` statement. – Mathieu Guindon Nov 11 '16 at 22:08

1 Answers1

3

By default Cells() refers to the ActiveSheet, so this will fail if the active sheet is not "port_total_periodic_rows":

Sheets("port_total_periodic_rows").Range(Cells(FNDCPYCTR, 2), _
                                           Cells(FNDCPYCTR, 4)).Copy

Should be:

With Sheets("port_total_periodic_rows")
    .Range(.Cells(FNDCPYCTR, 2), .Cells(FNDCPYCTR, 4)).Copy
End with

Similar thing also applies to the following line.

Also better to use Long instead of Integer to avoid possible overflow problems.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • I switching back and forth between two errors, – George Kalathoor Nov 14 '16 at 15:25
  • Thank you this answer worked and I found that the code I had written for paste is no longer accepted pastespecial paste:=xlpastevalues does not work intead I used PasteSpecial xlPasteValues – George Kalathoor Nov 14 '16 at 15:56
  • Range object requires atleast two cells i.e. range(.cells(1,2),.cells(3,4)) . Is there another object that has functions like copy and paste but is OK with just a single cell as input parameter ? – George Kalathoor Nov 14 '16 at 18:20