Search results

Morning all, I have found the bellow formula on another site which works great at matching a cells contents to the name of the tab that cell is in however I wonder if there is a way to alter it so the cell contents matches the name of a different tab within the same workbook? VBA is not an...

Morning all, I am using the below code to create multiple sheets from a data sheet. Currently when i run the code it asks if i want to save them and i then have to select a folder for them to go into. I would like to remove the question and get the code to automatically save the workbooks it...

Hi Mike, No worries with the delay and thanks for all your help with this. It works beautifully and does exactly what i needed. Many thanks again for all of your support. Tom

Hi all, I currently use the below piece of code to rename sheets based on a cell value. I wonder if anyone can help me alter the code so that a replication of the same name is updated with a number. For example if there are two cells (that both alter sheet names) with Joe Bloggs written in them...

Hi Mike, After playing around (and learning lots) with your version I have managed to get your solution working well on the workbook i am using, many thanks for that. Could you support me in getting the calculation to cap at 20 years service. I have attached your original workbook with a...

Morning both, Sorry for the slow reply, i have been celebrating my 30th birthday. Thank you for the above, both look quite advanced (in comparison to my knowledge level) so i will take some time to figure out how they work and then attempt to implement them into my workbook. I will come back...

Hi all, I am trying to work out how to calculate length of service into different age brackets. I have attached a simple excel example to hopefully help explain. What I need is a calculation that breaks the length of service into the following age brackets 18-21 years 22-40 years 41+ years...

Hi Chihiro, That has done the trick, slight adjustment i had to make is changing this 'If the sheet is visible then copy it to a new workbook If sh.Visible = -1 And IsError(Application.Match(sh.Name, shArr, 0)) Then sh.Copy to this 'If the sheet is visible...

Hi Chihiro, Thanks for the reply. I am trying to get the above code to work and approaching it by sandwiching the save code i have above into your code as i have shown below however this brings up several errors around End If. Any other pointers would be great. Sub Test() Dim shtArr Dim ws As...

Hi all, I am using some code that exports all visible sheets from a workbook into individual workbooks and saves them. What i would like to add is a list of sheet names to avoid. For example if i run the macro it would export all visible sheets except ones with the names Fred, Tom, Josh and...

Thanks Hui, I have now resolved this but you where to quick to reply before i got in there first. You are exactly right i was able to change activeworkbook to thisworkbook on each of the macros that where running and it is now resolved. Thanks for your help.

Apologies i have now resolved this myself. I had references to ActiveWorkbook which i changed to ThisWorkbook. Ridiculous that i didn't think of that before! Many thanks Tom

You sir have skills!! I could kiss you (now imagining some big hairy bloke that really hasn't appreciated that comment haha). Thank you so much for all of your help, it really is appreciated.

Ok, so you have worked your magic on the copying and pasting :). When i run the new code it opens the correct workbook and pastes everything it should. I then get a 'subscript out of range' error with the following code showing up in the debugger Workbooks(Dest).Close savechanges:=True The...

unfortunately still the same issue when using the File>Open option. I am using office 2010.

With all three open the same thing happens it brings the sheet that it is trying to copy to to the front but nothing is pasted.

So for book 1 it didn't ask to enable editing but for book 2 it did. I saved book 2 and ran the macro for both sheets again but still with the same issue that nothing is pasted. I know i keep saying it but thanks for the help with this :)

Morning. Mine was great, still recovering but that's always the sign of a good one. I tried the new code and it stops the error from coming up. The appropriate sheets opens but nothing is copied onto it and it stays open. No error of any description is shown. Is it about now that you are...

Good morning PCosta, How was your weekend? I have had a play around (changed sheet names, changed workbook names, moved the code into new workbooks, placed the files so that the file path was shorter etc) but still hit the same issue as described above. Let me know if you need anything more...

Unfortunately still the same issue. Thank you so much for the help! I can see it is nearly there as it does everything up to that point. I will continue to play around with some different locations and the code and see if i can get it to finalize.

Thanks again for your help. The files are attached, Hopefully its not something i have done to break it!

Thanks again PCosta87 I have tried to make it work but i keep getting a 'subscript out of range' error. The following line is highlighted in the debugger. lrow1 = Workbooks(Dest).Sheets(1).Columns("A").Cells(Rows.Count).End(xlUp).Row + 1 This happens regardless of which sheet i select in...

PCosta87 Thank you that works beautifully! The only one thing i would like is for it to work without the book open as in the macro will open the correct book paste the appropriate data and close the receiving book again. Is this something that is possible? Apologies i should have probably...

Hello all, My first post on here so please let me know if i need to do anything more in this post. I have a workbook that holds some data and a dropdown box. What i would like to do is get some VBA code that would copy and past particular active cells to another workbook on a shared drive...