+ Reply to Thread
Results 1 to 18 of 18

Macro copy one row at a time

  1. #1
    Registered User
    Join Date
    01-05-2011
    Location
    Slovenia
    MS-Off Ver
    Excel 2016
    Posts
    49

    Question Macro copy one row at a time

    Hi to all

    I'm new with vba programming so let me explain what's my problem. I would really appreciate if someone can help me.
    I have many combinations located in spreadsheet ''COMB'' in cell from b1:f1 in 792 rows. I would like to copy each row at a time to the spreadsheets ''WGP'' in cell from b4:b8 and then run my existing macro which run excel solver and then copy results to the b48 and bellow

    All I need is a code that will automatically copy one row at a time, run solver and copy results to another location and then copy next row and so on till end of a list of rows


    I attach my excel file and existing macros which was run manual by button, but now I want to automatic run all by some kind of a loop.
    Thanks in advance. Josef


    Excel solver code
    Please Login or Register  to view this content.
    copy in two row based on number of scenario
    Please Login or Register  to view this content.
    now i have a button which move manualy to the next scenario number

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Macro copy one row at a time

    Welcome to the forum! I just want to make sure I understand your goals.
    1. You want to iterate through each row in COMB copy and transpose each combination of colors to B4:B8 in WGP
    2. Run Solver
    3. Take the solution and copy to B48 and below in WGP...this is where it gets fuzzy. I'm confused as to how the solutions are outputted.
    If your questions has been answered to your satisfaction please don't forget to do the following:

    Add Reputation ... and ... Mark Thread as Solved

    Thanks,

    Ma 10:8b Freely you have received; freely give.

  3. #3
    Registered User
    Join Date
    01-05-2011
    Location
    Slovenia
    MS-Off Ver
    Excel 2016
    Posts
    49

    Re: Macro copy one row at a time

    That's my goals, to copy each row at a time, run a solution via solver and copy to another location and go for next row and make same process, so late I can find best option from all 792 combinations.

    In cell D4:G8 I have function which one show numbers based by name of the color in B4:B8.
    And then excel solver make solution by these numbers and put it in cell I4:I8, other macro then copy name of the colors from B4:B8 to B48:F48 and solution from solver from I4:I8 too B49:F49

    but if it's easy to copy in a new sheet, or in a different order or any other way i'm acceptable for it

  4. #4
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Macro copy one row at a time

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

  5. #5
    Registered User
    Join Date
    01-05-2011
    Location
    Slovenia
    MS-Off Ver
    Excel 2016
    Posts
    49

    Re: Macro copy one row at a time

    I think it's gonna work, but when i run macro i got this message from solver and i cannot close it.
    When I force program to close I receive another message box from solver password ask me enter password ?

    Actually I can't say if it's working. Something is copying but even if I reduce that list in COMB worksheet to few I cant press button in that solver box till end, it shows over and over. I have to force close whole program.
    There are also missing values in F48 row and bellow


    solver.jpg

    I also miss code to copying results from I4:I8 to row bellow

  6. #6
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Macro copy one row at a time

    Ok I'm close to fnishing the fix...but having trouble with the solver.

    What are the constraints on your solver?

  7. #7
    Registered User
    Join Date
    01-05-2011
    Location
    Slovenia
    MS-Off Ver
    Excel 2016
    Posts
    49

    Re: Macro copy one row at a time

    I have to appologize i was urgently absent these days

    This is weighted goal programming model so in minimization cell K3 i have this function
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I4:I8 are changing cells and whole list of constraints are written below
    Please Login or Register  to view this content.
    I didn't post the whole model, because i didn't want to make a mess, but if would be easy i will. I made a print screen how this model look like


    print.jpg

  8. #8
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: Macro copy one row at a time

    I think it would be better if you uploaded the whole model.

    I'm guessimating you wish to run Solver in a loop. That requires a setting to Solver in Visual Basic -> Tools -> References and tick box marked Solver. Do you know the complexity of you calculations.

    The default setting for Solver is "GRG nonlinear" and "smooth" problems but it your problem is linear then the "Simplex LP" runs faster. The Evolutionary engine is for non "smooth" problems. Don't know much about this.

    Looping Solver it is a good idea at the start to add the line
    Please Login or Register  to view this content.
    to avoid buildup of the criteria you are setting.
    To get the result and prepairing Solver for the next run you can add the line
    Please Login or Register  to view this content.
    Alf

  9. #9
    Registered User
    Join Date
    01-05-2011
    Location
    Slovenia
    MS-Off Ver
    Excel 2016
    Posts
    49

    Re: Macro copy one row at a time

    I have ticked solver in visual basic and solver was worked with command button.
    I just want to upgrade the model with automatic going through all possible combination one by one so I dont need to physically move data validation list one by one


    here is the whole model
    Attached Files Attached Files
    Last edited by joezzz; 11-16-2015 at 01:04 PM.

  10. #10
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: Macro copy one row at a time

    Looing at you model I can only see you have defined 8 different wines i.e. the one found in sheet "Base" under heading "Red", "Pink" and "yellow" so pasting values from sheet"COMB" makes most values in D4:G8 "#N/A"

    Running solver with your original macro works and testing with selecting different wines from the dropdown menu will not generate a solution as solver can't find a point that meets with all constraints so I think you need to relax these a bit.

    Running Solver in a loop if your rows go from 1 to 792 could be done with a macro like this

    Please Login or Register  to view this content.
    Alf

  11. #11
    Registered User
    Join Date
    01-05-2011
    Location
    Slovenia
    MS-Off Ver
    Excel 2016
    Posts
    49

    Re: Macro copy one row at a time

    Thanks for help,

    reason why most values are D4:G8 "#N/A" is because I use this code from Myrna Larson also posted on forum which generate all possible combinations, but in some reason it makes a space bar before every word in column B,C,D and E so words from sheet ''COMB'' and that in data validation list are not the same. Can you check code below ?


    Please Login or Register  to view this content.
    I know i ask silly questions but i'm a real amateur with VBA and I use codes only if can be easily modified. In your code you write to enter my other constraints, but i don't know how, everything i write into is in red. Here are my constraints

    Please Login or Register  to view this content.
    Can I ask you for help to modify my old code and add it to your code. Code have to copy all calculated results from solver to the new sheet. Now i used this code also with combination of other button which moves number to the next one. I just need to copy every result from all scenarios placed in ("B4:B8") ("I4:I8") and ("c14:c18") to another location
    Now i was copying each scenario in two rows, but it would be easy to copy only in one row.


    Please Login or Register  to view this content.

  12. #12
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: Macro copy one row at a time

    The easy part, vba code with your solver contrain settings.

    Please Login or Register  to view this content.
    The easiest way to write a code like this is:

    Open solver, clear all data from solver by pressing button "Reset All" click "ok" and close solver.

    The go to "Developers" tab and click "Record Macro"

    Start doing all steps to run solver i.e. click on data tab and fill in all the values you normaly do and run solver if you like. Close solver and go back to the "Developers" tab and click "Stop Recording".

    You now have a macro recording of the solver set up. You don't get the extras like
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    and you also get a second setup of line
    Please Login or Register  to view this content.
    before the "SolverSolve" line, but you don't need this so delete it as it makes code more easy to read.

    Will have a go at you other questions ans see if i can do something there as well.

    Alf

  13. #13
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: Macro copy one row at a time

    Looking at you copy and paste code it could be simplified

    Please Login or Register  to view this content.
    Not sure of your prameter "stc" is this a counter that increases after each copy or? Because at the moment it looks to me that old data will constantly being "ower written" by new data
    Since you aim to run solver in a loop 792 times you will get 792 lines with values and you take 5 values from a column and transposes it to a row so the first result gets written to the B column, C column, D column, E column and F column.

    The next values gets written to A, B, C, D and E column and the last set will be written to F, G, H, I and J column. You sure you can keep all this values appart?

    I would think a separate worksheet for the result would be easier to read.

    Alf

  14. #14
    Registered User
    Join Date
    01-05-2011
    Location
    Slovenia
    MS-Off Ver
    Excel 2016
    Posts
    49

    Re: Macro copy one row at a time

    thanks for help, I forget that record a macro was also an option to add my constraints - THANKS!

    ''stc'' was a counter. In my previus model i write a macro which was powered manualy by button. and that button move scenario number to the nextone so other button whichone copy data can read that number and copy to certain location and dont owerwrite the number as now i think it's happen.

    In my previous model i also copy data in two rows, but now it would be easy to put all result data in one row and new worksheet better asswell.
    but now macro have to automatically copy results in B4:B8; I4:I8;C14:C18 to rows one by one. I think the best way to do this is create new worksheet and copy all results in rows in order from 1-792

    So result from B4:B8 have to be written in new worksheet cell A1:E1 ; I4:I8 in cell F1:J1 ;C14:C18 in cell K1:O1 and next result in 2nd row and so on.

  15. #15
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: Macro copy one row at a time

    Yes that sounds resonable. If I assume the new sheet is named "Result" then I would suggest a code like this and by the way I'm assuming you run Solver in a loop.

    Please Login or Register  to view this content.
    The "Next" is there to return the loop back to reading the next line from "COMB" sheet and start another Solver run.

    Still I'm not sure how you set the solver loop i.e. reading in the lines you wish to "feed" to solver.

    Re troubleshooting the Myrna Larson permutation code my excel knowhow is not up to that. Sorry

    Alf

  16. #16
    Registered User
    Join Date
    01-05-2011
    Location
    Slovenia
    MS-Off Ver
    Excel 2016
    Posts
    49

    Re: Macro copy one row at a time

    It works, thanks !

    now I only have to change Myrna Larson code.

    Thanks' a lot !

  17. #17
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: Macro copy one row at a time

    You are welcome and thanks for the feedback.

    By the way if the problem with Myrna Larson's code is that
    but in some reason it makes a space bar before every word in column B,C,D and E
    you could use a macro to "clean" the permuted result.

    As pressing the space bar makes a blank you could use the "TRIM" function to get rid of it i.e.

    Please Login or Register  to view this content.
    Alf

  18. #18
    Registered User
    Join Date
    01-05-2011
    Location
    Slovenia
    MS-Off Ver
    Excel 2016
    Posts
    49

    Re: Macro copy one row at a time

    it works, thanks

+ 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. [SOLVED] Sequential copy/pastes in macro take too much time
    By macaonghus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-27-2014, 05:08 PM
  2. macro which uses two worksheets at a time (copy and paste)
    By Manjula1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-21-2014, 07:23 AM
  3. Macro to copy and paste at specific time of day
    By BCrawford in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-30-2013, 06:27 AM
  4. Macro to Copy Workbook from One Drive to another at a Specific Time
    By ashishmac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-09-2013, 02:28 AM
  5. [SOLVED] macro error Copy Time format to other Worksheet and continues copy range
    By feroguz in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-25-2012, 01:30 PM
  6. macro to copy to new column each time used
    By deanomcbeano27 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-06-2009, 05:51 PM
  7. i would like a macro to copy x time rows based
    By dversa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2009, 08:52 AM

Tags for this Thread

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