VBA for daily Trakker

[Mumgirl]

Hi All,



Wish you all, Happy new year, Hope you are doing good !!!

I am new on MrExcel Forum, looking for VBA for few tasks

First Task – One-time activity

I am preparing the workforce daily tracking sheet for all our partners, we will send this file by mail to the partners.

Once they open this file, they will fill details in Starting B1 to B4, there is one filed “Pin to Transfer Data” A5, here they will put their desired pin – will explain this bit letter

Starting B7 to B26 they will write Technician Name, I want to have a macro to create (Add) sheets based on a list provided in B7:B26.

I want to prevent macro in duplicating sheet however user can add technician name to list and macro should be able to create sheet based on newly added name in range B7:B26

Once a sheet is created, I want to copy mater data format from a sheet called hidden – this data should come with formatting and all data validations

In home tab apart from A1:E30 Rest cells should be protected with Password ( Password=1234)

Daily Monitoring Format.xlsb
	A	B	C	D	E
1	Region	North
2	Branch	Calcutta
3	ASC Name	Kaka Electronics
4	GCS Code	G2345562
5	Pin to Transfer Data
6	T#	Name
7	T1
8	T2
9	T3
10	T4
11	T5
12	T6
13	T7
14	T8
15	T9
16	T10
17	T11
18	T12
19	T13
20	T14
21	T15
22	T16
23	T17
24	T18
25	T19
26	T20
27
28
29
Home

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:B26	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B1	List	East,North,South,West
B2	List	Ahmedabad,Bangalore,Bhubaneswar,Calcutta,Chandigarh,Chennai,Delhi,Ghaziabad,Guwahati,Hyderabad,Indore,Jaipur,Kochi,Lucknow,Mumbai,Pune

Second Task
Once Technician sheets are created, the user will fill the required details in each technician list on daily basis.

I want to have a macro button on each sheet by clicking this button macro to ask to enter “Pin to Transfer Data” using input box and match input data with cell B5 in Home tab, this is just to prevent accidental transfer of data

If the input value is matching with B5 copy date dynamically ( sheet may have less data – macro to check data Cell B2 to last filled row and copy ) and paste it in “ASC Summary” sheet in column E Date, if not matching show massage, “Please enter correct code”,

Macro to append data on each transfer.

The region, Branch, GCS code, Technician Name data is not available in Technician sheet, macro to take input of Region, Branch, GCS code from Home tab and technician name from sheet name from where data was transferred. Macro to match Row with data copied from sheet to avoid any mismatch.



I don’t know how difficult this activity but am looking forward for support from experts.

Thanks
MumGirl

 

[EFANYoutube]

Hi MumGirl,

The bellow code should get you started
It takes all the entered cells in home B7:B26 and creates sheets with the cell value as the sheet name, but ignores duplicates
Then if it creates a sheet it copies and pastes the whole hidden sheet to the newly created sheet

Sub addSheets()

For Each cell In ThisWorkbook.Sheets("Home").Range("B7:B26").Cells
    If cell <> "" Then
        If DuplicateSheet(cell.Value) = False Then
            ThisWorkbook.Worksheets.Add
            ActiveSheet.Name = cell.Value
            ThisWorkbook.Sheets("Hidden").Cells.Copy ActiveSheet.Cells
        End If
    End If
Next cell

End Sub

Function DuplicateSheet(SheetName As String) As Boolean
    DuplicateSheet = False
    For Each sheet In ThisWorkbook.Sheets
        If sheet.Name = SheetName Then
            DuplicateSheet = True
            Exit For
        End If
    Next sheet
End Function

 

[Mumgirl]

Hi MumGirl,

The bellow code should get you started
It takes all the entered cells in home B7:B26 and creates sheets with the cell value as the sheet name, but ignores duplicates
Then if it creates a sheet it copies and pastes the whole hidden sheet to the newly created sheet

Sub addSheets()

For Each cell In ThisWorkbook.Sheets("Home").Range("B7:B26").Cells
    If cell <> "" Then
        If DuplicateSheet(cell.Value) = False Then
            ThisWorkbook.Worksheets.Add
            ActiveSheet.Name = cell.Value
            ThisWorkbook.Sheets("Hidden").Cells.Copy ActiveSheet.Cells
        End If
    End If
