Excel: Get Value from Sheet 1 to Sheet 2
Closed
BashirYazbik
BashirYazbik
- Posts
- 58
- Registration date
- Monday November 9, 2015
- Status
- Member
- Last seen
- December 6, 2015
BashirYazbik
- Posts
- 58
- Registration date
- Monday November 9, 2015
- Status
- Member
- Last seen
- December 6, 2015
Related:
- Excel: Get Value from Sheet 1 to Sheet 2
- Excel - Copy rows from sheet 1 to sheet 2 - How-To - Excel
- Excel copy sheet 1 to sheet 2 - Guide
- Excel vba create new sheet with name from cell - Guide
- How to automatically update one excel worksheet from another sheet ✓ - Forum - Excel
- Excel macro compare two sheets and highlight differences ✓ - Forum - Excel
3 replies
vcoolio
Nov 18, 2015 at 02:13 AM
- Posts
- 1345
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- May 20, 2022
Nov 18, 2015 at 02:13 AM
Hello Bashir,
Modify the code slightly as follows:-
As far as I can tell, your original code is trying to transfer the formulae also (hence the error as the code doesn't say anything about transferring the formulae) whereas you only need the values transferred to sheet 2, hence PasteSpecial xlPasteValues.
You also need to disable Events at the begining of the code and then enable them again at the end of the code ( as I've done above for you).
I hope that this helps.
Cheerio,
vcoolio.
Modify the code slightly as follows:-
Private Sub Worksheet_Activate() Application.EnableEvents = False Sheets("Sheet1").Range("A6:F6").Copy Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues Application.EnableEvents = True Application.CutCopyMode = False End Sub
As far as I can tell, your original code is trying to transfer the formulae also (hence the error as the code doesn't say anything about transferring the formulae) whereas you only need the values transferred to sheet 2, hence PasteSpecial xlPasteValues.
You also need to disable Events at the begining of the code and then enable them again at the end of the code ( as I've done above for you).
I hope that this helps.
Cheerio,
vcoolio.
vcoolio
Nov 19, 2015 at 12:24 AM
- Posts
- 1345
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- May 20, 2022
Nov 19, 2015 at 12:24 AM
Hello Bashir,
You're welcome. Glad that I could help.
@RayH:-
Thanks Ray. Good idea. I forgot about by-passing the clipboard.
Cheerio Gentlemen.
vcoolio.
You're welcome. Glad that I could help.
@RayH:-
Thanks Ray. Good idea. I forgot about by-passing the clipboard.
Cheerio Gentlemen.
vcoolio.
BashirYazbik
Nov 19, 2015 at 11:13 PM
- Posts
- 58
- Registration date
- Monday November 9, 2015
- Status
- Member
- Last seen
- December 6, 2015
Nov 19, 2015 at 11:13 PM
I still have one issue please.
The YELLOW part shown in the picture is the values got from sheet 1.
Every time the YELLOW part is updated, I need the GREEN part to be auto updated and keep adding down the row for the whole month. Please see picture.

I inserted the following code in VBA --> sheet 2:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim goout As Boolean
goout = False
xrow = 1
Set KeyCells = Range("A1:F1")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
xcolumn = Range(Target.Address).Column
Do
If Cells(xrow, xcolumn + 10).Value = "" Then
Cells(xrow, xcolumn + 10).Value = Cells(1, xcolumn).Value
goout = True
Else
xrow = xrow + 1
End If
Loop Until goout = True
End If
End Sub
If I update the YELLOW part manually, it will work fine and the GREEN part will auto update.
If I assign the code to an APPLY BUTTON in Sheet 1, the YELLOW part update but the GREEN part do not.
Thank you again for your help.
The YELLOW part shown in the picture is the values got from sheet 1.
Every time the YELLOW part is updated, I need the GREEN part to be auto updated and keep adding down the row for the whole month. Please see picture.

I inserted the following code in VBA --> sheet 2:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim goout As Boolean
goout = False
xrow = 1
Set KeyCells = Range("A1:F1")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
xcolumn = Range(Target.Address).Column
Do
If Cells(xrow, xcolumn + 10).Value = "" Then
Cells(xrow, xcolumn + 10).Value = Cells(1, xcolumn).Value
goout = True
Else
xrow = xrow + 1
End If
Loop Until goout = True
End If
End Sub
If I update the YELLOW part manually, it will work fine and the GREEN part will auto update.
If I assign the code to an APPLY BUTTON in Sheet 1, the YELLOW part update but the GREEN part do not.
Thank you again for your help.
Nov 18, 2015 at 08:15 AM
This work great for me. Thank you so much for the solution and the explanation.
Much appreciated.
Nov 18, 2015 at 03:01 PM
Nov 19, 2015 at 08:19 AM
Thank you so much.