Syntax with copy down and copying a block

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.

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

Set rngToCopy = Sheets("Data input sheet").Cells(Rows.Count, "D").End(xlUp).Offset(25, 4)

Any help would greatly be appreciated!

Here is the full piece
///////////////////////////////////////////////////////////
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 = Sheets("Data input sheet").Cells(Rows.Count, "D").End(xlUp).Offset(25, 4)
   
'Open new workbook to create text file

Workbooks.Add
rngToCopy.Copy
ActiveSheet.Paste


    Application.CutCopyMode = False
   
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
   
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.Delete
   
 Application.CutCopyMode = False
   
   
ca1358Asked:
Who is Participating?
 
EE_AutoDeleterConnect With a Mentor Commented:
ca1358,
Because you have presented a solution to your own problem which may be helpful to future searches, this question is now PAQed and your points have been refunded.

EE_AutoDeleter
0
 
ca1358Author Commented:
Ok got the rngToCopy to work.

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

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.  Can someone tell me why?


Dim r
  r = Range("E65536").End(xlUp).Row
  Range("D25").Resize(r, 1).FillDown
0
 
ca1358Author Commented:
Here is the answer- you can close this.  

Dim r
  r = Range("E65536").End(xlUp).Row
  Range("D25:D" & r).FillDown


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


Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.