Next cell

End Sub

Function DuplicateSheet(SheetName As String) As Boolean
    DuplicateSheet = False
    For Each sheet In ThisWorkbook.Sheets
        If sheet.Name = SheetName Then
            DuplicateSheet = True
            Exit For
        End If
    Next sheet
End Function

Thanks a lot, EFANYoutube, this code is working fine for my first task.

Could you please help me in the second task as well, if not complete at least we can fulfil part of it, I know matching Pin will be a difficult task but is it possible to create code for a copy from the created sheet and append in ASC summary sheet.

Thanks & Regards.

 

[Mumgirl]

Hi All,



Wish you all, Happy new year, Hope you are doing good !!!

I am new on MrExcel Forum, looking for VBA for few tasks

First Task – One-time activity

I am preparing the workforce daily tracking sheet for all our partners, we will send this file by mail to the partners.

Once they open this file, they will fill details in Starting B1 to B4, there is one filed “Pin to Transfer Data” A5, here they will put their desired pin – will explain this bit letter

Starting B7 to B26 they will write Technician Name, I want to have a macro to create (Add) sheets based on a list provided in B7:B26.

I want to prevent macro in duplicating sheet however user can add technician name to list and macro should be able to create sheet based on newly added name in range B7:B26

Once a sheet is created, I want to copy mater data format from a sheet called hidden – this data should come with formatting and all data validations

In home tab apart from A1:E30 Rest cells should be protected with Password ( Password=1234)

Daily Monitoring Format.xlsb
	A	B	C	D	E
1	Region	North
2	Branch	Calcutta
3	ASC Name	Kaka Electronics
4	GCS Code	G2345562
5	Pin to Transfer Data
6	T#	Name
7	T1
8	T2
9	T3
10	T4
11	T5
12	T6
13	T7
14	T8
15	T9
16	T10
17	T11
18	T12
19	T13
20	T14
21	T15
22	T16
23	T17
24	T18
25	T19
26	T20
27
28
29
Home

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:B26	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B1	List	East,North,South,West
B2	List	Ahmedabad,Bangalore,Bhubaneswar,Calcutta,Chandigarh,Chennai,Delhi,Ghaziabad,Guwahati,Hyderabad,Indore,Jaipur,Kochi,Lucknow,Mumbai,Pune

Second Task
Once Technician sheets are created, the user will fill the required details in each technician list on daily basis.

I want to have a macro button on each sheet by clicking this button macro to ask to enter “Pin to Transfer Data” using input box and match input data with cell B5 in Home tab, this is just to prevent accidental transfer of data

If the input value is matching with B5 copy date dynamically ( sheet may have less data – macro to check data Cell B2 to last filled row and copy ) and paste it in “ASC Summary” sheet in column E Date, if not matching show massage, “Please enter correct code”,

Macro to append data on each transfer.

The region, Branch, GCS code, Technician Name data is not available in Technician sheet, macro to take input of Region, Branch, GCS code from Home tab and technician name from sheet name from where data was transferred. Macro to match Row with data copied from sheet to avoid any mismatch.



I don’t know how difficult this activity but am looking forward for support from experts.

Thanks
MumGirl

Hi All,

I have got code for the first task from EFANYoutube, however second task is equally important.

I know my expectations are very high however I come to know a few formulas which can help, you also can suggest in case of any useful formula to make this simple.

My submission is if it is difficult to collect details from the home tab using code we can use if condition, below is sample.

Sharing my origional requirement again to make it simple

Second Task
Once Technician sheets are created, the user will fill the required details in each technician list on daily basis.

I want to have a macro button on each sheet by clicking this button macro to ask to enter “Pin to Transfer Data” using input box and match input data with cell B5 in Home tab, this is just to prevent accidental transfer of data, ( if it is difficult at least we can have prompt "Are you sure to transfer this data to ASC summary sheet" to avoid an accidental transfer.)

If the input value is matching with B5 copy date dynamically ( sheet may have less data – macro to check data Cell B2 to last filled row and copy ) and paste (Append) it in “ASC Summary” sheet in column E Date, if entered code is not matching with B5 show massage prompt should appear, “Please enter correct code”,

Macro to append data on each transfer.

The region, Branch, GCS code, Technician Name data is not available in Technician sheet, macro to take input of Region, Branch, GCS code from Home tab and technician name from sheet name from where data was transferred. Macro to match Row with data copied from sheet to avoid any mismatch.

I know its very difficult task but at least help me with code with possible functionalities.

Daily Monitoring Format.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	To get filled from Home Tab			Fill based on Sheet name from where data is copied
2	Region	Branch	GCS Code	Technician Name	Date	Work order No	W/o Date	Customer Name	Area	Model No	Serial Number	DOP	Warranty Status	Part Code	Part Qty	Appointment Status	Appointment Time	Status of call	Type of call	Amount Collected	Happy Call Status	Happy Call Date	Remarks
3	North	Chandigarh	Kaka Electronics		07-01-21	60-14442662886	07-01-21	Aniruth Abhinandana Bhattacharyya	Thiruvananthapuram	HL1643/00	HL0999999999999	07-01-21	Stock Set	996510076581	1	Yes	3:30 PM	Solved		₹ 750.00	Not Done	07-01-21	Customer out of station
4	North	Chandigarh	Kaka Electronics		07-01-21	60-14442662886	07-01-21		Thiruvananthapuram	HL1643/00	HL0999999999999	07-01-21	EW					Not Attended			Done
5
6
7
8
9
10
11
12
ASC Summary

Cell Formulas
Range	Formula
A3:A12	A3	=IF(E3>0,Home!$B$1,"")
B3:B12	B3	=IF(E3>0,Home!$B$2,"")
C3:C12	C3	=IF(E3>0,Home!$B$3,"")

Cells with Data Validation
Cell	Allow	Criteria
U3:U4	List	Done,Not Done
R3:R4	List	Solved,Pending,Not Attended
S3:S4	List	PR,NPR,Exchange,Demo
P3:P4	List	Yes,No
M3:M4	List	IW,OW,Stock Set,EW

 

[EFANYoutube]

I can help but I have a power cut at the moment, I look tomorrow

 

[sanket_sk]

I can help but I have a power cut at the moment, I look tomorrow

Ok, Thanks for support, will wait.

Thanks & Regards

 

[EFANYoutube]

Just having a look, this is all possible but just need to know, how many columns of the data do you want copied from the tech sheet to the summary? It looks like you just want the date and if so where do you get all the other data from?
If possible, you could send me a Google Drive or Onedrive link to the file so I can test as I go as this is a little more difficult but if not its ok, I'll do my best

 

[Mumgirl]

Hi EFANYoutube,

I want to move date from column B to T this is about column, and rows till data is available.
All drives are restricted in my system hence sharing sheets here only.

Sharing all the three sheets to avoid confusion.


