I have a workbook with 9 sheets that each contain a list of names. Each day the lists are updated. I have a second file with two worksheets that are updated daily based on the names in the first file. The names from 8 sheets go onto one sheet in the second file and the names from 1 sheet go to the second sheet. I would like to automate the population of the second file.
Is this possible?
Thanks
Jeff
We will need much more information to suggest a solution. It would be easier to help and test possible solutions if you could attach a copy of both files. 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). See the yellow banner at the top of this page for instructions to attach your files.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
I have attached the workbooks and a simple flow chart.
Make sure that both workbooks are open. Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook" of the Roll Call file. Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Enter a name in column A of any of the sheets in Roll Call and press the RETURN key or TAB key. The names will be automatically copied to the Data Flow file.
Please Login or Register to view this content.
Great - Thanks for your help!
You are very welcome.
I just found out I need have a couple criteria when copying the data.
1 - In the "Roll Call - Test" file, I only want to copy the name if there is data in the column called Shift. If that cell is blank, I don't copy the name
to the "Honda Security List" file.
2 - In the "Roll Call - Test" file, I don't need to copy the names from 2 sheets - KTH and Nissan. These sheets can be ignored.
How would I go about this?
Do you want the names Under "TEMPORARY ASSOCIATES" or under "ASSEMBLE-RITE ASSOCIATES" in the "HCM" sheet?
The names can start at the top of the column. The cells that say "temporary associates" & "Assemble-Rite Associates" can be removed.
Try:
Please Login or Register to view this content.
I copied this into the module for ThisWorkbook but nothing is being copied.
I tested the macro using the last two files you posted and it works properly. Do the names you are trying to copy have data in the corresponding "SHIFT" cell? Are you using the same two files you posted?
I was typing the data in the wrong order. Operator error on my part. Works great!
If I wanted the names to start at the top of the column on the Security sheet, what would have to be changed?
Thanks
I don't see the Security sheet in either file.
I have attached the Security file.
Ytu:
Please Login or Register to view this content.
Awesome! Thanks again!
My pleasure.
I just realized that when the names are copied to the "HCM" sheet in the Honda Security List file, the data is only populating the first row. Each time the previous name is written over. However it works fine on the "SPS" sheet in the same file.
Replace his line of code:
with this line:Please Login or Register to view this content.
Please Login or Register to view this content.
Awesome! Thanks!
You are very welcome.
Hi,
I've combined the 2 workbooks into 1 to simplify. Is there a way to be able to enter the names first then the data in the shift column for the code to work?
I've attached the updated workbook.
Thanks
Try:
Please Login or Register to view this content.
I didn't explain my question clearly. This revised code copies the cell contents from the "Shift" column. How would I copy the info from the "Name" column?
Thanks
I'm not sure what you mean. The current macro already copies the shift and name from SPS to SPS (2) and the name from the other sheets to HCM.
I don't need the shift copied over, just the name.
Try:
Please Login or Register to view this content.
This is copying the data from the "Shift" column. How would I copy the data from the "Name" column?
Try:
Please Login or Register to view this content.
Awesome! Works great!
Next question - If I copy the names and shift from a different sheet and paste the names & shift into the sheets, how can I get the names to copy to the HCM & SPS (2) sheets if there is data in the shift column?
What is the name of the "different sheet"? Attach an updated file that contains that sheet if necessary.If I copy the names and shift from a different sheet
Last edited by Mumps1; 06-08-2021 at 07:55 AM.
The name of the sheet the info comes from changes every day.
Is the sheet name completely different each day or does it only change in part? If it changes in part, what is the part of the name that remains constant? Is it's position in the workbook always the same and if so, what position is it in? For example, is it always the last sheet? We need some way of identifying the sheet so that it can be excluded from triggering the macro.
The name is completely different each day.
I can't help unless you are able to answer all the questions in Post #34 and the following question as well:
When you copy the names and shift from the different sheet, are the names and shift in columns A and B?
The file name changes in part. Eg: A Shift Roll Call - 6/14/21 to A Shift Roll Call - 6/15/21. However, not all the info is in the sheet everyday. Some days, part of the information comes as text in an email.
The sheets in the file stay in the same order.
The names and shift info are in the columns - A & B.
Try:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks