Selecting sheet name

[AndreMichielsen]

Hello,

I have a problem with selecting the right sheet with VBA.

I have a workbook with a form made in sheet 1
I have a workbook with all the months of the Year, i.e. January, February and so on.

I made a form in which one can fill some data and in one specific cell the date you want something done.
After completing the formdata, with one push on a button I open another workbook, save the data and close it again.
But in the macro I made I have to mention the specific name of the month to save the data.
All I want is that the month name of the cell in the form is compared to the right sheet name in the other workbook and so save the data in the right sheet, before closing the workbook again.
After that i clear the form and can start fill it again with new data.

What i can't find is how to select the right sheet according to the month name in the cell on the form.

 

[AndreMichielsen]

Do these ranges refer to the Lading sheet:

BolNumber = Range("K4")
DeliveryDate = Range("F6")
Time = Range("J29")
ShipfromName = Range("B15")
Commodity = Range("F4")
CarrierName = Range("B33")
Consignee = Range("B26")
ReleaseNumber = Range("F29")
Unloaddate = Range("F6")
Shippingnotes = Range("B38")

Yes they do, and they work well. My only problem is to automatically select the worksheet with the according month name in the Logistics workbook. The reference is the month name that is written in "F6" on the sheet Bill of Lading. And then write the info on the first empty row.

So basicly : How do i select a sheet with a specific name based on the montname i cell "F6"?

 

[mumps]

Try:

Sub SaveData()
    'Open existing Workbook Logistics and then
    'Writing Bol info to Workbook and close it again
    Application.ScreenUpdating = False
    Dim BolNumber As String, DeliveryDate As Date, Time As String, Commodity As String, Consignee As String, ReleaseNumber As String
    Dim ShipfromName As String, Unloaddate As Date, CarrierName As String, Shippingnotes As String
    Dim srcWS As Worksheet, wsName As String
    Set srcWS = ThisWorkbook.Sheets("Bill of Lading")
    With srcWS
        BolNumber = .Range("K4")
        DeliveryDate = .Range("F6")
        Time = .Range("J29")
        ShipfromName = .Range("B15")
        Commodity = .Range("F4")
        CarrierName = .Range("B33")
        Consignee = .Range("B26")
        ReleaseNumber = .Range("F29")
        Unloaddate = .Range("F6")
        Shippingnotes = .Range("B38")
        wsName = MonthName(Month(.Range("F6")))
    End With
    Workbooks.Open "C:\Users\DCI\OneDrive\Logistics Workbook.xlsx"
    With Sheets(wsName)
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 10).Value = Array(DeliveryDate, Time, BolNumber, ShipfromName, _
            Commodity, CarrierName, Consignee, ReleaseNumber, Unloaddate, Shippingnotes)
    End With
    ActiveWorkbook.Close Savechanges:=True
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

I have posted this problem before on this site but due to circumstances I was not able to react on time to questions from people who would dive into my problem.

Note that there is no time limit on replying back to threads. The old thread was still active and you can respond to it. As per rule 12 here: Message Board Rules, we do not allow duplicate threads on the same question, so I merged your two threads together. In the future, please just keep the same thread going, especially since the last post was from a person trying to help you (so you were not waiting on a reply from the helpers).

Also, when posting VBA code, it is best to use Code Tags so your code is easily readable. There are instructions on how to do that here: How to Post Your VBA Code

Thanks.
The Moderators

 

[AndreMichielsen]

Note that there is no time limit on replying back to threads. The old thread was still active and you can respond to it. As per rule 12 here: Message Board Rules, we do not allow duplicate threads on the same question, so I merged your two threads together. In the future, please just keep the same thread going, especially since the last post was from a person trying to help you (so you were not waiting on a reply from the helpers).

Also, when posting VBA code, it is best to use Code Tags so your code is easily readable. There are instructions on how to do that here: How to Post Your VBA Code

Thanks.
The Moderators

I am soory but I will follow the rules you mentioned.

 

[AndreMichielsen]

Try:

Sub SaveData()
    'Open existing Workbook Logistics and then
    'Writing Bol info to Workbook and close it again
    Application.ScreenUpdating = False
    Dim BolNumber As String, DeliveryDate As Date, Time As String, Commodity As String, Consignee As String, ReleaseNumber As String
    Dim ShipfromName As String, Unloaddate As Date, CarrierName As String, Shippingnotes As String
    Dim srcWS As Worksheet, wsName As String
    Set srcWS = ThisWorkbook.Sheets("Bill of Lading")
    With srcWS
        BolNumber = .Range("K4")
        DeliveryDate = .Range("F6")
        Time = .Range("J29")
        ShipfromName = .Range("B15")
        Commodity = .Range("F4")
        CarrierName = .Range("B33")
        Consignee = .Range("B26")
        ReleaseNumber = .Range("F29")
        Unloaddate = .Range("F6")
        Shippingnotes = .Range("B38")
        wsName = MonthName(Month(.Range("F6")))
    End With
    Workbooks.Open "C:\Users\DCI\OneDrive\Logistics Workbook.xlsx"
    With Sheets(wsName)
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 10).Value = Array(DeliveryDate, Time, BolNumber, ShipfromName, _
            Commodity, CarrierName, Consignee, ReleaseNumber, Unloaddate, Shippingnotes)
    End With
    ActiveWorkbook.Close Savechanges:=True
    Application.ScreenUpdating = True
End Sub

It is the solution to my problem, thank you very much for your help.
Greetings from the Netherlands