none
Using Cell Value as the Cell Reference in a Range RRS feed

  • Question

  • I have a worksheet "TempData" where I Identify the Cell from another sheet "PermData" that is the next cell I want to place data. I use a formula in Cell B6 to identify the next available cell in "PermData".  B6 will return a value like K56 which would be the next cell i want to use.

    I'd like to move the value that resides  in "TempData"  C10 to "PermData" to the cell referenced in "TempData" B6.

    Here is the vba which is obviously failing:

    Public Function MoveData()

    ScreenUpdating = False

    Dim Stat1 As String

    Stat1 = Worksheets("TempData").Cells(6, "B").Value

    ThisWorkbook.Worksheets("PermData").Range(Stat1).Value = ThisWorkbook.Worksheets("TempData").Range("C10").Value

    End Function

    Your help is greatly appreciated.





    • Edited by Qaspec Wednesday, August 27, 2014 8:01 PM
    Wednesday, August 27, 2014 7:38 PM

Answers

  • The only problem I see is that you should use Application.ScreenUpdating instead of just ScreenUpdating. The following works for me:

    Public Function MoveData()
        Application.ScreenUpdating = False
        Dim Stat1 As String
        Stat1 = Worksheets("TempData").Cells(6, "B").Value
        ThisWorkbook.Worksheets("PermData").Range(Stat1).Value = _
            ThisWorkbook.Worksheets("TempData").Range("C10").Value
        Application.ScreenUpdating = True
    End Function


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 27, 2014 8:49 PM