PDA

View Full Version : [SOLVED] Accessing Excel Tab data from Button



pivotguy
05-12-2016, 07:45 AM
I have two tabs 1. Detail 2. Summary

I have created the buttons in the excel 1. Detail 2. Summary

I am hiding the tabs from the excel. I would like to show the content of detail tab when I click on detail button. Same way when click on summary button show summary content.

Any suggestion where to put the vba code

Paul_Hossler
05-12-2016, 08:06 AM
I am hiding the tabs from the excel.

One worksheet has to be visible (making 3) so put the buttons on that sheet

pivotguy
05-12-2016, 08:22 AM
Yes. I put the button on Sheet#3 . Can you tell me what code I should put in the button?

JKwan
05-12-2016, 09:27 AM
put code into appropriate button and of course your sheet name as well


Worksheets("Detail").Visible = Not Worksheets("Detail").Visible
Worksheets("Summary").Visible = Not Worksheets("Summary").Visible

pivotguy
05-12-2016, 12:59 PM
Paul_Hossler

See the attached test.xlsm file . I created 2 buttons on sheet2. I would like to display content from sheet1 when click on button1
and content from sheet3 , when click on button2. I would like to preseve the same column labels, color, format as it was in the original
sheet1 and sheet3 tabs. The result will display in sheet2.

See my code is somewhat working but not able to retain the original format. Any suggestion.

Paul_Hossler
05-12-2016, 01:29 PM
Sounds something like this -- see the attachment



Option Explicit
Sub Showsheet1()
Worksheets("Sheet2").Range("A10:M100").Clear
With Worksheets("Sheet1")
Range(.Cells(1, 1), .Cells(13, 5)).Copy ' copy output sheet column 1 to column 6 - Copying Avanir Data
End With

Application.ScreenUpdating = False
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A10").Select
Application.ScreenUpdating = True
End Sub

Sub Showsheet3()
Worksheets("Sheet2").Range("A10:M100").Clear
With Worksheets("Sheet3")
Range(.Cells(1, 1), .Cells(13, 11)).Copy ' copy output sheet column 1 to column 6 - Copying Avanir Data
End With

Application.ScreenUpdating = False
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A10").Select
Application.ScreenUpdating = True
End Sub

pivotguy
05-12-2016, 06:20 PM
Paul_Hossler (http://www.vbaexpress.com/forum/member.php?9803-Paul_Hossler) : Thanks you very much for your time and effort. This issue have been resolved. Many thanks.