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.

 

[Oaktree]

Welcome to the board.

If your form has an Excel date in say cell D5, try something like:

Sheets(Format(Sheets("Form Sheet Name").Range("D5").Value,"mmmm")).Select

Since Sheets("Form sheet Name").Range("D5") has an Excel date, using the Format function will convert it into the months that your sheets are named.

 

[AndreMichielsen]

Sorry for my late reaction.
I put in the line that you proposed but I it doesn't work.
So I copied a part of my VBA code.
I work with a workbook 1 with in it a form and thus the date cell in F6
After finishing the form I want to save the information in an other workbook on the sheet with the according monthname
Now I change the macro every month as you can see in the formula but i would like the macro to choose the right month.
Hope you can help me.

André


'Workbook 1 contains the form in which the date cell

Dim DeliveryDate As Date
DeliveryDate = Range("F6")

Workbooks.Open "C:\Users\OneDrive\BOL sheets\Logistics 2021 Workbook.xlsx"

WorkSheets(Format(Sheets("Form sheets Name").Range("F6").Value,"mmmm").Select

Worksheets("May").Select
Worksheets("May").Range("A1").Select

If Worksheets("May").Range("A1").Offset(1, 0) <> "" Then
Worksheets("May").Range("A1").End(xlDown).Select
End If

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = DeliveryDate
'etc....

 

[Oaktree]

OK, "Form sheets Name" was intended to be the name of the actual sheet in the source file.

But, more importantly, I think you'll find it much easier to switch between the two files if you assign variables to them.

For example:

Sub CopyToOtherWorkbook()
Dim shtSourceSht As Worksheet
Dim wbkDestination As Workbook


Set shtSourceSht = ActiveSheet
Set wbkDestination = Workbooks.Open("C:\Users\OneDrive\BOL sheets\Logistics 2021 Workbook.xlsx")

With wbkDestination.Sheets(Format(shtSourceSht.Range("F6").Value, "mmmm"))

'do some stuff

End With


End Sub

Here, you'll note that I replaced the "Form sheets Name" idea with a reference to the active sheet instead. That part is different, but the idea of envoking Format(reference cell, "mmmm") is the crux of the suggestion anyway. That part is the same.

 

[AndreMichielsen]

Thank you again but the macro always stops at the line "With wbkDestination.Sheets( .. " )and it shows the remark ;"This property or method is not supported by this object"
I tried everything but I have no idea what to do next.

 

[Oaktree]

When you get the debug error, what does

 ?shtSourceSht.Range("F6").Value

return in the immediate window of VBE?

 

[AndreMichielsen]

Dear members

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.

I have to workbooks,
Workbook 1 is named BOL and contains 2 worksheets.
Workbook 2 is named Logistics and contains 12 worksheets, one for every month

In Workbook 1 the first worksheet is named Client list and that has all kind of address information.
The second worksheet is called Lading and is made as a form that you fill with all kind of information and the address information that we retrieve from client list. This worksheet has also a date field that we fill with a certain date.
After we finished the form we push a button and with that we save this form as pdf, as excel and it opens Workbook 2 (Logistics) and opens a Sheet and save all the data from this form on the first empty row.
Then it closes this Workbook 2.

What I can’t make is that, depending of the month name in the form of the sheet Lading, Workbook 2 opens on the worksheet with the right month name and save the information there. So if on the Lading sheet the month is May, on Workbook 2 the worksheet with the month name May is opened and the info saved there on the first empty row
I Hope somebody can help me,

Greetings from the Netherlands.

At this moment I have this formula made in VBA:

'Open existing Workbook Logistics and then
'Writing Bol info to Workbook and close it again

Dim BolNumber As String, DeliveryDate As Date, Time As String, Commodity As String, Consignee As String, ReleaseNumber As String, AccountNumber As Integer, AccountName As String, ShipfromName As String, ShipfromAddress As String, ShipfromTown As String, ShipfromPostalCode As String, Unloaddate As Date, ShiptoName As String, ShiptoRelease As String, ShiptoAddress As String, ShiptoTown As String, ShiptoPostalCode As String, CarrierName As String, CarrierAddress As String, CarrierTown As String, CarrierPostalCode As String, LastCommodityTransported As String, Remarks As String, Shippingnotes As String

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")

Workbooks.Open "C:\Users\DCI\OneDrive\ Logistics Workbook.xlsx"
Worksheets("July").Select
Worksheets("July").Range("A1").Select
If Worksheets("July").Range("A1").Offset(1, 0) <> "" Then
Worksheets("July").Range("A1").End(xlDown).Select
End If

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = DeliveryDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Time
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = BolNumber
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ShipfromName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Commodity
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CarrierName
ActiveCell.Offset(0, 3).Select
ActiveCell.Value = Consignee
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ReleaseNumber
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = DeliveryDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Shippingnotes

