Previous Topic Next Topic

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.
Related:

3 replies

Answer 1 / 3
vcoolio Posts 1364 Registration date Thursday July 24, 2014 Status Moderator Last seen February 10, 2023 252
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
Answer 2 / 3
vcoolio Posts 1364 Registration date Thursday July 24, 2014 Status Moderator Last seen February 10, 2023 252
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
Answer 3 / 3
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