Excel: Get Value from Sheet 1 to Sheet 2

Closed
BashirYazbik
Posts
58
Registration date
Monday November 9, 2015
Status
Member
Last seen
December 6, 2015
- Nov 17, 2015 at 10:33 PM
BashirYazbik
Posts
58
Registration date
Monday November 9, 2015
Status
Member
Last seen
December 6, 2015
- Nov 19, 2015 at 11:13 PM
Good evening,

I need help please about what I am wrong.
I have Cells A6 to F6 in Sheet1 has formula in it and I was trying to get the value of those cells to sheet 2 (Cells A1 to F1) using the following formula:

Private Sub Worksheet_Activate()
Sheets("Sheet1").Range("A6:F6").Copy Destination:=Sheets("Sheet2").Range("A1")
End Sub

But it gives me an error.
Thank you so much.

3 replies

vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Nov 18, 2015 at 02:13 AM
Hello Bashir,

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.
3
BashirYazbik
Posts
58
Registration date
Monday November 9, 2015
Status
Member
Last seen
December 6, 2015
14
Nov 18, 2015 at 08:15 AM
VCOOLIO,

This work great for me. Thank you so much for the solution and the explanation.
Much appreciated.
0
rayh > BashirYazbik
Posts
58
Registration date
Monday November 9, 2015
Status
Member
Last seen
December 6, 2015

Nov 18, 2015 at 03:01 PM
Also, this version which does not use the clipboard.

Sheets("Sheet2").Range("A1:F1") = Sheets("Sheet1").Range("A6:F6").Value
0
BashirYazbik
Posts
58
Registration date
Monday November 9, 2015
Status
Member
Last seen
December 6, 2015
14
Nov 19, 2015 at 08:19 AM
Rayh,

Thank you so much.
0
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
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.
1
BashirYazbik
Posts
58
Registration date
Monday November 9, 2015
Status
Member
Last seen
December 6, 2015
14
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.
0