ActiveWorkbook.Close Savechanges:=True

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your Client List and Lading sheets and also of one of the month sheets from workbook 2. Alternately, you could upload a copy of your two files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[AndreMichielsen]

VDB Bill of Lading 01-07-21 used CA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	Id	Short Name	Name	City	State	Country	Origin/Destination	Inactive	Address Line 1	Address Line 2	Zip/Postal Code	Currency	Trader/Salesrep	Contact Name	Phone	Ext	Fax No	Toll Free	Mobile/Cell	Email	Web Address	GST/VAT/ABN No	A/R Terms	Description	A/P Terms	Description	Last Changed	Changed By	GST Exemption No	PST/HST Exemption No	Tax 3 Exemption No
2	1000	MurrayK	Kevin Murray	Walton	ON	Canada		N	82009 Manley Line, RR#4	Huron East	N0K 1Z0	CND		Kevin Murray	519-345-2199				519-301-1054	kmurray@tcc.on.ca							9-12-2020 12:38	NADINE	Exempt
3	1001	Belmont Fa	Belmont Farm Supply Inc	Belmont	ON	Canada		N	14000 Belmont Road, P.O.Box 310		N0L 1B0	CND	Ron Van Der Burgt	Belmont Farm Supply Inc	519-644-1650		519-644-1795		519-281-9541	shosford@belmontfarmsupply.com							9-12-2020 12:39	NADINE	Exempt
4	1002	FroudJ	John Froud	St. Marys	ON	Canada		N	5604 Line 12, RR#1		N4X 1C4	CND		John Froud	519-671-1188					bottlebush@quadro.net							9-12-2020 12:39	NADINE	Exempt
5	1003	VanNynP	P & M Van Nynatten Inc	Sebringville	ON	Canada		N	4901 Line 39		N0K 1X0	CND		P & M Van Nynatten Inc	519-393-5731					petevn@hotmail.com							15-7-2020 12:57	ELLEN	Exempt
6	1004	Steunebrin	Lars C Steunebrink	Kirkton	ON	Canada		N	42540 Huron Street		N0K 1K0	CND		Lars C Steunebrink	519-274-4370					larssteunebrink@hotmail.com							7-12-2020 11:22	NADINE	Exempt
7	1005	LindnerB	Bradilee Farms Ltd c/o Brad Lindner	Sebringville	ON	Canada		N	4633 Road 130, RR#1		N0K 1X0	CND		Bradilee Farms Ltd c/o Brad Lindner	519-393-5728		519-395-5728		519-276-1116	Bradileefarms@yahoo.ca							9-12-2020 12:40	NADINE	Exempt
8	1006	DeWetering	DeWetering Disposal Ltd.	Sebringville	ON	Canada		N	4618 Road 145, RR#1		N0K 1X0	CND		DeWetering Disposal Ltd.	519-393-5250					deweteringdisposal@gmail.com							9-12-2020 12:41	NADINE	Exempt
9	1007	QT Info Sy	QT Info Systems, Inc	Chicago	IL	United States		N	141 West Jackson Blvd	Suite 1220 A	60604	CND		QT Info Systems, Inc													21-7-2020 12:50	ELLEN
10	1008	Tire Craft	Tire Craft Honeywood Tire Ltd	Stratford	ON	Canada		N	42 Dunlop Place	RR#3	N5A 6S4	CND		Tire Craft Honeywood Tire Ltd	519-273-3111		519-273-3123										22-10-2020 13:28	BKA	Exempt
Clientlist_Feb_25_2021

VDB Bill of Lading 01-07-21 used CA.xlsm
	F
6	donderdag, apr 29, 2021
Bill of Lading

Logistics Workbook 2021 AM.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Time	BOL #	Ship from Name	Commodity	Freight Carrier Name	Freight Rate	Contract#	Consignee	PO# / Release #	Unload Date	Shipping Notes
2	26-03-2021		69	V.D.B. Grains LTD	Corn	0			0		26-03-2021
3	26-03-2021	11	69	V.D.B. Grains LTD	Corn	0			0		26-3-2021
4	26-03-2021		70	V.D.B. Grains LTD	Corn	0			0		26-03-2021
5	27-03-2021	25 mar	71	V.D.B. Grains LTD	Corn	Lars C Steunebrink			John Froud	fff	27-03-2021	kkkk
6
March

 

[mumps]

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")