Condital Cell Copy

Microsoft Microsoft excel 2007 (pc)
July 2, 2010 at 01:10:59
Specs: Windows 7
First off, I never used VBA. I searched the forums and couldnt quite find a solution that would work for me. From what I can tell this is a common question, but would appreciate any help cause this will save me hours of work.

Here's what I'm looking to do.

On Sheet A I have a table that looks like this

Username CH1 CH2 CH3 CH4 CH5 ....
ab211 0 0

See More: Condital Cell Copy

Report •

July 2, 2010 at 01:19:45
ooops hit enter on accident.

On Sheet A I have a table that looks like this
1Username CH1 CH2 CH3 CH4 CH5 ....
2Bob 0 10
3John 10 9
4Joe 11 1
5Sarah 8 9

On sheet b I have the scores for CH3.
Sheet B has the same format has sheet A
Column A = user name
Column B = CH3 values

I would like to copy the CH3 scores from Sheet B to Sheet A under column D.

The problem I'm running into is that not all the users names are listed in Sheet B. Some users didnt participate. But all usersname are listed in Sheet A.

So I need the macro to validate that its copying into the appropriate row in sheet A. I ran into just blindly copying cause not all users were in Sheet B, C, etc.

I hope this makes sense.

Any help would be appreciated.


Report •

July 2, 2010 at 05:09:12

Here is a macro that will copy the data in Column B of the source worksheet named "Sheet B" and put it in column D of the destination worksheet named "Sheet A"
Data will be matched by names in column A on both worksheets.
The order of names and missing names do not matter.
Extra names on the source worksheet are ignored.

To run the macro, I suggest you add a button to your source 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

Private Sub Button1_Click()
Dim rngCell As Range
Dim rngStart As Range
Dim rngEnd As Range
Dim rngDestCell As Range
Dim rngDestStart As Range
Dim rngDestEnd As Range
Dim blnFound As Boolean

'set start of source data on "Sheet B"
Set rngStart = Worksheets("Sheet B").Range("A2")
'find end of source data on "Sheet B"
Set rngEnd = Worksheets("Sheet B") _
        .Range("A" & CStr(Application.Rows.Count)).End(xlUp)

'set start of destination data on "Sheet A"
Set rngDestStart = Worksheets("Sheet A").Range("A2")
'find end of destination data on "Sheet A"
Set rngDestEnd = Worksheets("Sheet A") _
        .Range("A" & CStr(Application.Rows.Count)).End(xlUp)

'loop through source data names
For Each rngCell In Worksheets("Sheet B").Range(rngStart, rngEnd)
    'flag as 'not found'
    blnFound = False
    'loop through destination sheet names
    For Each rngDestCell In Worksheets("Sheet A").Range(rngDestStart, rngDestEnd)
        'test for matching name
        If rngCell.Text = rngDestCell.Text Then
            'match found - so move source col B to destination Col D
            rngCell.Offset(0, 1).Copy Destination:=rngDestCell.Offset(0, 3)
            'flag that matching name found
            blnFound = True
        End If
        'no need to test any more destination names if a match found
        If blnFound = True Then Exit For
    Next rngDestCell
Next rngCell
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 changes made by 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.

You could add more buttons - one for each column to be copied, just changing the above macro to access the correct Source worksheet (Change "Sheet B") and the column to put the data into in the destination sheet Destination:=rngDestCell.Offset(0, 3) - change the value 3 (0=A, 3=D)


Report •

Related Solutions

Ask Question