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.SelectedSheet s.Delete
Sheets("Sheet2").Select
ActiveWindow.SelectedSheet s.Delete
Application.CutCopyMode = False
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).
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).
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.SelectedSheet
Sheets("Sheet2").Select
ActiveWindow.SelectedSheet
Application.CutCopyMode = False
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.
Dim r
r = Range("E65536").End(xlUp).
Range("D25:D" & r).FillDown
Set rngToCopy = Range("D25:J" & Cells(Rows.Count, "D").End(xlUp).Row)
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).
Range("D25").Resize(r, 1).FillDown