dabug80
asked on
Excel: Macro to Find Row Range For Cut/Paste
Hello,
I have the below code. Note my nested comments for the solutions I'm looking for. Essentially I've defined 3 ranges, but I'm not sure how to reference these ranges in the macro formula.
Note that I call upon the range 'sentlistend' twice - and the second time this would be a different row. Not sure if Excel can adjust for this.
Thanks.
I have the below code. Note my nested comments for the solutions I'm looking for. Essentially I've defined 3 ranges, but I'm not sure how to reference these ranges in the macro formula.
Note that I call upon the range 'sentlistend' twice - and the second time this would be a different row. Not sure if Excel can adjust for this.
Sub mac_addtosentlist()
'
' mac_addtosentlist Macro
'This macro copies the details of the people who will receive the 3 & 6 months emails and adds them on the sent list for future comparison
Dim sentlistend As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sent List")
sentlistend = ws.Range("A" & Rows.Count).End(xlUp).Row
Dim 3monthrowtally As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("3 Month Letter")
rowcount = ws.Range("A" & Rows.Count).End(xlUp).Row
Dim 6monthrowtally As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("6 Month Letter")
rowcount = ws.Range("A" & Rows.Count).End(xlUp).Row
Sheets("3 Month Letter").Select
'Below I want to select all the rows with data identified by 3monthrowtally
Activesheet.Rows("3monthrowtally").Select
Selection.Copy
Sheets("Sent List").Select
'Below I want to select 'A?' where '?' represents the row number identified by sentlistend. This will find me the first blank cell on this sheet, so I can paste the copied data.
ActiveCell.Row(sentlistend)+1
ActiveSheet.Paste
Sheets("6 Month Letter").Select
'
'Time to do the same, but for a second sheet. Below I want to select all the rows with data identified by 6monthrowtally
Activesheet.Rows("6monthrowtally").Select
Selection.Copy
Sheets("Sent List").Select
'Below I want to select 'A?' where '?' represents the row number identified by sentlistend. This will find me the first blank cell on this sheet, so I can paste the copied data.
'Note that because we've pasted data in already, does the sentlistend have to be redefined?
ActiveCell.Row(sentlistend)+1
ActiveSheet.Paste
Sheets("Paste Here").Select
End Sub
Thanks.
ASKER
Hello,
To clarify, I've added a sample sheet.
Essentially - I'd like to do the following:
1. 3 Month Letter sheet - copy all populated rows from 2 down.
2. Paste these copied rows to the bottom of the blank cells on the 'Sent Emails' sheet
3. 6 Month Letter sheet - copy all populated rows from 2 down.
4. Paste these copied rows to the bottom of the blank cells on the 'Sent Emails' sheet
Thanks
EE-Sample.xlsm
To clarify, I've added a sample sheet.
Essentially - I'd like to do the following:
1. 3 Month Letter sheet - copy all populated rows from 2 down.
2. Paste these copied rows to the bottom of the blank cells on the 'Sent Emails' sheet
3. 6 Month Letter sheet - copy all populated rows from 2 down.
4. Paste these copied rows to the bottom of the blank cells on the 'Sent Emails' sheet
Thanks
EE-Sample.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent solution. Thanks for building on my initial attempt and adding nested comments to explain your working.
dabug80
Just curious, but did you try the code I posted?
PS Using Select/Activate etc in Excel VBA code isn't good practice.
Just curious, but did you try the code I posted?
PS Using Select/Activate etc in Excel VBA code isn't good practice.
ASKER
Hi imnorie,
To be honest, no - I didn't try your code, as I was a bit put off by your sentence:
"PS If all you want to do is copy the last rows from '3 Month Letter' and '6 Month Letter' to 'Send List' you could try this."
As the definition of 'last rows' was a bit ambiguous to me. So I posted a clarifying post along with a sample spreadsheet.
To be honest, no - I didn't try your code, as I was a bit put off by your sentence:
"PS If all you want to do is copy the last rows from '3 Month Letter' and '6 Month Letter' to 'Send List' you could try this."
As the definition of 'last rows' was a bit ambiguous to me. So I posted a clarifying post along with a sample spreadsheet.
No problem.:)
What I meant by 'last rows' was the last populated row(s) in each sheet, but you have a solution and that's the most important thing.
What I meant by 'last rows' was the last populated row(s) in each sheet, but you have a solution and that's the most important thing.
Do you want to cut/copy rows from the sheets '3 Month Letter' and '6 Month Letter' and paste to sheet 'Sent List'?
If you do what rows do you want to cut/copy from each sheet and where do you want to paste them?
Also, there are some problems with your variables - you can't start a variable name with a number and you can't declare a variable, eg ws, more than once in the same sub.
The only declarations you should need are these.
Open in new window
PS If all you want to do is copy the last rows from '3 Month Letter' and '6 Month Letter' to 'Send List' you could try this.
Open in new window