vba code to open a hidden worksheet based on cell text value in another worksheet.

[J15491]

I have a macro assigned to a commandbutton to open a specific worksheet base on the text value in cell "AI2" :

Sub ActivateSheet()
Sheets(Sheet2.Range("AI2").Text).Activate
End Sub

I don't know how to write vba to activate a hidden worksheet as I have 40 worksheets in the workbook and only want the dashboard and database worksheet open at all times. Then I need code to paste data from Column "A" in the last row of the database to cell "C12" of the activated worksheet, Column "B" to cell "C14", Column "C" to Cell "C16", etc. My worksheet tabs are named the same as the text value that is entered in cell "AI2". Any help would be appreciated.

 

[Alex Blakenburg]

Do we have 3 sheets to work with or 2 ?
database seems to be the source sheet.
then is there a dashboard sheet AND a hidden sheet
or are we unhiding a hidden sheet and that becomes the dashboard sheet.
Which sheet has sheet name in AI2

Please use sheet names for each sheet, involved in the copy / paste of the data process.
We understand that one of the sheets will change based on the AI2, but just pick a name.

Does the hidden sheet need to be made visible or are we only copying data to/from that sheet ?
You need to unhide it to activate it but you don't need to unhide or activate it if we are just getting or sending data to it.

 

[J15491]

Do we have 3 sheets to work with or 2 ?
database seems to be the source sheet.
then is there a dashboard sheet AND a hidden sheet
or are we unhiding a hidden sheet and that becomes the dashboard sheet.
Which sheet has sheet

The dashboard sheet has a command button to open a userform (allows user to enter data into the text boxes). That data populates to the database worksheet. The dashboard also has a command button to start a new month and print a monthly HC report. It also contains a monthly expense budget that updates expenses as data is entered into the database. I would like this worksheet to stay open since the open userform command button is on the sheet. The user enters the data and once they hit the save button the information populates to the database worksheet.
Cell AI2 is on the database sheet. If AI2 contains CG then I need the hidden sheet (“CG”) to be visible, populate the data from the cells in the last row of the database (Col “A” to cell “C12”, Col. “B” to “C14”, Col.”C” to “C16”, etc.) of the activated worksheet, print 2 copies and save to a monthly folder in this case, August. The worksheet names are CC, CG, DE, DOM and TD.

Hope I explained it well enough.

 

[Alex Blakenburg]

See if this answers your original question.
I did the transferring of the values first, just so show that the sheet did not need to be visible to do that part.

Sub CopyLastRowOfDataSheet()

    Const srcHRSheetName As String = "AI2"
    
    Dim wb As Workbook
    Dim dbSht As Worksheet
    Dim hrSht As Worksheet
    
    Dim dbLastRow As Long
    
    Set wb = ActiveWorkbook
    Set dbSht = wb.Worksheets("database")
    dbLastRow = dbSht.Cells(dbSht.Rows.Count, "A").End(xlUp).Row
    
    Set hrSht = wb.Worksheets(dbSht.Range(srcHRSheetName).Value)
    
    With hrSht
        .Cells(12, "C") = dbSht.Cells(dbLastRow, "A").Value2
        .Cells(14, "C") = dbSht.Cells(dbLastRow, "B").Value2
        .Cells(16, "C") = dbSht.Cells(dbLastRow, "C").Value2
        .Visible = xlSheetVisible
        .Activate
        .Cells(1, "A").Select
    End With

End Sub

 

[J15491]

See if this answers your original question.
I did the transferring of the values first, just so show that the sheet did not need to be visible to do that part.

Sub CopyLastRowOfDataSheet()

    Const srcHRSheetName As String = "AI2"
   
    Dim wb As Workbook
    Dim dbSht As Worksheet
    Dim hrSht As Worksheet
   
    Dim dbLastRow As Long
   
    Set wb = ActiveWorkbook
    Set dbSht = wb.Worksheets("database")
    dbLastRow = dbSht.Cells(dbSht.Rows.Count, "A").End(xlUp).Row
   
    Set hrSht = wb.Worksheets(dbSht.Range(srcHRSheetName).Value)
   
    With hrSht
        .Cells(12, "C") = dbSht.Cells(dbLastRow, "A").Value2
        .Cells(14, "C") = dbSht.Cells(dbLastRow, "B").Value2
        .Cells(16, "C") = dbSht.Cells(dbLastRow, "C").Value2
        .Visible = xlSheetVisible
        .Activate
        .Cells(1, "A").Select
    End With

End Sub

Okay it opened the hidden worksheet, no information was populated to the active worksheet.

 

[Alex Blakenburg]

It would be so much easier if you used sheet names.
What are you calling the active sheet (what it the sheet name you are referring to) ?

