Help moving a VBA code from one workbook to another- with different sheet name

[air044]

i need help using this code in another work book with different name. When I change the name of the sheet 2 to the new sheet of Table- it doent work the cells go blank and code doesn;t work. Can someone tell me what I'm doing wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, LR As Long, NR1 As Long, NR2 As Long

If Not Intersect(Target, Range("E4")) Is Nothing Then
    Application.EnableEvents = False
    Range("E21:N30").ClearContents
    NR1 = 21
    NR2 = 21
    With Sheets("Sheet2")
        .AutoFilterMode = False
        .Rows(1).AutoFilter 3, "*" & Range("E4") & "*"
        LR = .Range("C" & .Rows.Count).End(xlUp).Row
        If LR > 1 Then
            For Each cell In .Range("M2:M" & LR).SpecialCells(xlVisible)
                Select Case UCase(cell.Value)
                    Case "ME"
                        Range("E" & NR1).Value = .Cells(cell.Row, "K").Value
                        Range("H" & NR1).Value = .Cells(cell.Row, "B").Value
                        Range("I" & NR1).Value = .Cells(cell.Row, "I").Value
                        NR1 = NR1 + 1
                    Case "THEM"
                        Range("J" & NR2).Value = .Cells(cell.Row, "K").Value
                        Range("M" & NR2).Value = .Cells(cell.Row, "B").Value
                        Range("N" & NR2).Value = .Cells(cell.Row, "I").Value
                        NR2 = NR2 + 1
                End Select
            Next cell
        Else
            Range("E21") = "none"
        End If
        .AutoFilterMode = False
    End With
    Application.EnableEvents = True
End If
End Sub

[StephenR]

Please add code tags as per the forum rules.

[Greg M]

Hi there,

I'm trying to figure out what might (or might not!) be happening at your end.

What do you mean when you say the cells "go blank"? Are the values cleared from all of the cells? Does the AutoFilter hide all of the data rows? Does something else happen?

The Worksheet_Change event is related to the worksheet which contains the Worksheet_Change routine in its VBA CodeModule - have you entered a copy of your code in the CodeModule for the "Table" worksheet?

You don't need to refer to a worksheet by its Name when the code involved is contained in the CodeModule of that worksheet, so instead of using:

With Sheets("Sheet2")

you can use:

With Me

Hope the above helps - please let me know how you get on.

Regards,

Greg M

[air044]

works normal until i change the sheet title to TABLE, then i get Debug error:



Then after hitting debug I get code error:




and the cells in HQ table no longer pulls data from sheet 2(Table)?? help??

debug.pngcode.jpg

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, LR As Long, NR1 As Long, NR2 As Long

If Not Intersect(Target, Range("E4")) Is Nothing Then
    Application.EnableEvents = False
    Range("E21:N30").ClearContents
    NR1 = 21
    NR2 = 21
    With Sheets("LOG")
        .AutoFilterMode = False
        .Rows(1).AutoFilter 3, "*" & Range("E4") & "*"
        LR = .Range("C" & .Rows.Count).End(xlUp).Row
        If LR > 1 Then
            For Each cell In .Range("M2:M" & LR).SpecialCells(xlVisible)
                Select Case UCase(cell.Value)
                    Case "ME"
                        Range("E" & NR1).Value = .Cells(cell.Row, "K").Value
                        Range("H" & NR1).Value = .Cells(cell.Row, "B").Value
                        Range("I" & NR1).Value = .Cells(cell.Row, "I").Value
                        NR1 = NR1 + 1
                    Case "THEM"
                        Range("J" & NR2).Value = .Cells(cell.Row, "K").Value
                        Range("M" & NR2).Value = .Cells(cell.Row, "B").Value
                        Range("N" & NR2).Value = .Cells(cell.Row, "I").Value
                        NR2 = NR2 + 1
                End Select
            Next cell
        Else
            Range("E21") = "none"
        End If
        .AutoFilterMode = False
    End With
    Application.EnableEvents = True
End If
End Sub

This above is how ther error shows in VB " With Sheets ("Log") highlighted yellow. I tried your fix but it didn't work.

[Greg M]

Hi again,

I wasn't able to view either the .png or the .jpg file you posted.

Can you post some/all of your workbook here so we can get a better understanding of how it's supposed to work?

Regards,

Greg M

[air044]

[QUOTE=Greg M;3917451]Hi again,

I wasn't able to view either the .png or the .jpg file you posted.

Can you post some/all of your workbook here so we can get a better understanding of how it's supposed to work?

Regards,

Greg M[/QUOTE

[Greg M]

Hi again,

The following code appears to work in the attached copy of the workbook you posted earlier:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, LR As Long, NR1 As Long, NR2 As Long

If Not Intersect(Target, Range("E4")) Is Nothing Then
    Application.EnableEvents = False
    Range("E21:N30").ClearContents
    NR1 = 21
    NR2 = 21
    With Sheets("TABLE")
        .AutoFilterMode = False
        .Rows(1).AutoFilter 3, "*" & Range("E4") & "*"
        LR = .Range("C" & .Rows.Count).End(xlUp).Row
        If LR > 1 Then
            For Each cell In .Range("M2:M" & LR).SpecialCells(xlVisible)
                Select Case UCase(cell.Value)
                    Case "ME"
                        Range("E" & NR1).Value = .Cells(cell.Row, "K").Value
                        Range("H" & NR1).Value = .Cells(cell.Row, "B").Value
                        Range("I" & NR1).Value = .Cells(cell.Row, "I").Value
                        NR1 = NR1 + 1
                    Case "THEM"
                        Range("J" & NR2).Value = .Cells(cell.Row, "K").Value
                        Range("M" & NR2).Value = .Cells(cell.Row, "B").Value
                        Range("N" & NR2).Value = .Cells(cell.Row, "I").Value
                        NR2 = NR2 + 1
                End Select
            Next cell
        Else
            Range("E21") = "none"
        End If
        .AutoFilterMode = False
    End With
    Application.EnableEvents = True
End If
End Sub

Hope this helps - as always, please let me know how you get on.

Regards,

Greg M


P.S. Many thanks for the Reputation increase