VBA Code works with worksheet_change but not as macro when called?

[jack109]

Hi, the code below works when the target cell ES1 is changed but wont work if I manually call it. I'm guessing it not as easy as changing Private Sub Worksheet_Change(ByVal Target As Range) to Sub Copy data(). Obviously part of the code needs to be changed but I can't see where with my limited VBA knowledge.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim sht, actSh As Worksheet
Dim aRow, lRow As Long
Dim srcH As String

Set actSh = ActiveSheet
srcH = actSh.Range("ES1").Value

If Target.Address = "$ES$1" Then
    If srcH = "" Then Exit Sub
        aRow = actSh.Cells(Rows.Count, 2).End(xlUp).Row
        If aRow > 7 Then actSh.Range("B7:AA" & aRow).ClearContents
        For Each sht In ActiveWorkbook.Sheets
            If sht.Name = srcH Then
                lRow = Sheets(srcH).Cells(Rows.Count, 2).End(xlUp).Row
                Sheets(srcH).Range("B7:AA" & lRow).Copy
                actSh.Range("B7").PasteSpecial xlPasteValues
                Exit Sub
            End If
        Next sht
    MsgBox "Unable to find sheet named: " & actSh.Range("ES1").Value
End If
Application.ScreenUpdating = True
End Sub

 

[HighAndWilder]

Hi, the code below works when the target cell ES1 is changed but wont work if I manually call it. I'm guessing it not as easy as changing Private Sub Worksheet_Change(ByVal Target As Range) to Sub Copy data(). Obviously part of the code needs to be changed but I can't see where with my limited VBA knowledge.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim sht, actSh As Worksheet
Dim aRow, lRow As Long
Dim srcH As String

Set actSh = ActiveSheet
srcH = actSh.Range("ES1").Value

If Target.Address = "$ES$1" Then
    If srcH = "" Then Exit Sub
        aRow = actSh.Cells(Rows.Count, 2).End(xlUp).Row
        If aRow > 7 Then actSh.Range("B7:AA" & aRow).ClearContents
        For Each sht In ActiveWorkbook.Sheets
            If sht.Name = srcH Then
                lRow = Sheets(srcH).Cells(Rows.Count, 2).End(xlUp).Row
                Sheets(srcH).Range("B7:AA" & lRow).Copy
                actSh.Range("B7").PasteSpecial xlPasteValues
                Exit Sub
            End If
        Next sht
    MsgBox "Unable to find sheet named: " & actSh.Range("ES1").Value
End If
Application.ScreenUpdating = True
End Sub

Can you post the new procedure that you have created out of the worksheet_change one?

If I need to run some code in this way I create another sub which I then call from Worksheet_Change.

 

[jack109]

Sub copy_data()
Application.ScreenUpdating = False
Dim sht, actSh As Worksheet
Dim aRow, lRow As Long
Dim srcH As String

Set actSh = ActiveSheet
srcH = actSh.Range("ES1").Value

If Target.Address = "$ES$1" Then
    If srcH = "" Then Exit Sub
        aRow = actSh.Cells(Rows.Count, 2).End(xlUp).Row
        If aRow > 7 Then actSh.Range("B7:AA" & aRow).ClearContents
        For Each sht In ActiveWorkbook.Sheets
            If sht.Name = srcH Then
                lRow = Sheets(srcH).Cells(Rows.Count, 2).End(xlUp).Row
                Sheets(srcH).Range("BA:AA" & lRow).Copy
                actSh.Range("B7").PasteSpecial xlPasteValues
                Exit Sub
            End If
        Next sht
    MsgBox "Unable to find sheet named: " & actSh.Range("ES1").Value
End If
Application.ScreenUpdating = True
End Sub

I thought if I just dropped the worksheet_change it would be fine. Instead of firing when via changing a cell, I could get it to fire when assigning a macro to a button. When I do that i'm getting a runtime error 424: object required.

Basically I want the code to copy and paste from said sheet that is the value in ES1 and instead of pasting to active sheet , paste to a sheet called "DATA" but Im trying to do that by editing the code one step at a time. Obviously I have fallen at the first hurdle.

cheers

 

[HighAndWilder]

Sub copy_data()
Application.ScreenUpdating = False
Dim sht, actSh As Worksheet
Dim aRow, lRow As Long
Dim srcH As String

Set actSh = ActiveSheet
srcH = actSh.Range("ES1").Value

If Target.Address = "$ES$1" Then
    If srcH = "" Then Exit Sub
        aRow = actSh.Cells(Rows.Count, 2).End(xlUp).Row
        If aRow > 7 Then actSh.Range("B7:AA" & aRow).ClearContents
        For Each sht In ActiveWorkbook.Sheets
            If sht.Name = srcH Then
                lRow = Sheets(srcH).Cells(Rows.Count, 2).End(xlUp).Row
                Sheets(srcH).Range("BA:AA" & lRow).Copy
                actSh.Range("B7").PasteSpecial xlPasteValues
                Exit Sub
            End If
        Next sht
    MsgBox "Unable to find sheet named: " & actSh.Range("ES1").Value
End If
Application.ScreenUpdating = True
End Sub

I thought if I just dropped the worksheet_change it would be fine. Instead of firing when via changing a cell, I could get it to fire when assigning a macro to a button. When I do that i'm getting a runtime error 424: object required.

Basically I want the code to copy and paste from said sheet that is the value in ES1 and instead of pasting to active sheet , paste to a sheet called "DATA" but Im trying to do that by editing the code one step at a time. Obviously I have fallen at the first hurdle.

cheers

I've not looked at what you are trying to do BUT the variable Target is sent by the system to various event handler procedures, including Worksheet change, and you don't have that argument.

You reference Target but it is not dimmed or assigned a range value.

 

[jack109]

I think I've cracked it? It seems to work when I delete these two lines

If Target.Address = "$ES$1" Then
    If srcH = "" Then Exit Sub

 

[Joe4]

I think I've cracked it? It seems to work when I delete these two lines

If Target.Address = "$ES$1" Then
    If srcH = "" Then Exit Sub

As HighAndWilder explained, your other code does not work because Target is not included in that procedure, so Excel VBA does not know what you are referring to!

Note in the "Worksheet_Change" procedure, it is a parameter being passed in, namely the cell(s) updated that trigger the code to run:

Private Sub Worksheet_Change(ByVal Target As Range)

But in your other procedure, there is no reference to Target, so it isn't anything.

The question is on your other code, do you want any restriction regarding that range?
Maybe if you only want that to run if E1 is the active cell, you can change that IF statement to this:

If ActiveCell.Address = "$ES$1" Then

If you remove it altogether, it will do no checks, and always run that section when you kick off the code manually.

 

[jack109]

I think it’s doing what I want it to do, well it seems to be without any issues. Heres some context to what it’s or what I’m trying to achieve.

I have a sheet called “BREAKDOWN” which I use to summarise data. This sheet has a data validation cell (B2) with which contains all the sheet names I want to summarise. When that cell is changed (B2) a worksheet_change code copies the value of B2 to ES1 in a sheet called “DATA”. It then calls the macro that I modified above , which copies data from the said sheet name that is set in ES1 to “DATA”.

So the summary sheet is basically refreshing the data I’m summarising in the “background” via the VBA code.

As I said it seems to working now and I’m yet to encounter any issues.