Apr 16 2018
05:26 AM
- last edited on
Jul 25 2018
09:57 AM
by
TechCommunityAP
Apr 16 2018
05:26 AM
- last edited on
Jul 25 2018
09:57 AM
by
TechCommunityAP
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.
Apr 16 2018 06:54 AM
Hi,
Please test the solution in the attached file.
It's a fairly good solution because you need to press a button to move the new records to the second sheet.
Apr 16 2018 07:50 AM - edited Apr 16 2018 09:15 AM
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.
Apr 16 2018 09:37 AM
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
Apr 16 2018 09:45 AM - edited Apr 16 2018 09:49 AM
Thank you so much. I figured out what I was doing wrong so I have the original formula working.
Sh2.Range
I had to change that to: Sheets("the name of my sheet").Range
Now I'll look over what you just posted and get back to you.
I really appreciate the help.
Is it simply to make a change so that ANY value that is changed in column B and column E would do the same?
Apr 16 2018 10:14 AM - edited Apr 16 2018 10:16 AM
Do you mean that you want to get rid of using the button and make this code triggers in case if the cell B2 or E2 of the Sheet 1 is changed?
If so, please right-click the Sheet 1 tab and choose View Code, and then copy and paste this code there:
Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B2, E2")) Is Nothing Then
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
End If
Application.ScreenUpdating = True
On Error GoTo 0
End Sub
Apr 16 2018 10:45 AM - edited Apr 16 2018 11:44 AM
well I did remove the button and got it to work automatically and it works great!
But how difficult would it be to make it so on any of the "B" columns it would do the same thing? Not just B2 and E2? So any time I change any value in columns "B" or "E" it would still work the same?
basically do it's code no matter what cell in column "B" get's changed.
Apr 17 2018 08:48 AM
@hopr37 hotmail wrote:well I did remove the button and got it to work automatically and it works great!
But how difficult would it be to make it so on any of the "B" columns it would do the same thing? Not just B2 and E2? So any time I change any value in columns "B" or "E" it would still work the same?
basically do it's code no matter what cell in column "B" get's changed.
In this case, please test this code:
Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B:B, E:E")) Is Nothing Then
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("B" & Target.Row).Value
Sh2.Range("E1").Offset(r2).Value = Sh1.Range("E" & Target.Row).Value
Sh2.Activate
Sh2.Range("E1").Offset(r2).Select
End If
Application.ScreenUpdating = True
On Error GoTo 0
End Sub
Apr 17 2018 09:47 AM - edited Apr 17 2018 09:58 AM
That works awesome!
Thank you so much!
Jun 16 2020 01:44 PM
Hi @Haytham Amairah! I found this to be super helpful, but I want to do this for multiple columns (D to BB). I'm new to VBA, so I'm not sure how to iterate through columns. Can you offer some pointers? Thanks in advance!
Jan 30 2021 11:05 PM