Solved

Excel: Macro to Find Row Range For Cut/Paste

Posted on 2014-10-23
7
251 Views
Last Modified: 2014-10-27
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.

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

Open in new window


Thanks.
0
Comment
Question by:dabug80
  • 3
  • 3
7 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 40401231
It's not really clear what you want to cut/copy/paste from/to.

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.
Dim ws As Worksheet
Dim sentlistend As Long
Dim monthrowtally3 As Long
Dim monthrowtally6 As Long
Dim RowCount As Long

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.
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 ws As Worksheet
Dim wsSent As Worksheet

    Set wsSent = ThisWorkbook.Sheets("Sent List")
    
    Set ws = ThisWorkbook.Sheets("3 Month Letter")
    
    ws.Range("A" & Rows.Count).End(xlUp).EntireRow.Copy wsSent.Range("A" & Rows.Count).Offset(1)
    
    Set ws = ThisWorkbook.Sheets("6 Month Letter")
    
    ws.Range("A" & Rows.Count).End(xlUp).EntireRow.Copy wsSent.Range("A" & Rows.Count).Offset(1)
      
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:dabug80
ID: 40405529
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
0
 
LVL 12

Accepted Solution

by:
sdwalker earned 500 total points
ID: 40407179
If I understand it correctly, this is what you're looking for.  If not, please let me know.
sdw-EE-Sample.xlsm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Closing Comment

by:dabug80
ID: 40407435
Excellent solution. Thanks for building on my initial attempt and adding nested comments to explain your working.
0
 
LVL 33

Expert Comment

by:Norie
ID: 40407540
dabug80

Just curious, but did you try the code I posted?

PS Using Select/Activate etc in Excel VBA code isn't good practice.
0
 
LVL 1

Author Comment

by:dabug80
ID: 40407549
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.
0
 
LVL 33

Expert Comment

by:Norie
ID: 40407604
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now