+ Reply to Thread
Results 1 to 18 of 18

Macro copy one row at a time

Hybrid View

  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
    Private Sub CommandButton2_Click()
    SolverOk SetCell:="$K$3", MaxMinVal:=2, ValueOf:=0, ByChange:="$I$4:$I$8", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverOk SetCell:="$K$3", MaxMinVal:=2, ValueOf:=0, ByChange:="$I$4:$I$8", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve
    End Sub
    copy in two row based on number of scenario
    Private Sub CommandButton3_Click()
    stc = Excel.Range("I2").Value
        
        Range("B4:B8").Select
        Selection.Copy
        Range("B47").Select
        ActiveCell.Offset(stc, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Range("B48").Select
        
    Range("I4:I8").Select
        Selection.Copy
        Range("A48").Select
        ActiveCell.Offset(stc, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Range("A49").Select
        
      Range("c14:c18").Select
        Selection.Copy
        Range("f48").Select
        ActiveCell.Offset(stc, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Range("f49").Select
    End Sub
    now i have a button which move manualy to the next scenario number

    Dim intDV As Integer
    Dim ws As Worksheet
    Dim c As Range
    Dim rngList As Range
    
    Set ws = Sheets("WGP")
    Set c = ws.Range("I2")
    Set rngList = ws.Range("STEVILA ")
    intDV = 0
    On Error Resume Next
    intDV = Application.WorksheetFunction.Match(c.Value, rngList, 0)
    
    If intDV = 0 Or intDV = 1 Then
      c.Value = rngList.Cells(rngList.Rows.Count, 1)
    Else
      c.Value = rngList.Cells(intDV - 1, 1)
    End If
    End Sub
    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...
    Option Explicit
    Private Sub CommandButton2_Click()
    Dim wb As Workbook
    Dim wsComb As Worksheet
    Dim wsWGP As Worksheet
    Dim i As Integer
    
    Set wb = ThisWorkbook
    Set wsComb = wb.Worksheets("COMB")
    Set wsWGP = wb.Worksheets("WGP")
    
    'Start your loop
    For i = 1 To wsComb.UsedRange.Rows.Count
        wsComb.Range("B" & i, "F" & i).Copy
        wsWGP.Range("B4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=True
        Solver wsWGP
        wsWGP.Range("B4:B8").Copy
        wsWGP.Range("B" & 47 + i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=True
        wsWGP.Range("B" & 48 + i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=True
    Next
    End Sub
    
    Sub Solver(ws)
    Application.CommandBars("cell").Reset
    With ws
        SolverOk SetCell:="$K$3", MaxMinVal:=2, ValueOf:=0, ByChange:="$I$4:$I$8", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverOk SetCell:="$K$3", MaxMinVal:=2, ValueOf:=0, ByChange:="$I$4:$I$8", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve
    End With
    End Sub

  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
    =K7*(SUMPRODUCT(O5:O9;Z14:Z18))+L7*(SUMPRODUCT(P5:P9;Z14:Z18))


    I4:I8 are changing cells and whole list of constraints are written below
    B21:B25 >= D21:D25     
    B26:B45 <= D26:D45
    F21:F45 >= H21:H45
    K21:K25 <= M21:M25
    O21:O25 >= M21:M25
    C14 > E14      
    C15 > E15      
    C16 > E16      
    C17 > E17      
    C18 > E18
    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
    SolverReset
    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
    SolverSolve UserFinish:=True
    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

    Sub SolvLoop()
    Dim i As Integer
    
    For i = 1 To 792
    
    Sheets("COMB").Range("B" & i & ":F" & i).Copy
    
    Range("B4").PasteSpecial xlPasteValues, Transpose:=True
    
    Application.CutCopyMode = False
    
    SolverReste
    
    SolverOk SetCell:="$K$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$I$4:$I$8", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        ' All you other constraints
        ' are entered here
        
       SolverSolve UserFinish:=True
        
       Range("K3").Copy
       Range("??").PasteSpecial xlPasteValues
        
    Next
    
    End Sub
    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 ?


    Option Explicit
    
    Dim vAllItems As Variant
    Dim Buffer() As String
    Dim BufferPtr As Long
    Dim Results As Worksheet
    '
    ' Myrna Larson, July 25, 2000, Microsoft.Public.Excel.Misc
    
    Sub ListPermutationsOrCombinations()
    Dim Rng As Range
    Dim PopSize As Integer
    Dim SetSize As Integer
    Dim Which As String
    Dim n As Double
    Const BufferSize As Long = 4096
    
    Worksheets("Sheet1").Range("A1").Select
    Set Rng = Selection.Columns(1).Cells
    If Rng.Cells.Count = 1 Then
    Set Rng = Range(Rng, Rng.End(xlDown))
    End If
    
    PopSize = Rng.Cells.Count - 2
    If PopSize < 2 Then GoTo DataError
    
    SetSize = Rng.Cells(2).Value
    If SetSize > PopSize Then GoTo DataError
    
    Which = UCase$(Rng.Cells(1).Value)
    Select Case Which
    Case "C"
    n = Application.WorksheetFunction.Combin(PopSize, SetSize)
    Case "P"
    n = Application.WorksheetFunction.Permut(PopSize, SetSize)
    Case Else
    GoTo DataError
    End Select
    'pre 2007 use
    'If N > Cells.Count Then GoTo DataError
    '2007 and later use
    If n > Cells.CountLarge Then GoTo DataError
    
    Application.ScreenUpdating = False
    
    Set Results = Worksheets.Add
    
    vAllItems = Rng.Offset(2, 0).Resize(PopSize).Value
    ReDim Buffer(1 To BufferSize) As String
    BufferPtr = 0
    
    If Which = "C" Then
    AddCombination PopSize, SetSize
    Else
    AddPermutation PopSize, SetSize
    End If
    vAllItems = 0
    'remove the leading ' from the next 4 lines to split into columns
        Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
           TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1))
    
    Application.ScreenUpdating = True
    Exit Sub
    
    DataError:
    If n = 0 Then
    Which = "Enter your data in a vertical range of at least 4 cells." _
    & String$(2, 10) _
    & "Top cell must contain the letter C or P, 2nd cell is the Number" _
    & "of items in a subset, the cells below are the values from Which" _
    & "the subset is to be chosen."
    
    Else
    Which = "This requires " & Format$(n, "#,##0") & _
    " cells, more than are available on the worksheet!"
    End If
    MsgBox Which, vbOKOnly, "DATA ERROR"
    Exit Sub
    End Sub
    
    Private Sub AddPermutation(Optional PopSize As Integer = 0, _
    Optional SetSize As Integer = 0, _
    Optional NextMember As Integer = 0)
    
    Static iPopSize As Integer
    Static iSetSize As Integer
    Static SetMembers() As Integer
    Static Used() As Integer
    Dim i As Integer
    
    If PopSize <> 0 Then
    iPopSize = PopSize
    iSetSize = SetSize
    ReDim SetMembers(1 To iSetSize) As Integer
    ReDim Used(1 To iPopSize) As Integer
    NextMember = 1
    End If
    
    For i = 1 To iPopSize
    If Used(i) = 0 Then
    SetMembers(NextMember) = i
    If NextMember <> iSetSize Then
    Used(i) = True
    AddPermutation , , NextMember + 1
    Used(i) = False
    Else
    SavePermutation SetMembers()
    End If
    End If
    Next i
    
    If NextMember = 1 Then
    SavePermutation SetMembers(), True
    Erase SetMembers
    Erase Used
    End If
    
    End Sub 'AddPermutation
    
    Private Sub AddCombination(Optional PopSize As Integer = 0, _
    Optional SetSize As Integer = 0, _
    Optional NextMember As Integer = 0, _
    Optional NextItem As Integer = 0)
    
    Static iPopSize As Integer
    Static iSetSize As Integer
    Static SetMembers() As Integer
    Dim i As Integer
    
    If PopSize <> 0 Then
    iPopSize = PopSize
    iSetSize = SetSize
    ReDim SetMembers(1 To iSetSize) As Integer
    NextMember = 1
    NextItem = 1
    End If
    
    For i = NextItem To iPopSize
    SetMembers(NextMember) = i
    If NextMember <> iSetSize Then
    AddCombination , , NextMember + 1, i + 1
    Else
    SavePermutation SetMembers()
    End If
    Next i
    
    If NextMember = 1 Then
    SavePermutation SetMembers(), True
    Erase SetMembers
    End If
    
    End Sub 'AddCombination
    
    Private Sub SavePermutation(ItemsChosen() As Integer, _
    Optional FlushBuffer As Boolean = False)
    
    Dim i As Integer, sValue As String
    Static RowNum As Long, ColNum As Long
    
    If RowNum = 0 Then RowNum = 1
    If ColNum = 0 Then ColNum = 1
    
    If FlushBuffer = True Or BufferPtr = UBound(Buffer()) Then
    If BufferPtr > 0 Then
    If (RowNum + BufferPtr - 1) > Rows.Count Then
    RowNum = 1
    ColNum = ColNum + 1
    If ColNum > 256 Then Exit Sub
    End If
    
    Results.Cells(RowNum, ColNum).Resize(BufferPtr, 1).Value _
    = Application.WorksheetFunction.Transpose(Buffer())
    RowNum = RowNum + BufferPtr
    End If
    
    BufferPtr = 0
    If FlushBuffer = True Then
    Erase Buffer
    RowNum = 0
    ColNum = 0
    Exit Sub
    Else
    ReDim Buffer(1 To UBound(Buffer))
    End If
    
    End If
    
    'construct the next set
    For i = 1 To UBound(ItemsChosen)
    sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)
    Next i
    
    'and save it in the buffer
    BufferPtr = BufferPtr + 1
    Buffer(BufferPtr) = Mid$(sValue, 3)
    
    End Sub 'SavePermutation
    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

    B21:B25 >= D21:D25     
    B26:B45 <= D26:D45
    F21:F45 >= H21:H45
    K21:K25 <= M21:M25
    O21:O25 >= M21:M25
    C14 > E14      
    C15 > E15      
    C16 > E16      
    C17 > E17      
    C18 > E18
    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.


    Range("B4:B8").Select
        Selection.Copy
        Range("B47").Select
        ActiveCell.Offset(stc, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Range("B48").Select
        
    Range("I4:I8").Select
        Selection.Copy
        Range("A48").Select
        ActiveCell.Offset(stc, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Range("A49").Select
        
      Range("c14:c18").Select
        Selection.Copy
        Range("f48").Select
        ActiveCell.Offset(stc, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Range("f49").Select

  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

    Looking at you copy and paste code it could be simplified

    Range("B4:B8").Copy
        Range("B47").Offset(stc, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        
    Range("I4:I8").Copy
        Range("A48").Offset(stc, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        
      Range("c14:c18").Copy
        Range("f48").Offset(stc, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
    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

  13. #13
    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.

  14. #14
    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.

    Sub solvCostr()
    
    SolverReset
        SolverOk SetCell:="$K$3", MaxMinVal:=2, ValueOf:=0, ByChange:="$I$4:$I$8", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverAdd CellRef:="$B$21:$B$25", Relation:=3, FormulaText:="$D$21:$D$25"
        SolverAdd CellRef:="$C$14", Relation:=3, FormulaText:="$E$14"
        SolverAdd CellRef:="$C$15:$C$18", Relation:=2, FormulaText:="$E$15:$E$18"
        SolverAdd CellRef:="$F$21:$F$45", Relation:=3, FormulaText:="$H$21:$H$45"
        SolverAdd CellRef:="$K$21:$K$25", Relation:=1, FormulaText:="$M$21:$M$25"
        SolverAdd CellRef:="$O$21:$O$25", Relation:=3, FormulaText:="$M$21:$M$25"
        SolverSolve UserFinish:=True
    End Sub
    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
    SolverReset
    and
    SolverSolve UserFinish:=True
    and you also get a second setup of line
    SolverOk SetCell:="$K$3", MaxMinVal:=2, ValueOf:=0, ByChange:="$I$4:$I$8", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
    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

  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.

    SolverSolve UserFinish:=True
    
    Range("B3:B8").Copy
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False, Transpose:=True
    
    Range("I4:I8").Copy
    Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False, Transpose:=True
    
    Range("C14:C18").Copy
    Sheets("Sheet2").Range("K" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False, Transpose:=True
    
    Application.CutCopyMode = False
    
    Next
    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.

    Sub TrimVal()
    Dim cell As Range
    
    For Each cell In ActiveSheet.UsedRange
    
    cell = Trim(cell)
    
    Next
    
    End Sub
    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