VBA - select worksheet based on cell value (Date) - HELP!

[gcpiller]

Good Evening -

I am very very new to coding and have a problem with which I would very much appreciate help...

I have a spreadsheet with a data set which I have to upload. the data comes in for one month. I want to copy the data from the upload worksheet to a sheet corresponding to the date... So, i need to have a macro that can select a worksheet based on a cell value. Let's say that the date I am using is 9/30/2017. Since i cannot name a worksheet a date, I have chosen to name my worksheets Sep-17, Aug-17 etc... The initial copy and paste is a necessary function because I want the column heading to be the 9/30/2017 date instead of "Current Period" which is how it comes in from the upload.

My problem is that when i try and reformat the date after moving it - the text version = Oct-17 NOT Sep-17. My code is below. Any thoughts would be so very much appreciated. Thank you so so much

sub SelectWorksheet()


Dim strWsName As String
Dim Data As Worksheet


Worksheets("Data").Range("i2").Copy
With Worksheets("Data").Range("h1")
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteValues
End With
Dim DT As Date
DT = Date


With Worksheets("Data").Range("h1")
.NumberFormat = "@"
.Value = Format(DT, "mmm-yy")
End With


strWsName = Sheet1.Range("h1")
Sheets(strWsName).Select
End Sub

 

[frabulator]

Welcome to Mr. Excel!

When pasting code please use the formatting tools located above the text box when typing. This is shown with a "#" icon and uses the following syntax: ( "[ C O D E ] [ / C O D E ]").

To fix your current code replace 'Format(DT, "mmm-yy")' with 'Format(strWsName, "mmm-yy")'. When using DT in this format you are telling the code to use DT as your starting point, not as a format. Because of this you are using the current data instead of 9/30/17.

That aside, I see a lot of code that is not needed. Please try the following. It uses only the string you created and does not require any cell other than I2 to be used. I have commented out lines of code that you do not need. I also through in an error handling incase the page specified was not found.

Sub SelectWorksheet()

    Dim strWsName As String
    
        'Dim Data As Worksheet
    '----Data is never declaired
    
    
    strWsName = Worksheets("Data").Range("i2").Value
    
        'Worksheets("Data").Range("i2").Copy
        'With Worksheets("Data").Range("h1")
        '.PasteSpecial xlPasteFormats
        '.PasteSpecial xlPasteValues
        'End With
        'Dim DT As Date
        'DT = Date
    '--- copy and paste is never a good idea
    '--- instead remove this and just declare
    '--- a string (strWsName). Faster and
    '--- much cleaner.
    

    
    
        'With Worksheets("Data").Range("h1")
        '.NumberFormat = "@"
        '.Value = Format(DT, "mmm-yy")
        'End With
    '--- Above can be replaced with one line
    
    
    strWsName = Format(strWsName, "mmm-yy")
    
        'strWsName = Sheet1.Range("h1")
On Error GoTo Error
    
    Sheets(strWsName).Select
    Exit Sub
    
Error:
    MsgBox ("Worksheet """ & strWsName & """ not found.")
On Error GoTo 0

    
End Sub