Selecting sheet name

AndreMichielsen

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Do these ranges refer to the Lading sheet:
VBA Code:
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"?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,867
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Try:
VBA Code:
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
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,927
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Try:
VBA Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,396
Messages
5,769,837
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top