Associate Multiple Cells with One Cell

[geno32080]

Can a Macro be used to associate Multiple Cells with One Cell on the same sheet and then be copied and pasted to another sheet? The association would be B4 = C4, C5, C7, C8 and C9.
If I select cell B4, on sheet 5, that cell and the associated cells get copied from sheet 5 and pasted to sheet 3 starting with the B4 pasted to cell B15, and then C4 thru C9 pasted to cell D15 thru D19?
I don't know if that's even possible. I've tried Data validation, but that was a dead end. I'm up for any suggestions. Thank you.

 

[Joe4]

I think that should all be do-able with VBA, though I am a little fuzzy on the logic/rules that we would need to program.
Can you show us an example, and walk us through it with that data?

 

[geno32080]

Absolutely, There are three types of clean, as seen on the Service Data sheet, B4. B5 and B6, I would like to associate . A2, A3 and A4 with the appropriate type of clean. Weekly Cleaning (A2) to the description of service on C4. So when the user clicks the Select Type of Clean button, the macro would choose the red bold font, copy it and the associated cells with A2 to the Create Work order Sheet, the macro would paste the Weekly cleaning to cell B15. and the associated cells would paste to C15 and down . Does that help?

 

[Joe4]

Note quite entirely clear yet (remember, while you have this complex/elaborate form you are building and you know exactly what you want it to do), all that we know about it is what you share with us.
So you want to be painstakingly detailed in your explanations, being clear about exactly what you want.
And it looks like you want to use the formatting functionality (bold/red) like you used in your last question (but did not mention until now in this question).

So when the user clicks the Select Type of Clean button, the macro would choose the red bold font, copy it and the associated cells with A2 to the Create Work order

Is that button on the Service Data sheet somewhere? The only button I see from your images is "Service Data".

Sheet, the macro would paste the Weekly cleaning to cell B15. and the associated cells would paste to C15 and down

Do you mean all the records which have a value of A2 in column D?

Also, is there any flexibility on the design of this? The way you have structured the data in cells B2 - B4, which have the name of the "Type of Clean" AND the code in the same string is going to make it harder to maintain and program against (especially if your code could move out to three characters, like A10, A11, etc). it would be better to be in a two column lookup table.

All things equal, billing programs like these work much better in relational database programs like Microsoft Access. While they can be done in Excel, it is often clunky and cumbersome (as you are seeing).

 

[geno32080]

Thanks for your interest. I highlighted your questions in red, I think I got them all.

Is that button on the Service Data sheet somewhere? The only button I see from your images is "Service Data".

Yes it is on the right side of the sheet.

Do you mean all the records which have a value of A2 in column D?

Yes, I put those defintions in D E F so I could see which description of service would relate to which type of clean. I didnt know of any other way. There s plenty of flexibility. Ideally the end result would resemble the screen shot below. Im not sure how to associate Weekly Clean with the description of service.

The way you have structured the data in cells B2 - B4, which have the name of the "Type of Clean" AND the code in the same string is going

I Only put the A2 in the Type of Clean (WEEKLY CLEANING = A2) for the same reason I listed A2 in column D as a reference only.

 

[Joe4]

Remind me again, what triggers the cells in range B2:B4 to turn bold red?
Is it simply just selecting that cell (the ActiveCell)?
I am asking because it would be much easier to program against the ActiveCell, then it is to try to find which cells have Conditional Formatting applied to them.
Due to the structure/layout of your project, the code is going to be complex enough. I just don't want to overcomplicate it if we don't have to.

Also, I thought you said that if B4 is highlighted on the Service Data sheet, the value in B4 should be copied over to B15 on the Work Order sheet.
However, in your example, you show "Weekly Cleaning = A2" in cell B4 on your Service Data Sheet, and you show "REGULAR CLEANING" in cell B15 on your Work Order sheet.
So that doesn't seem to follow the rule you laid out for us.

 

[Joe4]

If my assumptions in the previous post are correct, I believe that the following code will do what you want based on your current structure (it may not be the prettiest code, but it seems to get the job done):

Sub MyMoveData()

    Dim ccode As String
    Dim lr As Long
    Dim r As Long
    Dim nr As Long
    
'   Make sure you are in column B below row 3
    If ActiveCell.Column <> 2 Or ActiveCell.Row < 4 Then
        MsgBox "You must be in column B under row 3 to start!"
        Exit Sub
    End If
    
'   Get code from end of string
    ccode = Trim(Mid(ActiveCell.Value, InStr(1, ActiveCell.Value, "=") + 1))
    
'   Find last row with data in column C
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Set initial new row value
    nr = 15
    
    Application.ScreenUpdating = False
    
'   Populate cell B15 on Work Order sheet
    Sheets("Work Order").Range("B15").Value = ActiveCell.Value
    
'   Loop through all rows
    For r = 4 To lr
'       Check to see if columns D, E, or F contain the selected code
        If (Cells(r, "D") = ccode) Or (Cells(r, "E") = ccode) Or (Cells(r, "F") = ccode) Then
'           Populate column C on Work Order Sheet
            Sheets("Work Order").Range("C" & nr).Value = Cells(r, "C").Value
'           Increment row counter
            nr = nr + 1
        End If
    Next r
    
    Application.ScreenUpdating = True
        
End Sub

 

[geno32080]

Remind me again, what triggers the cells in range B2:B4 to turn bold red?
The trigger is a conditional format, I can change that to what ever is easiest for coding. I only set that up so when the user makes a selection it highlights the cell and makes it clear what selection has been made. And the cells are B4:B6 on the Service data sheet.

Weekly Cleaning = A2" in cell B4 on your Service Data Sheet, and you show "REGULAR CLEANING" in cell B15

That was a mistake on my part. I typed in the "Regular Cleaning" phrase. The correct phrase is Weekly Cleaning.(sorry)

 

[Joe4]

Remind me again, what triggers the cells in range B2:B4 to turn bold red?
The trigger is a conditional format, I can change that to what ever is easiest for coding. I only set that up so when the user makes a selection it highlights the cell and makes it clear what selection has been made. And the cells are B4:B6 on the Service data sheet.

Weekly Cleaning = A2" in cell B4 on your Service Data Sheet, and you show "REGULAR CLEANING" in cell B15

That was a mistake on my part. I typed in the "Regular Cleaning" phrase. The correct phrase is Weekly Cleaning.(sorry)

OK, I think then the code that I provided in my last post should work the way you want.

 

[geno32080]

Made one small adjustment. "Work Order" changed to "CREATE WORK OPRDER" and BOOM! Like a champ. You are the Excel Macro Man!

Thank you.