+ Reply to Thread
Results 1 to 14 of 14

Macro to copy non continuous ranges in closed worbooks based on user input

  1. #1
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Ottawa, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    104

    Macro to copy non continuous ranges in closed worbooks based on user input

    I need some help on a 2 parts macro.

    The first part of the macro ask the user to input a number (LSearchValue) where it then search for that number in another worksheet (MasterData) and copies the rows matching that number inputted by the user to another Worksheet (PostMortem). All this is done in the active workbook. This part is already working well and you can find the code below.

    I need help on the 2nd part. I would like to keep using the number inputted earlier by the user (LSearchValue) in the first part of the macro. I would like the 2nd part of the macro to search for this number in all excel files found in a folder (no need to look at subfolders) until it find a excel Workbook that contains a Worksheet named the same as the number previously inputted by the user. Once it finds it, It would then copy the cell B4 from the closed workbook and paste that value in the active workbook in cells H4; it would also copy cell B8 from the closed workbook and paste that value in the active workbook in cells H6.

    As an example to illustrate the 2nd part of the macro, if the user inputted 12345, the macro would search all workbooks found in the folder until it finds a Workbook that has a worksheet name 12345. It would then copy the cell B4 from the closed workbook (Worksheet 12345) and paste that value in the active workbook in cells H4; it would also copy cell B8 from the closed workbook (Worksheet 12345) and paste that value in the active workbook in cells H6.

    I have trying to figure out the 2nd part but without success. I am including the code I got so far; I believe the first part that search for closed workbooks is accurate but the part bolded where it specify a worksheet based on LSearchValue and to copy cells is definitely not accurate.

    Thank you for your time.


    1st part (Working)

    Please Login or Register  to view this content.

    2nd part (Need help on)

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Bonjour, Hi Chris,

    Here's a proposal (Not tested) :

    First macro (I did some "clean up" in it) :

    Please Login or Register  to view this content.
    2nd macro :

    Please Login or Register  to view this content.

    As you can see, the 2nd macro is called from the 1st macro and the "LSearchValue" is passed as an argument to the 2nd macro.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Ottawa, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    104

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Thank you GC Excel for your quick response. I spent 1 hour this morning on it but it still not working.

    The first part gives me the error message but that is not a big deal as I can use my old version and add the calling of the 2nd macro. But thank you for cleaning up the code, I will try to resolve it in the future using your code as I like it better.

    Not sure why the 2nd part does not work. The macro is running fine but not copying any data. I did find that I should have put strExtension = Dir("*.xlsx")
    but that did not solve the problem. Let me run some more tests on my side and I get back to you if I cannot figure it out. (I will mark as solve if I find the solution)

    2 questions for you though:
    1: The 2nd macro (CopyRangeFromWbs) does not show up as a macro to run when I hit alt+F8 (It seems because of the addition of (sSearchValue As String).Is this normal?
    2: If i want to see if the 2nd macro work by forcing a value for sSearchValue (for testing purpose), should I put sSearchValue = 12345 or sSearchValue = "12345"?

    Thank you!

    P.S. I may have less time working on it in the next few days but I will get back to you.

  4. #4
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Hi Chris,

    It is normal, if a macro has input arguments like the second macro, to not show when you press alt+F8
    To test it, create a dummy tests macro like this :


    Please Login or Register  to view this content.
    Since the expected argument is string, you should use "12345" instead of 12345
    (even though Excel would convert it correctly in this case)

  5. #5
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Ottawa, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    104

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Hello GC Excel,

    I found where it is not working but I will still need your help I try to run the original macro that Loop Through a Folder of Excel Workbooks and found out that to use xlsm files I needed to change 2 part of that original code (changes are in bold):

    strExtension = Dir("*.xlsm")
    wbNew.SaveAs Filename:="C:\test\TemplateCollation", FileFormat:=xlOpenXMLWorkbookMacroEnabled

    The problem is that in your code, I do not see a place where I change the file format to xlOpenXMLWorkbookMacroEnabled so this is where I would need your help. I believe if we can add the file format somewhere in the code, it should work.

    FYI I did run your code on a .XLS file and it work perfectly. We just need to run it on a XLSM files.

    P.S. Sorry I did not mention initially that I was going to use XLSM files, I should have been more clear.

  6. #6
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Hi Chris,
    Why do you need to save the file?
    My understanding is that you loop through the .xlsm files in your folder, open it, send the data to the master workbook and close it. No need to save since you haven't made any change to the file.
    In the macro, this line is used to close the opened workbook without saving it

    Please Login or Register  to view this content.
    Change it to :
    Please Login or Register  to view this content.
    if you want to save the changes.


    In the original macro you found, you are looking for .xls file and want to save them as .xlsm.
    That's not required in your case.

  7. #7
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Ottawa, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    104

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Hello GC Excel,

    I agree with you, I do not need to save the file. Your macro works with .xls file but not with .xlsm files even if i change the line

    strExtension = Dir("*.xls")

    to

    strExtension = Dir("*.xlsm")

    Base on my testing of the original macro that Loop Through a Folder of Excel Workbooks I had to change 2 line of data in order for the that macro to work with .xlsm files. I was suggesting that if we could change those 2 lines of data in your proposed code, it may now works with .xlsm files. So is there a way to have the code FileFormat:=xlOpenXMLWorkbookMacroEnabled but without having save to file just in order to be able to use .xlsm files?

    Please note that my suggestion may not be valid or possible. If so is there another way that your code would work with .xlsm files?

    Thank you for your time.

  8. #8
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Hi Chris,

    Can you try this code :

    Please Login or Register  to view this content.
    This will list all .xslm files in the immediate windows (CTRL + G). If any .xlsm in the C:\test folder
    As you can see it only uses the DIR function...

  9. #9
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Ottawa, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    104

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Hello GC Excel,

    Thanks again for spending your time trying to help me out, really appreciated. I did run your code and I did not get any results that I could see and when I open the immediate windows (CTRL+G) it was empty.

    I am attaching my test files as it may help you better understand.
    ActiveWorkbook.xlsm is the active workbook that is on a shared drive and where I run the macro from. Inside the workbook you will find the following macro:
    -CopyRangeFromWbsXLS: your original code that you suggested
    -CopyRangeFromWbsXLSM: same as your original code that you suggested but I just change the following line (changes in bold) strExtension = Dir("*.xlsm")
    -Test: Your code above that list .xslm files
    -CallMacroXLS and CallMacroXLSM: used to call the macro in order to test it

    ClosedWorkbook.XLS and ClosedWorkbook.XLSM are the same exact file where the macro is trying to pick up information, the only difference is in the file format. The files are stored in C:\test\.

    If you run the macro CallMacroXLS and have the file Closedworkbook.XLS in C:\test\ the macro will work and pick up the data.
    If you run the macro CallMacroXLSM and have the file Closedworkbook.XLSM in C:\test\ the macro will not work.

    How could the macro be modified to be able to pickup .XLSM files? Thanks again!

    P.S. Please use the 2nd version of the ActiveWorkbook.XLSM (the one that is 20.7KB) as the first version (19.6KB) had a little mistake
    Attached Files Attached Files
    Last edited by Chrispelletier; 12-19-2013 at 08:29 AM.

  10. #10
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Hi Chris,

    I tried both macro and nothing worked.
    Then I tried the Test macro and it listed me totally different files.

    Then I realized that CHDIR only changes the directory.
    If you are on a different drive, you need to use CHDRIVE first

    So, include this line in all the macros and test again :
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Ottawa, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    104

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Hello Gc Excel,


    Thank you for your reply. I made the changes to the code and the .XLSM file (ClosedWorkbook.XLSM) does show up in the Immediate windows.

    So i guess it is looking into the file but not pickup the information?

    I relook the original macro that Loop Through a Folder of Excel Workbooks, it does mention it works on Excel 2007 but did not make any mention of working on Excel 2010 (Which is the version of Excel that I am using). Could it be that the code to loop through files does not work on Excel 2010 so we have to look for a new code for that part?

    Thank you!

  12. #12
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Hi chris
    I'm away for 2 weeks. I will get back to you when i get back home.

  13. #13
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Hi Chris,
    Have you been able to solve your problem?
    From your last comment, if the macro works in Excel 2007, it should also work in Excel 2010. In fact, I'm using Excel 2013 and it works: the values from ClosedWorkbook.xlsm are pulled correctly in ActiveWorkbook.xlsm

  14. #14
    Forum Contributor
    Join Date
    03-29-2012
    Location
    Ottawa, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    104

    Re: Macro to copy non continuous ranges in closed worbooks based on user input

    Hello GC Excel,

    I just came back from holiday myself and the problem is solved. Not sure why I had some problems before but I just did a test this monring and it is working with .XLMS files.

    Thank you so much for all your help and time spent working with me on this. I will mark as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to Copy Rows based on User Input
    By CharterJP in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-24-2012, 06:13 PM
  2. Automatic Formula Input Based on User Input Using a Macro
    By Drayloc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-03-2012, 12:10 PM
  3. User input from non-continuous ranges
    By vbanoob2 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-24-2011, 01:36 PM
  4. Replies: 3
    Last Post: 02-20-2011, 06:30 AM
  5. macro to copy cells based on user input
    By pankaj2145 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-03-2009, 04:54 AM

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