Replies

Yep… sorry… just attempting to streamline as much as possible… eliminate keystrokes when I can, etc… thought is was worth a try!!
Thanks again Hans!
LJM

As it stands now, I have a pivot table that I refresh each month (with the new data) and then do a quick copy & paste, then press a command button to run the “fill with above” macro, so it doesn’t take but 15 seconds… I’m thinking that in Excel I’ve automated it as much as I can. I thought it was worth asking to see if the pivot table had that option…I use them extensively, however I’m still learning new things about them and I thought maybe I just couldn’t find this option.
Thanks Hans!
LJM

Maybe I exaggerated… it’s not “massive” amounts of data, however it’s usually about 1,300 lines… the pivot table summarizes it into about 250 lines. I’m using Excel to “map” ADP’s payroll download into our data format so I can then upload it as a journal entry. I use a lot of vlookup’s to map it, then summarize it using a pivot table… also, I don’t know much about access, so I’m thinking probably not, however I willingly to look at it as an option if you think it’s something I should try.

Perfect!
Thanks Hans!
LJM

Good to know… thanks Hans!
LJM

This works great Hans… thanks for the solution! I’m still curious if it is not possible to change the color for the “button” option in the “forms” tool bar?

There is a reason you are a MVP… this is fantastic!
Thank you Legare!
Lana

Hi Legare,
Sorry about that… for example purposes I had generalized a file that had a macro in it that someone else wrote, therefore the password. It wasn’t anything I wrote, so it had nothing to do with my copy & paste dilemma. I copied my example to a “clean” workbook and re-attached to the original question.
To answer the question in your reply, I would like to copy ALL of the rows from the “data to copy & paste” to the appropriate areas of the “New Worksheet”. One thing to keep in mind is that the data in the “data to copy & paste” worksheet may be 150 lines one month and 250 lines long the next, so it varies. I planned on setting up the 10 journal entry templates in the “New Worksheet” with plenty of space between each to accomodate the copying & pasting of the varying lengths of data from the “data to copy & paste” worksheet.
To answer your next question, there will always be a journal entry template for EVERY company, so we shouldn’t have a problem there. Also keep in mind that the first two digits (Left,2) in the department code (in the example 75R&D) is the company number, so I thought we could read the left two digits to determine which got moved and to where?!?! Does that make sense?
Thanks!
Lana

Hello!
I need help as usual… my search in this forum found a few posts that may be similar, but because I’m not up on the code (yet!) I can’t tell if it will do what I need. I’ve attached a worksheet as an example… what I’m in need of doing is to transfer a large amount of data from one worksheet to another based on a variable. For example, there may be 10 lines out of 150 lines that need to be copied over to the next worksheet, however they need to go in a certain place in the new worksheet. The variable is the company number which is built into our department number… for example the company number is 75 and the data that needs to be copied has a department number of 75R&D. I’d love a macro that could search the data and find all lines that have a “75” in the left 2 characters, and then copy and paste those lines into the next worksheet… BUT it has to paste it in a spot where company 75’s lines need to be. Basically I have about 10 company numbers, which means I’ll have 10 areas on the new worksheet where the 10 separate sets of data need to be pasted… man, I hope my spreadsheet makes more sense?!?!?! Any help on this would be greatly appreciated!!
THANKS!
Lana

Thank you macropod!
I will try this out next time it occurs… it’s a pesky little problem that doesn’t ALWAYS occur, when it does it’s quite irritating.
Thanks again!
Lana

Thank you Legare!
LJM

Thanks so much Hans!! This is exactly what I was looking for! I really appreciate your help!
Lana

My vlookup formulas always have “false” as the fourth parameter. The problem most definitely is a numeric and/text mix issue…. however it’s odd that the account number (all numeric = 8000140) doesn’t show a match and of course I know it matchs as it’s in the vlookup data reference. When I multiply one or both by the 1, then it “resets” itself and recognizes a match. I’ve attached my current spreadsheet which presented itself with this problem… keep in mind that some of the data is downloaded from the AS/400, so maybe it’s formatting characteristics are causing the problem… I don’t know… sometimes if I simply re-key the 8000140 by hand, then it matches. It’s got to be a formatting thing. Of course re-keying anything kind of defeats the purpose, as you know… and as mentioned earlier, I don’t always get this problem… I download and use vlookups EVERY day… 95% of the time I don’t have this problem… then it rears it ugly head.
Thanks,
Lana

Thank you Hans! This is exactly what I wanted it to do. It is fabulous… you do great work! I was wondering if some sort of vlookup would work in VBA as I use them all the time in Excel, however I’m still totally green in VBA, so I didn’t know how to go about it until I saw it in the code you wrote for this. This is a great learning tool for me! I really appreciate your help with this!
Thanks again,
LJM

Perfect! You rock Hans… thanks so much!!
LJM