Forum Discussion

hopr37 hotmail's avatar
hopr37 hotmail
Brass Contributor
Apr 16, 2018

Copy specific cell to new worksheet but keep original cell value

Trying to copy a specific cell(s) from sheet 1 to sheet 2 when a value in said cell changes.

example:

part 1.

sheet1 cell B2

value=1

sheet1 cell E2

Value is 10

 

Copies value from sheet1 cell B2 and sheet1 cell E2 to worksheet2 cell B2 and E2

part 2.

Sheet1 cell B2

Value changes to 2

sheet1 cell E2

Value is 20

copies new value from sheet1 cell B2 and E2 to sheet2 but moves new value down 1 cell to B3 and E3 but also leaving sheet2 cell B2 and cell E2 as is.

So everytime the value of sheet1 B2 and E2 changes, it copies that new changes to sheet2 but moves the new values down one row.

Thank You for any help.

 

 

 

copy cell
Macros & VBA

10 Replies

Sort By
    • hopr37 hotmail's avatar
      hopr37 hotmail
      Brass Contributor
      Apr 16, 2018

      That works awsome! Thank you.

      Now. One more question. ( I didn't want to make things too complicated for myself until I understood what you did with this.)
      How would you make it so that ANY cell in columns B and E change could it do the same thing?

       

      Also, I can't seem to get this to work if I change the sheet names. I've tried using Sh4 ( in place of Sh2) but that doesn't work.
      I've tried changing sh1 to a name and sh2 to a name ( I have multiple tabs with different names) but that doesn't work either. It works great in your spreadsheet buy not mine. 

      • Haytham Amairah's avatar
        Haytham Amairah
        Silver Contributor
        Apr 16, 2018

        Hi,

         

        The solution is powered by this code:

        Sub test()
         On Error Resume Next
         Application.ScreenUpdating = False
         
         Dim r1, r2 As Integer
         
         r1 = Application.WorksheetFunction.CountA(Sh2.Range("B:B"))
         r2 = Application.WorksheetFunction.CountA(Sh2.Range("E:E"))
         
         Sh2.Range("B1").Offset(r1).Value = Sh1.Range("B2").Value
         Sh2.Range("E1").Offset(r2).Value = Sh1.Range("E2").Value
         Sh2.Activate
         Sh2.Range("E1").Offset(r2).Select
         
         Application.ScreenUpdating = True
         On Error GoTo 0
        End Sub

         

         

        This is the explanation of the code (line by line):

         

        To prevent any error message from appearing

        On Error Resume Next

         

        To turn off the screen updates and flickers that can appear while the code is running

        Application.ScreenUpdating = False

         

        To count the numbers of the non-blanks cells in column B of the sheet 2 and assign it to the variable r1

        r1 = Application.WorksheetFunction.CountA(Sh2.Range("B:B"))

         

        To count the numbers of the non-blanks cells in column E of the sheet 2 and assign it to the variable r2

        r2 = Application.WorksheetFunction.CountA(Sh2.Range("E:E"))

          

        The below code sets the focus on the cell B1 of the Sheet 2, and go down (offset) by the number of the non-blanks cells in column B of the Sheet 2, and then it assigns the value of the cell B2 of the sheet 1 to the new cell in column B of the Sheet 2.
        NOTE: The number the non-blanks cells is already assigned to the variable r1, so I passed this variable to the Offset function: Offset(r1).

        Sh2.Range("B1").Offset(r1).Value = Sh1.Range("B2").Value

         

        This is the same code above but for the column E of the Sheet 2

        Sh2.Range("E1").Offset(r2).Value = Sh1.Range("E2").Value

         

        To activate the Sheet 2

        Sh2.Activate

         

        To select the last filled cell in column E of the Sheet2 after the process is finished

        NOTE: This code is bonus and you can delete it!

        Sh2.Range("E1").Offset(r2).Select

         

        These are the last two lines which are used to restore the default status of the screen updating and error handling

         Application.ScreenUpdating = True
         On Error GoTo 0

         

        I hope this is clear enough
        Haytham

Resources