Show me your database sheet and is it actually called that ?
XL2BB preferred but at least a screenshot showing row & column references and data range
and the sheet name.

 

[J15491]

It would be so much easier if you used sheet names.
What are you calling the active sheet (what it the sheet name you are referring to) ?

Show me your database sheet and is it actually called that ?
XL2BB preferred but at least a screenshot showing row & column references and data range
and the sheet name.

 

[J15491]

Thank you for your help so far. I'm sure how to do the xl2bb format so I sent you a link to the file. It's still a work in progress.

Rent_Utility Exp Rpt WorkInProgress_8_8_21_version3.xlsm

Microsoft Excel Workbook

1drv.ms

 

[Alex Blakenburg]

The database sheet data is in a table and that table has blank rows.
I was using xlup which in the case of a table finds the last row of the table and not the last row with data in it.
I have modified the code below to use Find to work out the last row.

There are also change events in a number of your sheets. I don't think these are actually doing anything useful for you and you might want to remove them.
However I have added a lines to turn EnableEvents off & back on.

PS: The sample Vendor in the workbook in your link did not actually have a sheet set up for it, so just keep that in mind.

Sub CopyLastRowOfDataSheet()

    Application.EnableEvents = False    
    
    
    Const scrHRSheetName As String = "AI2"
    
    Dim wb As Workbook
    Dim dbSht As Worksheet
    Dim hrSht As Worksheet
    Dim dbTblName As String
    Dim dbLastrow As Long
    
    Set wb = ActiveWorkbook
    Set dbSht = wb.Worksheets("database")
    dbTblName = "TableRegister"
    dbLastrow = dbSht.ListObjects(dbTblName).ListColumns(1).Range.Find( _
               What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    
    Set hrSht = wb.Worksheets(dbSht.Range(scrHRSheetName).Value)
    
    With hrSht
        .Cells(11, "C") = dbSht.Cells(dbLastrow, "A").Value2
        .Cells(14, "C") = dbSht.Cells(dbLastrow, "B").Value2
        .Cells(16, "C") = dbSht.Cells(dbLastrow, "C").Value2
        .Cells(18, "C") = dbSht.Cells(dbLastrow, "C").Value2
        .Cells(18, "F") = dbSht.Cells(dbLastrow, "C").Value2
        .Cells(23, "C") = dbSht.Cells(dbLastrow, "C").Value2
        .Cells(31, "C") = dbSht.Cells(dbLastrow, "C").Value2
        .Visible = xlSheetVisible
        .Activate
        .Cells(1, "A").Select
    End With
    
    Application.EnableEvents = True 
    
End Sub

 

[J15491]

The database sheet data is in a table and that table has blank rows.
I was using xlup which in the case of a table finds the last row of the table and not the last row with data in it.
I have modified the code below to use Find to work out the last row.

There are also change events in a number of your sheets. I don't think these are actually doing anything useful for you and you might want to remove them.
However I have added a lines to turn EnableEvents off & back on.

PS: The sample Vendor in the workbook in your link did not actually have a sheet set up for it, so just keep that in mind.

Sub CopyLastRowOfDataSheet()

    Application.EnableEvents = False   
   
   
    Const scrHRSheetName As String = "AI2"
   
    Dim wb As Workbook
    Dim dbSht As Worksheet
    Dim hrSht As Worksheet
    Dim dbTblName As String
    Dim dbLastrow As Long
   
    Set wb = ActiveWorkbook
    Set dbSht = wb.Worksheets("database")
    dbTblName = "TableRegister"
    dbLastrow = dbSht.ListObjects(dbTblName).ListColumns(1).Range.Find( _
               What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

   
    Set hrSht = wb.Worksheets(dbSht.Range(scrHRSheetName).Value)
   
    With hrSht
        .Cells(11, "C") = dbSht.Cells(dbLastrow, "A").Value2
        .Cells(14, "C") = dbSht.Cells(dbLastrow, "B").Value2
        .Cells(16, "C") = dbSht.Cells(dbLastrow, "C").Value2
        .Cells(18, "C") = dbSht.Cells(dbLastrow, "C").Value2
        .Cells(18, "F") = dbSht.Cells(dbLastrow, "C").Value2
        .Cells(23, "C") = dbSht.Cells(dbLastrow, "C").Value2
        .Cells(31, "C") = dbSht.Cells(dbLastrow, "C").Value2
        .Visible = xlSheetVisible
        .Activate
        .Cells(1, "A").Select
    End With
   
    Application.EnableEvents = True
   
End Sub

Thank you Alex, I will add this code. I have some code that I need to remove, just thought I would remove it after I get this all working properly. Save lots of notes so I would know what to remove, etc.

Again, thank you for all your help and patience.

Joseph Jalbert