I have the following code that copies whole row and the 3 rows under to a worksheet called "INTERESTS" when a number of letter is found in column E.
Could someone please change it so it could be run as a macro for a workbook with 50 sheet? worksheet names are 1,2,3,4,5,6 and so on
Thanks
Could someone please change it so it could be run as a macro for a workbook with 50 sheet? worksheet names are 1,2,3,4,5,6 and so on
Thanks
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nr As Long 'next row
Dim ws As Worksheet
If Target.Column = 5 And Target.Cells.Count = 1 Then
Select Case LCase(Trim(Target.Value))
Case "1"
Set ws = Worksheets("INTERESTS")
Case "2"
Set ws = Worksheets("INTERESTS")
Case "3"
Set ws = Worksheets("INTERESTS")
Case "d2"
Set ws = Worksheets("INTERESTS")
Case "d3"
Set ws = Worksheets("INTERESTS")
End Select
If Not ws Is Nothing Then
nr = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'get next empty row on data sheet
Rows(Target.Row & ":" & Target.Row + 3).Copy 'copy the row and the next 3 rows
ws.Rows(nr).PasteSpecial xlPasteValues 'paste the values in the next available row
End If
End If
End Sub