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

jack109

Board Regular
Joined
May 10, 2020
Messages
71
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 
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.

VBA Code:
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.
 
Upvote 0
VBA Code:
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
 
Upvote 0
VBA Code:
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.
 
Upvote 0
I think I've cracked it? It seems to work when I delete these two lines


VBA Code:
If Target.Address = "$ES$1" Then
    If srcH = "" Then Exit Sub
 
Upvote 0
I think I've cracked it? It seems to work when I delete these two lines


VBA Code:
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:
Rich (BB code):
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:
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top