New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Macro to Cut and Paste Entire row to another worksheet

Macros, VBA, Excel Automation, etc.

Macro to Cut and Paste Entire row to another worksheet

Postby sgt major 1 » Wed Dec 07, 2011 6:55 pm

Hello All,

This is my first post so excuse me if i break any rules. I have hit a wall with a macro that needs some help from the pros.

I have a tab labeled "bonds" which acts as the main sheet in which all data is dumped and it has headers in row 1.
I have 4 other tabs - "ABS" , "CD's" , "TSY-Agency" , "CMO" that have identical headers in row 1 as well.

What I am trying to do is program a macro that looks in "Column A" on the "bonds" tab and if any cell in that column has 1 of 4 values the entire row is cut and pasted to its corresponding sheets. For example: A2 = "ABS" then the macro will cut and paste the entire row to A2 on the "ABS tab." A3 = "TSY" then the macro will cut and paste the entire row to the "TSY-Agency tab." and so forth.

On the "bonds" tab there will be many repeats of of all 4 categories and they may be in different order. The following is what i have so far and it only works for the ABS, not the other categories. If there is a more efficient way of doing it please let me know.

Code: Select all
Set i = Sheets("bonds")
    Set e = Sheets("ABS")
    Dim d
    Dim j
    d = 1
    j = 2
    Do Until IsEmpty(i.Range("A" & j))
    If i.Range("A" & j) = "ABS" Then
    d = d + 1
    e.Rows(d).Value = i.Rows(j).Value
    End If
    j = j + 1
    Loop


Thank you very much
  • 0

sgt major 1
Rookie
 
Posts: 2
Joined: Dec 7, 2011
Reputation: 0

Re: Macro to Cut and Paste Entire row to another worksheet

Postby Don » Wed Dec 07, 2011 7:20 pm

Hi there and welcome to the forum!

There are many different ways to do what you were looking for, but I just added onto what you were doing and it works pretty well.

Here is the updated code:

Code: Select all
Sub Copy_Data()

Dim d As Integer
Dim j As Integer

Set i = Sheets("bonds")
Set e = Sheets("ABS")
Set o = Sheets("CD's")
Set u = Sheets("TSY-Agency")
Set ab = Sheets("CMO")

d = 1
j = 2

Do Until IsEmpty(i.Range("A" & j))

    If i.Range("A" & j) = "ABS" Then
        d = e.Range("A" & Rows.Count).End(xlUp).Row + 1
        e.Rows(d).Value = i.Rows(j).Value
    ElseIf i.Range("A" & j) = "CD's" Then
        d = o.Range("A" & Rows.Count).End(xlUp).Row + 1
        o.Rows(d).Value = i.Rows(j).Value
    ElseIf i.Range("A" & j) = "TSY-Agency" Then
        d = u.Range("A" & Rows.Count).End(xlUp).Row + 1
        u.Rows(d).Value = i.Rows(j).Value
    ElseIf i.Range("A" & j) = "CMO" Then
        d = ab.Range("A" & Rows.Count).End(xlUp).Row + 1
        ab.Rows(d).Value = i.Rows(j).Value
    End If
   
    j = j + 1
   
Loop



End Sub



You were only missing a few things, the main one being an IF statement with a few more conditions. Also, this line e.Range("A" & Rows.Count).End(xlUp).Row + 1 is how you can get the next empty row for the desired worksheets.

hope this helps :)
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Re: Macro to Cut and Paste Entire row to another worksheet

Postby Sisyphus » Thu Dec 08, 2011 3:47 am

Hi,
We have just been through this, more or less. If you like, have a look at my today's post on the old Teachexcel.com forum. I have explained there how to address ranges for the Copy method. Cut works the same way.
Regards,
:D
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Macro to Cut and Paste Entire row to another worksheet

Postby sgt major 1 » Thu Dec 08, 2011 5:49 pm

Don

thanks for the response, it did not work completely. It transfered all of the ABS to the "ABS" tab. There must be somthing missing. The macro should look in column A and reference 4 values before it copies and pastes. the 4 values that may appear in column A are:

ABS (needs to be transferred to "ABS" tab)
US (needs to be transferred to "TSY-Agency" tab)
CD (Needs to be transferred to "CD" tab)
CMO (needs to be transferred to "CMO" tab)

any suggestiopns are appreciated
  • 0

sgt major 1
Rookie
 
Posts: 2
Joined: Dec 7, 2011
Reputation: 0

Re: Macro to Cut and Paste Entire row to another worksheet

Postby Don » Thu Dec 08, 2011 5:53 pm

Oh ok, I don't think that was mentioned in the first post. I just assumed the name of the tabs was the same as what was in the cells in column A.

Try this:

Code: Select all
    Sub Copy_Data()

    Dim d As Integer
    Dim j As Integer

    Set i = Sheets("bonds")
    Set e = Sheets("ABS")
    Set o = Sheets("CD's")
    Set u = Sheets("TSY-Agency")
    Set ab = Sheets("CMO")

    d = 1
    j = 2

    Do Until IsEmpty(i.Range("A" & j))

        If i.Range("A" & j) = "ABS" Then
            d = e.Range("A" & Rows.Count).End(xlUp).Row + 1
            e.Rows(d).Value = i.Rows(j).Value
        ElseIf i.Range("A" & j) = "CD" Then
            d = o.Range("A" & Rows.Count).End(xlUp).Row + 1
            o.Rows(d).Value = i.Rows(j).Value
        ElseIf i.Range("A" & j) = "US" Then
            d = u.Range("A" & Rows.Count).End(xlUp).Row + 1
            u.Rows(d).Value = i.Rows(j).Value
        ElseIf i.Range("A" & j) = "CMO" Then
            d = ab.Range("A" & Rows.Count).End(xlUp).Row + 1
            ab.Rows(d).Value = i.Rows(j).Value
        End If
       
        j = j + 1
       
    Loop



    End Sub

  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 38 guests