Copy info from excel file to a template

Here is a process for you. Open the attached workbook and run the following code. The module will ask you to locate that days data file. The code will then use the template information to create and save a new file with the embedded data. Let us know how it works.

Please Login or Register  to view this content.

Thank you for your expertise in this area. I made an error in my sample data and I forgot there will be a column G, so an adjustment needs to be made to the code. I added it and re-attached the file and posted the example. Is it possible to add a condition so it will not copy any rows that have been colour formatted? I only want data in cells that have a white background. I added a couple more columns to my template as well so please extend the (border format) when the macro runs.

Thx

Again thanks I was able to change the code you wrote myself to make the changes I needed. The only thing remaining is to not copy any of the cells with formatted colour. Would it be a condition say rgb is (0) for white?

Try this update:

Please Login or Register  to view this content.

if you want to instead use the a value such as x in column Q instead of coloring the rows, the code would look like this.

Please Login or Register  to view this content.

Big thanks

Here is the updated code and template. I had to rewrite the formulas to include a dynamic detection of the last column to copy over appropriately. They are still the same formulas.

Please Login or Register  to view this content.

Possible issue: In the sample data, an x is only listed in some items in column 17, "Q". There are no x values in column 16, "P". Are you going to add the value x into column 16 going further? Or did you mean to check column 17 for the x value? But if we do that, after your last request, those lines with x in 17 will never use the new formatting if they are excluded from copying over. I included both lines of code so that you can remove either if you wish. Or let me know and I'll make the update.

Please Login or Register  to view this content.

This update incorporates adding the conditional formatting to column K.

Please Login or Register  to view this content.

Here is the update with the new code and template. The formulas have also been updated to only count to row 148.

Please Login or Register  to view this content.

To convert

Formula:

Please Login or Register  to view this content.

to a dynamic range use

Formula:

Please Login or Register  to view this content.

Many thanks

Your welcome. Have a nice day

Is it possible to update the code to hide/remove the blank cells after the macro ruins? This will bring the table in b150:M170 up to the output table produced when the macro has run.

Sure, I just added one line highlighted in red. Here is the full submodule:

Please Login or Register  to view this content.

thank you once again

Also when the macro runs could you have it turn sheet protection on please. Thx

I slightly rearranged and added the line to protect the sheet below.

Please Login or Register  to view this content.

If you want to add a password to protect the sheet use this line instead of the .protect line:

Please Login or Register  to view this content.

When the macro runs I would like the table it produces to be not locked. My guess is some type of range needs to be written into the code. I noticed that when the macro runs the security settings for all the other cells copy to the new worksheet, so no other code is needed.

Also when the macro runs and creates an array with the data and you enter a value in ((K)>0), it turns the cell red like it should. Then you can put a (Y) in L and change it to green, but when you delete the (y) it stays red, until you click the cell in K. Just curious why that is, shouldn't it change back from green to red automatically when Y is removed.

Set c4 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=ISBLANK(K2)")
c4.Interior.Color = 16777215
Set c1 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(K2>0,L2=""y"")")
c1.Interior.Color = 11854022
Set c2 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=K2=0")
c2.Interior.Color = 11854022
Set c3 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(ISBLANK(L2),K2>0)")
c3.Interior.Color = 10066431

To lock the table cells add the red code line where indicated below.

Please Login or Register  to view this content.

I'm researching why the conditional formatting does not automatically update.

I would like the array that is produced from the macro running to be unlocked. The array is still locked and I want to be able to enter data manually.

That's strange it works now go figure

Is it possible to add two more formatting conditions to the array that is produced when the macro runs. They are as follows:
1. In column 5 (E) if any value is >=1900 then turn the font colour red.
2. In column 6 (F) if any value is >=45000 then turn the font colour red.

As for the formulas for S10 and S11, see if these will do the job.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Here are the changes you came up with for the conditional formats you asked about before. Great job on them.

Please Login or Register  to view this content.

As for the formulas, did you try the solutions in post #36? I based those formulas on column K since that value appears to be the one that designates if any work is still outstanding.

Note: For future reference, this site asks that any code posted in the thread be encircled by code tags. You can do that by highlighting the code and then selecting the # in the menu bar above the text box.

Yes this is where the file name is assigned. Try:

Please Login or Register  to view this content.

include the following line before the .protect:

Please Login or Register  to view this content.

