Hello, I am new to this forum ( indeed, new to any Excel-forum ) ...
I have a worksheet containing a list of employees ("Employees"), which includes, inter alia; first name (Column B), last name (Column C), job title (Column D) and employee number (Column A). The names are self-explanatory while the (unique) employee numbers are four digits in length (e.g., 0012, 1025, 5000, 8016, etc.). Employees with a number <8000 work in one location, while those with an employee number >=8000 are attributable to another place of business. As we often hire drivers (Column D), the employee worksheet ("Employees") is dynamic.
What I am looking to do is apply Visual Basic so that I can intermittently use the revised "Employees"-worksheet to update a table containing these "drivers" in another worksheet. This second worksheet ("Accidents") contains two lists of drivers, who work in separate locations, Location A and Location B, and are accordingly "tagged" as such with the aforementioned employee number).
So, here is an abbreviated list of my challenges :
FIRSTLY, although I am able to locate the corresponding drivers and location using :
If Worksheets("Employees").Cells(i, 4).Value = "Driver" And Worksheets("Employees").Cells(i, 1).Value < 8000 Then
Worksheets("Employees").Cells(i, 2).Copy
... I am unable to concatenate first and last names, then copy (so that I can subsequently paste these into "Accidents").
SECONDLY, as the list of drivers becomes longer with time, it is, of course, requisite that the VBA-code "count" the number of entries that have been copied and, accordingly, compare this sum to the number of existing rows in the "driver-table" within the "Accidents" sheet and subsequently "add" rows to the driver-table within "Accidents", if necessary.
Furthermore, as the driver-table(s) within "Accidents" have additional columns, I can not simply "add" rows: The VBA-code needs to copy extant rows, including formulae and formatting, yet excluding other entries (i.e., the name and number of accidents per month in any copie rows) prior to pasting/adding these rows. Once inserted, the code will be required to subsequently paste the concatenated, individual drivers. Notwithstanding using an automated sort function once pasted.
That's a lot in one breath. I hope/trust you can follow my train-of-thought.
Better than a thousand days of diligent study is one day with a great (forum).
- Japanese Proverb
Any assistance is highly appreciated.
waltherk
I have a worksheet containing a list of employees ("Employees"), which includes, inter alia; first name (Column B), last name (Column C), job title (Column D) and employee number (Column A). The names are self-explanatory while the (unique) employee numbers are four digits in length (e.g., 0012, 1025, 5000, 8016, etc.). Employees with a number <8000 work in one location, while those with an employee number >=8000 are attributable to another place of business. As we often hire drivers (Column D), the employee worksheet ("Employees") is dynamic.
What I am looking to do is apply Visual Basic so that I can intermittently use the revised "Employees"-worksheet to update a table containing these "drivers" in another worksheet. This second worksheet ("Accidents") contains two lists of drivers, who work in separate locations, Location A and Location B, and are accordingly "tagged" as such with the aforementioned employee number).
So, here is an abbreviated list of my challenges :
FIRSTLY, although I am able to locate the corresponding drivers and location using :
If Worksheets("Employees").Cells(i, 4).Value = "Driver" And Worksheets("Employees").Cells(i, 1).Value < 8000 Then
Worksheets("Employees").Cells(i, 2).Copy
... I am unable to concatenate first and last names, then copy (so that I can subsequently paste these into "Accidents").
SECONDLY, as the list of drivers becomes longer with time, it is, of course, requisite that the VBA-code "count" the number of entries that have been copied and, accordingly, compare this sum to the number of existing rows in the "driver-table" within the "Accidents" sheet and subsequently "add" rows to the driver-table within "Accidents", if necessary.
Furthermore, as the driver-table(s) within "Accidents" have additional columns, I can not simply "add" rows: The VBA-code needs to copy extant rows, including formulae and formatting, yet excluding other entries (i.e., the name and number of accidents per month in any copie rows) prior to pasting/adding these rows. Once inserted, the code will be required to subsequently paste the concatenated, individual drivers. Notwithstanding using an automated sort function once pasted.
That's a lot in one breath. I hope/trust you can follow my train-of-thought.
Better than a thousand days of diligent study is one day with a great (forum).
- Japanese Proverb
Any assistance is highly appreciated.
waltherk
