Link to home
Start Free TrialLog in
Avatar of ca1358
ca1358

asked on

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
   
   
Avatar of ca1358
ca1358

ASKER

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
Avatar of ca1358

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of EE_AutoDeleter
EE_AutoDeleter

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial