The row argument for Resize is the total number of rows in the resized range,
not the last row. Since you are not starting in row 1, you need to subtract
25 rows (and add 1 back, so you include both the starting and ending rows).

Also, your statement
Set rngToCopy = Range("D25:J" & Cells(Rows.Count, "D").End(xlUp).Row
needs a closing right parenthesis. But it's doing all that work just to tell
you row r, which you already know.

I would rewrite your code fragment as follows:

Private Sub cmdTransferToSRP_Click()
'Copy Data and transfer to New Workbook
Dim rngToCopy As Range
Dim rngToPaste As Range
Dim r As Long
r& = Cells(Rows.Count, 5).End(xlUp).Row
Range("D25").Resize(r& - 25 + 1).FillDown
Set rngToCopy = Range("D25:J" & r&)

Hope this helps,

Hutch

"ca1358" wrote:

Excel VBA

I am trying to
1) copy D25 and copy down where Column E data ends
2) Then Copy start in D25 through Column J where data ends
And put into a new worksheet starting A1.
D E F G H I
J
25 2805 11111 11111 1111 1111 111 111
26 11111 11111 1111 1111 111 111
27 11111 11111 1111 1111 111 111

I am having trouble with syntax of the following piece of code.

Now need to figure out
Test Data right now is D25:J37 with D25 having on "2805" and D26 on down
blank but when I run the code for some reason the "2805" in D25 is copying
down to D61 and not stop in D37 Where Column E ends in E37. Can someone tell
me why?

Any help would greatly be appreciated!


Private Sub cmdTransferToSRP_Click()
'Copy Data and transfer to New Workbook
Dim rngToCopy As Range
Dim rngToPaste As Range


Dim r
r = Range("E65536").End(xlUp).Row
Range("D25").Resize(r, 1).FillDown


Set rngToCopy = Range("D25:J" & Cells(Rows.Count, "D").End(xlUp).Row

--
ca1358