Posts by ShosMeister

Re: Loop thru folder of workbook using cell value and copy/paste values beween files

Reversed logic.

Re: Loop thru folder of workbook using cell value and copy/paste values beween files

Can you send me a template and London file with representative data?

Re: Loop thru folder of workbook using cell value and copy/paste values beween files

Guessing you nor he can see easily how to reverse the copy/paste logic? I don't know if I can get around to this today so if you don't see something by next Wednesday, ping me.

Re: Merge Multiple Sheets to one Master sheet

Glad it's working for you.

Re: Macros Won't Work on Protected & Hidden Cells

Office/Excel 2007 and up expanded the conditions so you are no longer limited to 3 but understand the desire to have it in VBA for easier management/adjustments.

As was asked, did you actually use a password to protect the sheet? Did you actually protect the sheet or the entire workbook? This all makes a difference.

Re: Macros Won't Work on Protected & Hidden Cells

If you did not use a password to protect the sheet initially, leave that off and just use the Unprotect/Protect. Works great all the time.

If all you are doing is controlling the cell format based on values in the cells, you may want to look at Conditional Formatting instead. Much easier to do and manage compared to VBA.

Re: Paid Time Off Accrual Workbook

From what you have provided, I don't know of a way to do this via straight formula. The logic is a bit sketchy as to when/why you would change the accrual rate and how far back that goes. I see it's based on their hire date but if you were hired 4 years ago, only this years accrual would be counted which would be at the 4 year rate; right?

The simplest thing I can think of is to have a Per Pay Period entry for each employee where the numbers are calculated and stored as numbers. Then your summary and everything else can use the sum of those. Other than that, there's not a way I can see to calculate "historical" data.

Re: Merge Multiple Sheets to one Master sheet

Well without having your complete data set, I can't duplicate it as it runs fine given the sample data you provided.

Re: Excel 2003 #NUM Error Challenge

Found this in another thread

Quote

What version of Excel are you using? Do the named ranges refer to entire columns? If so, you can't use entire columns as range references with SUMPRODUCT unless you're using Excel 2007 or later.

There are also other threads indicating tracing the path as SUMPRODUCT does not in itself return the #NUM error; rather, one of the cells it is referencing already has a #NUM error or an invalid numeric value.

Re: Insert columns in elaborate workbook

There is no way anyone will be able to help you inserting new columns without a copy of the workbook. Also, looking at your screen print, you will have a lot of trouble if all of those cells that appear to be merged are in fact merged. That is a big sore spot of mine as merging cells just causes problems.

Re: Summing the functions of the previous function

Sounds like you want a user defined function? Take a look at this -> http://www.ozgrid.com/VBA/Functions.htm#UDFIntro

Re: VBA Copy/Paste from Active Cell sheet 1 to next blank cell sheet 2

Sounds like the problem you are having is finding the last row?

LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

.

Re: Loop thru folder of workbook using cell value and copy/paste values beween files

Used this with your sample London file. Please look at the code to try to understand what's going on. Tried to make it future proof.

Re: Macro for placing numbers between two numbers in other sheet

Glad it's working for you and hope you take some time to see what it's doing and how so you can learn a bit more about VBA and Excel.

Re: Loop thru folder of workbook using cell value and copy/paste values beween files

One thing to make this simpler and less susceptible to errors if the workbooks change is to have the columns in the template match those in London (the a-n columns). Is that possible? If not, it will require a bit more work to ensure the correct data is copied from the correct source to destination column.

Re: Loop thru folder of workbook using cell value and copy/paste values beween files

Please fill in representative sample data in the columns to be copied as this may impact how the data is captured (string, int, long, date, etc.).

Re: Macro for placing numbers between two numbers in other sheet

Ahhh. Change the variable type for both From_Val and To_Val from Integer to Long. That should fix the overflow problem.

This is a good example of why you need to try to put data that is as close as possible to your real data when uploading sample files. Obviously, if there is proprietary data that needs to be changed, then do so but try to keep it similar.

Re: Loop thru folder of workbook using cell value and copy/paste values beween files

So where does it get the data for the headings to start with (London, Leeds, etc.)? Or is the sample you have in Template what it will look like at the start - IOW that you will fill in the dates/locations/names?

Re: Visual Basic locate and paste

You'll need to attach a sample workbook. One with the data as it's captured daily (as you indicated) and a sample of what you expect the results to look like. All of your requirements seem pretty straight forward but without sample data, it would be impossible to come up with a working solution.

Re: Macro for placing numbers between two numbers in other sheet

Don't forget to use the CODE tags in your posts!

Where are you getting the error? Obviously the "real" data is significantly different than your sample so you may need to upload your file.