Excel VBA - Assigning macro to button.

[grin2000]

I have 4 colums named Yr1 Yr2 Yr3 and 3yr Summary all from A to D. Nex
to those colums starting from E are hidden colums all having mor
datailed info from each of the 12 months of that certain year. What
don't know how to do is to assign the correct macro that when I clic
on one of those buttons (for example Yr1) I want it to go straight t
those 12 colums with each of the months but I want it to show th
"view" of all 12 months on 1 page so that when someone prints it ou
just those 12 months will be printed out. I also want all those
buttons to stay in place and not go away once I click on them. So i
someone can help me with the code for the macro that can send m
straight to the detailed 12 months "view" once I click on the button
appreciate it

 

[Greg Wilson]

It's not very likely I've fully understood your request. I
hope the following attempt will at least get the ball
rolling.

Quote 1:
"I have 4 colums named Yr1 Yr2 Yr3 and 3yr Summary all
from A to D."

Quote 2:
"What I don't know how to do is to assign the correct
macro that when I click on one of those buttons (for
example Yr1) I want it to go straight to those 12 colums
with each of the months ..."

My take on the above Quotes 1 and 2 is that Columns A
through D are not named ranges but instead each have a
button at the top with their captions
being "Yr1", "Yr2", "Yr3" and "3Yr Summary" respectively.

Quote 3:
"Next to those colums starting from E are hidden colums
all having more datailed info from each of the 12 months
of that certain year."

My take on Quote 3 is that:
i) Columns E through P contain the detailed info for each
of the months for Yr1.
ii) Columns Q through AB contain the detailed info for
each of the months for Yr2.
iii) Columns AC through AN contain detailed info for each
of the months for Yr3.

My code makes the following assumptions:
i) The sheet you refer to is named "Year Info".
ii) The sheet you want the data transferred to is
named "Report".
iii) Cells A1, B1 and C1 each have a command button (or
some other shape object) contained within the boundaries
of the cell and that the below macro is assigned to these
buttons. To demo the code, ensure that the top-left corner
of each button is contained within the cell.
iv) You want the data to be transferred to Columns A
through L of the "Report" sheet.
v) You want to set the print area to this range and to
activate the sheet.

Sub TransferYearData()
Dim C As Range, Rng1 As Range, Rng2 As Range
Dim Rw As Long, Col As Integer

With Sheets("Year Info")
Set C = .Shapes(Application.Caller).TopLeftCell
Col = C.Column
Rw = .UsedRange.Rows.Count
Set Rng1 = .Cells(1, Col * 12 - 7).Resize(Rw, 12)
End With

With Sheets("Report")
.Columns("A:L").ClearContents
Set Rng2 = .Cells(1, 1).Resize(Rw, 12)
Rng2.Value = Rng1.Value
.PageSetup.PrintArea = Rng2.Address
.Activate
End With

End Sub

Regards,
Greg

 

[grin2000]

Thanks Greg. Your subroutine worked just perfectly. Thanks agai