Macro ******* multitask

[bbqsmokeman]

Hello

I am new to the site and macros but would like to know if anyone can help.

Basically I am learning macros and would like to know if it is possible to have a macro display a range from another sheet

Sheet 1 has names (A13:A40) with IDs (B13:B40) and a 2015 calendar in range of M9:AI43
Sheets 2, 3 4 have the reps names on them with a calendar at L14:AH48

What I would like to do is have a macro that when I click on cell B13 (whoever the name is) then the macro would display the calendar from sheet 2 (same name referencing B13 on sheet 1 and temporary copy or display the calendar from sheet 2 in sheet 1 at M9:AI43
If this can be done, I would be looking at the macro to be able to do the exact same thing for any ID clicked on in sheet 1 and reference the corresponding name (each sheet is rep named)

Any help is greatly appreciated.
Noobie macro learner here; but they say you're never to old to learn

 

[Peter_SSs]

Welcome to the MrExcel board!

Rather than clicking a cell I've set this up to work on a double-click. That usually means the code doesn't need to run as often.
In addition, I've made it so you can double-click either the ID in column B or the Name in column A (rows 13:40).
I've assumed that the names in Column A exactly match the sheet names for those reps.

Test this in a copy of your workbook.

To implement ..

1. Right click the main sheet (Sheet 1 in your description) name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test by double-clicking a name or ID or anywhere else on the main sheet.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_BeforeDoubleClick([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range, Cancel [color=darkblue]As[/color] [color=darkblue]Boolean[/color])
  [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
  [color=darkblue]Dim[/color] rCal [color=darkblue]As[/color] Range
  [color=darkblue]Dim[/color] sName [color=darkblue]As[/color] [color=darkblue]String[/color]
  
  [color=darkblue]Const[/color] sRepCal [color=darkblue]As[/color] [color=darkblue]String[/color] = "L14:AH48"
  
  [color=darkblue]With[/color] Target
    [color=darkblue]If[/color] .Column < 3 And .Row >= 13 And .Row <= 40 [color=darkblue]Then[/color]
      Cancel = [color=darkblue]True[/color]
      [color=darkblue]Set[/color] rCal = Range("M9:AI43")
      rCal.ClearContents
      sName = .Offset(, 1 - .Column).Value
      [color=darkblue]If[/color] sName <> "" [color=darkblue]Then[/color]
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
        [color=darkblue]Set[/color] ws = Sheets(sName)
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
        [color=darkblue]If[/color] ws [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
          MsgBox "No worksheet for: " & sName
        [color=darkblue]Else[/color]
          rCal.Value = ws.Range(sRepCal).Value
        [color=darkblue]End[/color] [color=darkblue]If[/color]
      [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
  [color=darkblue]End[/color] [color=darkblue]With[/color]
End [color=darkblue]Sub[/color]

 

[bbqsmokeman]

Thank you Peter

I ran a test with your code and it appears to copy it with exception that the calendar for the person (Name or ID) has colored cells. My bad for not adding this criteria in my initial inquiry/request and the colored cells it is copying is not showing on the main sheet but I do see the transitioning effect and learning the reasons behind your code as well to help me better understand why it does what it does. I am assuming I would have to use Interior ColorIndex in the main code so they cells that have different colors moves over with the main code and present in the main sheet. Thank you again, greatly appreciated!

 

[Peter_SSs]

Try just substituting this line of code

<del>rCal.Value = ws.Range(sRepCal).Value</del>
ws.Range(sRepCal).Copy Destination:=rCal

 

[bbqsmokeman]

Peter you are a genius! That worked perfectly! Thank you sir!

 

[Peter_SSs]

Glad it worked for you.
Thanks for letting us know.