Updates are made and all our previous work has been incorporated. I had added new requests to the file uploaded in post #40, and since that was an older file code, some of the previous enhancements were left out. This code addresses all your questions to date. One question you had was where would the new file be created. It will always be created in the file where this template resides.

Please Login or Register  to view this content.

Here are the updated formulas. Counta counts cells if they are not empty, and since the fields in Col A have a formula in them, they count those cells and include the blank space in Col M as a unique item. So instead let's use the Count function that counts numbers.
U10:

Formula:

Please Login or Register  to view this content.

U11:

Formula:

Please Login or Register  to view this content.

U157:

Formula:

Please Login or Register  to view this content.

U158:

Formula:

Please Login or Register  to view this content.

To fix the hiding too many rows, update this line of code:

Please Login or Register  to view this content.

I think U 157 has to be counta since I want to know how many emp's are on task and how many worked in dept. See attached

You're right. I think that applies to U158 too. I got carried away with the change for the previous formulas.

Here are the formulas with a dynamic line count within the 152-170 table. That's what I missed before.

U157:

Formula:

Please Login or Register  to view this content.

U158:

Formula:

Please Login or Register  to view this content.

Yes, you're in luck. With O365, you can use the Filter and choose functions. Make sure all the contents of range A172 to M191 is cleared. Then in A173 enter

Formula:

Please Login or Register  to view this content.

That was fun.

Here is the code with updates. I left the formula solution in place since column G is not needing a formula. I included unlocking the cells in the worksheet change code so that protection continues to work after a worksheet change executes. I did shift the bottom table over by one column. I made sure all the formatting for copied over table areas are aligned as well.

Please Login or Register  to view this content.

This works thank you with my conditional formatting. Nothing else required

It looks like "Sample source # 2" had data previously in row 75. Since that data was cleared and the row not deleted, Excel sees it as a row that should be counted in the data, even though it is blank. If you were to select and delete that row and rerun the code, that final empty row will not show up in the data transfer. System generated source files should not have this scenario.

To get the file name to appear like:

DB Monday, March 27, 2023

Update the Filename line to:

Please Login or Register  to view this content.

Thank you for the quick reply

Your request, to obtain the next value of column M when a "w" is listed in column 16, can be achieved by adding the code highlighted in red into the original code.

Please Login or Register  to view this content.

Thank you

I didn't realize you still needed the "RUN..." code in that row, below is how you add it in red. For the protection, that code is written in below as well in green.

Please Login or Register  to view this content.

Thank you!

Thank you

Here is the code with the additional requests:
Forgot to mention to have code rerun auto hide. When a user adds or deletes a row. I decided that the source would always have enough data so I do not require k +2 to add 2 rows. The two tables can be tight to one another.
So could it be if you hit control A for add a row and control D to delete a row?
Also renumber rows in column A ascending order (1-21) and copy 'run/sa' in column 'r'. Also correct any rows that the height gets changed due to pasting. I would like the row height to be 15
For delete a row the user would hit control D and a message box would come up select what row to delete. The user would then delete the row and the code would adjust count in column A.

* I also noted that the last template that was shared moved the bottom table down by two rows. I updated the code to update the data on the sheet correctly.

Please Login or Register  to view this content.

Here is the code without the print lines. The RUN S/A does get updated when a line is added with this code.

Please Login or Register  to view this content.

Here is the code with the change to prevent blue/yellow formatting applied to newly added row. I also added to the inputbox the selected row number.

Please Login or Register  to view this content.

This code includes an update to limit row additions to rows 3 to 148 on the template, highlighted in red.

Please Login or Register  to view this content.

Thank you

For the Password issue, you only need to add the password to the unprotect line. That line should look like:

Please Login or Register  to view this content.

As for the error, I can't duplicate the error. Since we have an error trapping line, the error notice may not be telling you what the actual issue is. Comment out the trap line (Put an ' in front of the On Error GoTo Trap line) temporarily with an apostrophe and then you will see where the error is located.

Yes, there is a way. Try adding the following redim line into the code:

Please Login or Register  to view this content.

Thank you!

Alright, here are the updates. New lines highlighted in red. Let me know if you have any questions. The only possible issue I can foresee is if the new lines plus the data add up to greater than 148. Do you think that might happen?

Please Login or Register  to view this content.

Here are the updates, in red. The code in blue is what closes the VBA code window.

Please Login or Register  to view this content.

FYI, this thread has apparently reached its length limit, so new request will probably need a new thread.

Here is another line to update. Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Thank you sir