Copying cell values in sheet 1 to cells in sheet 2

**coasterman** · 12-17-2011, 03:04 PM

As part of the workbook I have a scenario sheet on sheet2 which is partly dependent on user input values on sheet1. The output of sheet2 is then pasted to the next available row in sheet3. At present I am having to revert back to sheet 1 to change the user values and then back to sheet 2 to run the scenario. These sheet 1 input cells need to remain on sheet1 as they are where users spend most time. I know I could simply record a macro and cut and paste the various cells one by one into sheet1 cells but is there a more elegant coding solution?

There are only about 8 values to change if that helps.

Many thanks

**mikerickson** · 12-18-2011, 07:40 PM

There might be a more elegant solution, but that solution would be eaiser to find if you recorded the (ugly) macro and posted that.

**coasterman** · 12-19-2011, 09:39 AM

That would be more instructive I would have to agree

Sub LOECalxChangeValues()
'
' LOECalcChangeValues Macro
'

'
    Application.ScreenUpdating = False
    Sheets("LOETblCalx").Select
    Range("Q11:R14").Select
    Selection.Copy
    Sheets("Home").Select
    Range("F28").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("LOETblCalx").Select
    Range("p12").Select
    Selection.Copy
    Sheets("Home").Select
    Range("F19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("LOETblCalx").Select
    Range("P12").Select
    Application.ScreenUpdating = True
     
    
End Sub

**mikerickson** · 12-19-2011, 03:54 PM

With Sheets("LOETblCalx")
    With .Range("Q11:R14")
        Sheets("Home").Range("F28").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
    Sheets("Home").Range("F19").Value = .Range("P12").Value
    .Range("P12").Select
End With

**coasterman** · 12-20-2011, 02:53 PM

Thank you very much for th. I think I can see how I now need to re-write much of the code in my workbook as it contains dozens of Select Copy Paste routines. Hopefully by the end of re-writing I will have a proper feel for the use of With statement in this sort of context.

**mikerickson** · 12-20-2011, 04:01 PM

The imporant part of the code I posted wasn't the use of With..EndWith (although that is a very useful feature of VBA), but removing the Selection makes the code both faster and more robust.

Select is almost never nessesary.