Capture cells from one sheet into another. Hide/Unhide Sheets

Click GO ADVANCED and use the paperclip icon to post up your workbook. We'll take a look for you.

Hi,

One solution.

Assumes your from sheet is called 'Front'. Change the second macro as appropriate if not.

1. Create a list of month names and name the range 'months'
2. Create a validation drop down cell and use the list parameter and specify '=months'. And name this cell 'Selection'
3. Create the two macros shown below in the VBE
4. On the front sheet add a button and make it run the 'ShowMonth' macro
5. On the month sheets add a button and make them run the 'ShowFrontSheet' macro. (Create a button on one of the months and then just copy it and paste to the other 11 sheets).

Sub ShowMonth()
    Dim x As Integer
    Application.ScreenUpdating = False
    
    For x = 1 To Worksheets.Count
        Sheets(x).Visible = True
    Next x
    
    For x = 1 To Worksheets.Count    '12
        If Sheets(x).Name <> Range("selection") Then Sheets(x).Visible = False
    Next x

End Sub
Sub ShowFrontSheet()
    Dim x As Integer
    
    For x = 1 To Worksheets.Count    '12
        If Sheets(x).Name <> "Front" Then
            Sheets(x).Visible = False
        Else
            Sheets(x).Visible = True
        End If
    Next x

End Sub

HTH

The attached workbook shows another approach. There are several ways to accomplish your task.
J

OK...I read your original post and I assumed (not good) that you only wanted to view the selected month. I didn't believe you really wanted to import the data from the selected month to the "Front" sheet. My mistake and I apologize.
Now, may I ask why? What is the purpose of copying the data from the selected month to the front sheet? Knowing what you plan to do with this information may help me in developing the procedure.
J

I had some time today (Saturday) and looked at your request. Play with the attachment and see if it does what you need.

Hope this helps. J

Originally Posted by jj72uk

I would post this in the general excel help forum but if i do i guess it would be moved here.

There is 2 parts to my question and I will explain them as best as i can.

1. I have a front sheet and 12 other sheets ( 1 for each month of the year) they all have the same layout. On my front sheet I want to create a button which will capture the sheet onto my front sheet for the current month, whether this means to make 12 buttons to choose which month is displayed on the front sheet or one that detects the date by system time i dont mind.

2. These 12 sheets will ideally be hidden and what I am wanting is again, on the front sheet 12 buttons for each sheet to bring up the corresponding hidden sheet so they can be viewed, and then on each of the 12 sheets another button which will hide them and return the user to the front sheet.

I hope ive explained myself properly.

Any help?

Take a look at this. No buttons, just hyperlinks.
http://www.excelforum.com/excel-prog...-on-index.html

I see you solved this issue

is there any way when the sheet closes it defaults back to a particular sheet?

Did you solve this one

when it copies it doesn't keep the same cell sizes etc

J