Home Tab-( here we are creating worksheets as per the list provided by the user- you have already shared code for this

Daily Monitoring Format.xlsb
	A	B	C
1	Region	North	User will set
2	Branch	Chandigarh	User will set
3	ASC Name	Kaka Electronics	User will set
4	GCS Code	G2345562	User will set
5	Pin to Transfer Data		User will set
6	T#	Name
7	T1		User to enter Tech Name name
8	T2		User to enter Tech Name name
9	T3		User to enter Tech Name name
10	T4		User to enter Tech Name name
11	T5		User to enter Tech Name name
12	T6		User to enter Tech Name name
13	T7		User to enter Tech Name name
14	T8		User to enter Tech Name name
15	T9		User to enter Tech Name name
16	T10		User to enter Tech Name name
17	T11		User to enter Tech Name name
18	T12		User to enter Tech Name name
19	T13		User to enter Tech Name name
Home

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:B26	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B1	List	East,North,South,West
B2	List	Ahmedabad,Bangalore,Bhubaneswar,Calcutta,Chandigarh,Chennai,Delhi,Ghaziabad,Guwahati,Hyderabad,Indore,Jaipur,Kochi,Lucknow,Mumbai,Pune

Once Worksheets are created user will fill these sheets as per tech name,
I want the macro to create a data transfer button in each sheet, once clicked either input box should ask for Pin and match entered pin with home tab B5 if match macro to cut data from B to Column T till the data is available in rows and move to ASC summary starting Column E to W and append on each move.

Once moved the technician sheet contents should get cleared so that user can enter new details next time.

If Pin matching is dificult at least one prompt asking are you sure to move data to ASC summary?

Daily Monitoring Format.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	SR#	Date	Work order No	W/o Date	Customer Name	Area	Model No	Serial Number	DOP	Warranty Status	Part Code	Part Qty	Appointment Status	Appointment Time	Status of call	Type of call	Amount Collected	Happy Call Status	Happy Call Date	Remarks
2	1	07-01-21	RG-14442662887	07-01-21	Aniruth Abhinandana Bhattacharyya	Thiruvananthapuram	RC4315/789	HL0999999999999	07-01-21	Stock Set	996510076581	1	Yes	3:30 PM	Solved		₹ 750.00	Not Done	07-01-21	Customer out of station
3	2	07-01-21	RG-14442662886	07-01-21		Thiruvananthapuram	RC4315/789	HL0999999999999	07-01-21	EW					Not Attended			Done
4	3
5	4
6	5
7	6
Manoj

Cells with Data Validation
Cell	Allow	Criteria
J2:J7	List	IW,OW,Stock Set,EW
M2:M7	List	Yes,No
O2:O21	List	Solved,Pending,Not Attended
P2:P21	List	PR,NPR,Exchange,Demo
R2:R7	List	Done,Not Done

ASC summary-
Once data copied it will get pasted starting column E to W and append on each move from each sheet.
Region, Branch, CGS code should come from Home tab either with macro or with if the function
And the technician code should get captured from the sheet name from data was moved.

Daily Monitoring Format.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	To get filled from Home Tab			Fill based on Sheet name from where data is copied
2	Region	Branch	GCS Code	Technician Name	Date	Work order No	W/o Date	Customer Name	Area	Model No	Serial Number	DOP	Warranty Status	Part Code	Part Qty	Appointment Status	Appointment Time	Status of call	Type of call	Amount Collected	Happy Call Status	Happy Call Date	Remarks
3	North	Chandigarh	Kaka Electronics	Manoj	07-01-21	60-14442662886	07-01-21	Aniruth Abhinandana Bhattacharyya	Thiruvananthapuram	HL1643/00	HL0999999999999	07-01-21	Stock Set	996510076581	1	Yes	3:30 PM	Solved		₹ 750.00	Not Done	07-01-21	Customer out of station
4	North	Chandigarh	Kaka Electronics	Manoj	07-01-21	60-14442662886	07-01-21		Thiruvananthapuram	HL1643/00	HL0999999999999	07-01-21	EW					Not Attended			Done
5
6
7
8
ASC Summary

Cell Formulas
Range	Formula
A3:A8	A3	=IF(E3>0,Home!$B$1,"")
B3:B8	B3	=IF(E3>0,Home!$B$2,"")
C3:C8	C3	=IF(E3>0,Home!$B$3,"")

Cells with Data Validation
Cell	Allow	Criteria
U3:U4	List	Done,Not Done
R3:R4	List	Solved,Pending,Not Attended
S3:S4	List	PR,NPR,Exchange,Demo
P3:P4	List	Yes,No
M3:M4	List	IW,OW,Stock Set,EW

Hope this clarifies.

Thanks & Regards

 

[EFANYoutube]

Ok, looks like a pretty fun little project, Thanks for the explaination, it has made it very clear now.
I'll get back to you soon with the solution
If I create a workbook are you able to open it and get the code?
If not its all good, I will share and explain the code here

 

[Peter_SSs]

If not its all good, I will share and explain the code here

In any case please share and explain here. Refer to #4 of the Forum Rules, particularly the last two paragraphs.