+ Reply to Thread
Results 1 to 75 of 75

Change to multiple worksheets in same file?

  1. #1
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Change to multiple worksheets in same file?

    Hi people!!

    As the title says really, is it possible to make the same change to multiple (hundreds) files in the same folder at once, without opening them all and changing them individually??
    For example i have hundreds of files and they all have a database tab and i need to update the database but obviously changing them all individually would take a while!!

    So is it possible to change certain info on a certain tab in multiple files at once i suppose is the question??

    Thanks in advance for any help!!!

  2. #2
    Forum Contributor
    Join Date
    09-23-2008
    Location
    Mexico
    Posts
    200

    Re: Change to multiple worksheets in same file?

    Maybe you can adapt this:
    http://www.mrexcel.com/forum/excel-q...workbooks.html

  3. #3
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Thanks for the response!!

    That didn't do what i'm after!!

    I don't wanna move any data just change data (the same data in each file) that is already in each file (all 1015 of them)???

    I'm assuming it can not be done but thought i would ask the question......

  4. #4
    Registered User
    Join Date
    11-15-2012
    Location
    Phnom Penh (http://learnmsexcel2gether.tk)
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Change to multiple worksheets in same file?

    I think by changing data in every files like your question without open files is not possible. But by using VBA you will able to change data in these files by just one click. However VBA will open those files and close them after updated.

    If you would like to know how to achieve it pls give me few sample file then i will write VBA code and post back to you...

    If anyone know other way better than mind pls share, I also would like to know it.

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Yes, if you specify what needs to be changed and where, you can use a VBA code which opens each file, does the updates and closes it for you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  6. #6
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    So if i wanted to changed cell A10 in the 5 attached files from 'AMPLIFIER GUITAR' TO 'GUITAR AMPLIFIER' for example, there is a code that will do this??
    Attached Files Attached Files

  7. #7
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Yes, that can be done. However, to ensure that we dont have to do double work, is this the only condition? Or are there a list of conditions that need to be checked and then find-replace action performed?

  8. #8
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    There will need to be things added to the list in the future! For example add 'BANANA' to cell A17 and 'CAMERA' to cell A18......

    Thanks for all your help!!

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Do you want to specify the find-replace criteria in the code itself? You could even have a separate sheet (maybe hidden) that lists out all the criteria and the code could go through it one by one. It can help you maintain it without having to dig into the code and also avoid the possibilities of messing up the code.

  10. #10
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Well it's something that is being constantly added to! Previously i have updated each sheet individually (very time consuming) as and when a new product is needed, so if there was one hidden sheet that i could update and that will update all worksheets then that would be great?? Is that really possible??

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Yes its possible. You have attached the files that need to be changed. Why not attach the file with the hidden sheet? You can list out the criteria and the macro can go through one by one.

  12. #12
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    I've attached a file with a hidden sheet but i'm not really sure what i'm supposed to put on that hidden sheet?????
    Attached Files Attached Files

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    What i meant is - currently you want me to search for Amplifier guitar and change it to Guitar amplifier right? The code can be written for that part. However, if you have another such term to do a find replace on, you may have to repeat the code to take care of the 2nd term. But instead of that, i can help you with a loop that will go through the entire list of such items to be found and replaced so that you can add to this list at any time.

  14. #14
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Oh, that sounds exactly what i need!! Would be great if you could do that??

    Thanks again for all your help!!!

  15. #15
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Do you want to provide me the file or should i just dummy data? You can ensure that the Find text is in column A and Replace text in column B.

  16. #16
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    If you could use dummy data please!! Obviously the files i provided were just for example. I will incorporate your code into my actual files once received....

    Thanks!!

  17. #17
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Could you just use dummy data please! I will import your code into my file once created....

    Field text is column A and replace text is column A also....

    Thanks!!

  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Do you have the find and replace text one below the other?

  19. #19
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Yes, all in 1 line in column A! The list is about 500 lines long if that matters...

  20. #20
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Quote Originally Posted by arlu1201 View Post
    Do you have the find and replace text one below the other?
    Have you had any joy with the code yet arlu1201??

    Thanks
    Last edited by Frazzfreeman; 11-21-2012 at 04:38 AM.

  21. #21
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Try this code - Put it in a standard module (steps given below) in the workbook which contains the list of find-replace criteria in Sheet1 -
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose Tools | Macro | Macros
    Select a macro in the list, and click the Run button

  22. #22
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Thanks for doing that!!

    When i run the code i get the following error massage;

    Run-time error '52':
    Bad file name or number


    Do i need to change something within the code perhaps??

  23. #23
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    You need to change this line myPath = "D:\Test" to match your system path.

    Which line is it highlighting?

  24. #24
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    It's highlighting the following;

    CurrentFileName = Dir(myPath & "\*.xls")

  25. #25
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    I have changed the line you suggested but still get the same error and it still highlights the above line??

  26. #26
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    If you hover your mouse over Currentfilename, what value does it show?

  27. #27
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Currentfilename=""

  28. #28
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    I've tried again and this time it didn't throw up any errors!!! But, it doesn't seem to have done anything, is there a command i need to give to change all the files???

  29. #29
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Last message wasn't very clear, Let me re-phrase - Is there a command i need to give to make 'changes to the multiple worksheets' within that same folder, once the code is running???

  30. #30
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    You ran the macro right? The usual way of running it in excel 2003?

  31. #31
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    I have!! The screen flashes then does nothing to be honest! I'm using a files with the examples i previously attached in this thread....

  32. #32
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    I've attached the first file from my test folder (with 7 different files all with the same info) which has the code in, just in case.....

    Cheers
    FrazzFreeman
    Attached Files Attached Files

  33. #33
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Ok, i will need to explain a few things here -
    This file 1.xls which you have attached - is it the file which needs to be changed or is it the file which contains the list of entries that need to be changed in other files?
    Is this a whole replace you are doing or part? For e.g. are you replacing only a part of the text in the cell or the whole text?

  34. #34
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    All the files within the folder contain the same text on a specific tab which is labelled Database.

    Some background;

    This Database is a list of electrical items, when new items become available i need to add them to the existing database's (in my actual folder i have approx 500 files, so to change each file would be very time consuming).
    Also some existing text needs to be changed on all files, like the example i have given previously e.g. AMPLIFIER GUITAR TO GUITAR AMPLIFIER.

    Hopefully this clears it up and it is still possible to do??? Apologies for not being clearer...

  35. #35
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    So will the items to be changed differ from file to file?

  36. #36
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    No, the changes will be the same for each file but there could be many changes!!

  37. #37
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Ok, in that case, the list of changes will have to be maintained in the macro file. Do not maintain the list in each workbook. Then the macro file can open each file and read through the list of changes. Thats how i have designed the macro.

  38. #38
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    To clarify, i would need to make a list of the changes that i need in the 'Database' tab of the sheet that is running the macro so it can pull the information for all files from there??

    What if i wanted to add additional things (not changes as such, but new data) to the original list, would that be a separate list on the same tab (Database)??

  39. #39
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Yes, you are right. The code i provided you does the same thing. I have made the changes to include the "Database" sheet name -

    Please Login or Register  to view this content.
    If you want to add new items to the list, then that will have to be a different macro or we will need to alter the above code a little. And yes, that will need to be in a different list. Maybe the same sheet, but different column.

  40. #40
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    I will try the code now and let you know....

  41. #41
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    I have ran the macro and the screen flashes again and appears to do nothing??

    I've attached the first 2 files from my folder (TEST 1), the file 1.xls has the macro and i have made some changes to the list for test purposes, you'll see that file 2.xls has no changes???
    Attached Files Attached Files

  42. #42
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    As i mentioned to you earlier, the changes to be made should be in a separate file maybe a macro file. This file should not be in the same folder as the other files and it should only contain the list of items to be changed and the macro. It will then open up each file from the folder specified and do the find-replace on each.

  43. #43
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    We're getting there now!

    It is opening the files to make the changes and it must be doing something because it's asking me to save changes, however, it is not actually making changes!! I have attached the file 'TheseChanges' to show the list of two changes i would like to make to the other attached file '3'. I assume it's to do with the way i have listed the 2 items in the 'TheseChanges' file??? Do i need 2 columns, 1 that shows the current way it's listed and another that shows the changes i would like i.e; A being the current listed items and B being the changes i would like to make??

    A B
    AMPLIFIER GUITAR / GUITAR AMPLIFIER
    BATTERY CHARGER / BATTERY


    Hope this makes sense, i'm sure we're nearly there now!
    Attached Files Attached Files
    Last edited by Frazzfreeman; 12-12-2012 at 03:45 AM.

  44. #44
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    You had mentioned that you would be listing the items like this -

    A2 - AMPLIFIER GUITAR
    A3 - GUITAR AMPLIFIER
    A4 - BATTERY CHARGER
    A5 - BATTERY

    Hence i coded it that way. Please let me know in what format you want the first term and 2nd term to be in the macro file so i can change the code accordingly.

  45. #45
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Quote Originally Posted by arlu1201 View Post
    Do you have the find and replace text one below the other?
    I assume you mean this question you asked, which i obviously miss understood, apologies!!!!!!!

    Only the data to 'FIND' is all in a list, column A of everyone of my files (SEE ATTACHED)!!
    I suppose the 'REPLACE' data should be in column B of the macro file 'TheseChanges'
    Attached Files Attached Files
    Last edited by Frazzfreeman; 12-12-2012 at 05:20 AM.

  46. #46
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    So you would be having the Find text in A and the Replace text in B? I will need to re-work the code a little.

  47. #47
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    That would be easier!!

    (As previously mentioned there are entirely new entries that need to be added too!! I don't know whether that could be column C??)

  48. #48
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Yes you could put them in column C and i will add that part into the code.

  49. #49
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Ok, do you have all information from me for the code now???

    Thanks again for all your help!!!

  50. #50
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Try this updated code - as i mentioned earlier, this code should be in a standalone file in a different folder / location. It will then pick up each file to do the find-replace and additions.
    Please Login or Register  to view this content.
    Last edited by arlu1201; 12-12-2012 at 10:14 AM.

  51. #51
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Good news, the changes now work!!! Thanks for that!!

    The additions do not though, I assume once again I'm doing something wrong but not sure???

    (I've attached the macro file and one of the files that should have the additions)
    Attached Files Attached Files

  52. #52
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Sorry, i missed out a few things in the addition code line. You can try the above code now.

  53. #53
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Perfect!! 1 last thing though, can you add to the code to make it sort column A into alphabetical order once the changes have been made??

  54. #54
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Updated code -
    Please Login or Register  to view this content.
    Last edited by arlu1201; 12-13-2012 at 05:09 AM.

  55. #55
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Right then, this code is perfect when i use it on my test data, however, I have added one of my actual files into the test folder and it does not make the changes!
    The only difference between files that i can see is the tab number (the test data 'test1' is on tab 1 and my actual file 'test2' is on tab 10), although they are both labelled 'Database'. Is that the issue?
    Attached Files Attached Files

  56. #56
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Is your tab in the master file named "Database" and the tab in the individual files also named "Database"?

  57. #57
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Yes they are!!

  58. #58
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    I have updated the code in post 54. Please try now and let me know.

  59. #59
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Some files pop up with an error 'run-time error - 1004. The debug highlights the following...

    Please Login or Register  to view this content.

  60. #60
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Would it be because there are empty cells on row 2??

  61. #61
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    A2 here is the first line below the header. So if your data has headers in any other row other than 1, then change the value in that code accordingly.

  62. #62
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    All the files have headers in other rows!! So, which part of the code do i need to change specifically???

  63. #63
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    The other header is in cell D1 and again row 2 is blank??

  64. #64
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    You mean you have different chunks of data in different columns, but they all start from row 3?

  65. #65
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    That's correct!!!

  66. #66
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  67. #67
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Amazing!!!

    Looks all good now!! Thanks again for all your help!!!

    I'm sure we'll speak again, haha!!

    Thanks!!!

  68. #68
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Am glad its solved.

  69. #69
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Unfortunately, another issue has arrived!!

    Everything seems to be happening fine other than, the additional items seem to have copied three times instead of just the one???

    See the attached, i've marked them in red!!

    Help??
    Attached Files Attached Files

  70. #70
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Did you run the macro 3 times and have the values copied thrice or is it after 1 run?

  71. #71
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    I'm pretty sure it was after 1 run only!!!!

  72. #72
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    Try this code - The sorting code was wrongly placed and that could be causing issues -
    Please Login or Register  to view this content.

  73. #73
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Thanks! I'm out the office until Monday but I'll try it then and let you know!!!

    Again, thanks for your help.....

  74. #74
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Midlands, UK
    MS-Off Ver
    2003
    Posts
    149

    Re: Change to multiple worksheets in same file?

    Again, i think it's now working!!

    I'll marked the thread solved!!

    Thanks again for all your help!!!!

    Regards

  75. #75
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change to multiple worksheets in same file?

    NP glad it worked.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1