Copy certain cells to another sheet

Microsoft Office excel 2007 plain & simp...
March 16, 2010 at 07:47:07
Specs: Windows Vista
I have an Excel program that I have built but need some help with an addition that I have added that I need a Macro to Move Data on sheet Bills based on a Keyword in Column "N". If "N" shows "Paid" than I need to move certain cells to worksheet Input. Here is what needs moved cells 'B to A', 'C to B', 'D to G', 'F to D', 'J to E', 'K to C', 'L to F'. my Data starts on row 13 on the "Bills" page and on the "Input" page starts on 10. once the Data have been moved I would like it if it would clear the row from the Bills page and move the data below up to fills the empty spot.

See More: Copy certain cells to another sheet

March 16, 2010 at 09:30:06

Here is a macro that will find rows marked "Paid" and copy the cells you specified to the Input worksheet.

I hope that I have got the right paring of cells between Bills and Input. If not it should be pretty easy to make adjustments.
After copying the cells, all rows marked "Paid" are deleted (and rows moved up).

To run the macro, I suggest you add a button to your source (Bills) worksheet.

From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit

Sub Button1_Click()
Dim rngBillsStart As Range
Dim rngBillsEnd As Range
Dim rngInputEnd As Range
Dim rngCell As Range
Dim n As Integer

On Error GoTo ErrHnd

'Turn screen updating off - to avoid flicker
Application.ScreenUpdating = False

'set first data row on Bills worksheet
Set rngBillsStart = Worksheets("Bills").Range("A13")

'find last used row on Bills worksheet
Set rngBillsEnd = Worksheets("Bills").Range("A" & CStr(Application.Rows.Count)) _

'loop through Bills data area and find any row with "Paid" in col N (offset =13)
For Each rngCell In Worksheets("Bills").Range(rngBillsStart, rngBillsEnd)
    'test column N
    If rngCell.Offset(0, 13).Text = "Paid" Then
        'find row after last used row on Input worksheet
        Set rngInputEnd = Worksheets("Input") _
                        .Range("A" & CStr(Application.Rows.Count)) _
                        .End(xlUp).Offset(1, 0)
        'move 'B to A', 'C to B', 'D to G', 'F to D', 'J to E', 'K to C', 'L to F'
        rngCell.Offset(0, 1).Copy Destination:=rngInputEnd.Offset(0, 0)
        rngCell.Offset(0, 2).Copy Destination:=rngInputEnd.Offset(0, 1)
        rngCell.Offset(0, 3).Copy Destination:=rngInputEnd.Offset(0, 6)
        rngCell.Offset(0, 5).Copy Destination:=rngInputEnd.Offset(0, 3)
        rngCell.Offset(0, 9).Copy Destination:=rngInputEnd.Offset(0, 4)
        rngCell.Offset(0, 10).Copy Destination:=rngInputEnd.Offset(0, 2)
        rngCell.Offset(0, 11).Copy Destination:=rngInputEnd.Offset(0, 5)
    End If
Next rngCell

'delete all the 'Paid' rows - work backwards when deleting rows
For n = rngBillsEnd.Row To rngBillsStart.Row Step -1
    'find and delete row if col N is "Paid"
    If Worksheets("Bills").Range("N" & CStr(n)).Text = "Paid" Then
    End If
Next n

'Turn screen updating on again
Application.ScreenUpdating = True
Exit Sub

'error handler
'Turn screen updating on again
Application.ScreenUpdating = True
End Sub

Note that Sub Button1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful

As Macros cannot be undone with the undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

After selecting any cell, the new command button should now respond to a click and run the macro.

Note that the copy operation does a complete copy and paste in one statement. If you only wanted to move the values, then each statement will need to be split into a copy statement and a second PasteSpecial (paste values) statement.

Hope this helps.


Report •
Related Solutions

Ask Question