Excel: Get Value from Sheet 1 to Sheet 2Closed

Question

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.

vcoolio · Accepted Answer

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.

vcoolio · Answer

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.

BashirYazbik · Answer

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.

Excel: Get Value from Sheet 1 to Sheet 2

3 replies

Subscribe To Our Newsletter!