Sheetname as a variable

[jomili]

If I have this line in my macro triggered to run when I click in a specific cell :

ThisWorkbook.Worksheets("SourceSheet").Range("A1").Formula= Sheet2.Range("A1").Formula

and in that cell I clicked I have the name of the worksheet I want to copy to (instead of "Sheet2", maybe "DestSheet"), how do I change Sheet2 to DestSheet?

[Richard Buttrey]

Hi,

If I've understood correctly try:

ThisWorkbook.Worksheets("SourceSheet").Range("A1").Formula = Sheets(ActiveCell.Value).Range("A1").Formula

HTH

[jomili]

Richard,

I tried your code, as shown below, but it didn't work. It gives me a subscript out of range error.

Private Sub Worksheet_Change(ByVal Target As Range)


Worksheets("Masterlist").Range("A15").Formula = Sheets(ActiveCell.Value).Range("A15").Formula

End Sub

So then I tried adapting come code I was using in another workbook. The original code copies the activecell value to another sheet. I tried to adapt it to copy the range of the row I want to copy and paste (columns A through F of the row I make the change in), but that also didn't work:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim strValue As String

i = Target.Row
strValue = Sheets("Masterlist").Cells(i, "A:F").Value

Sheets(ActiveCell.Value).Cells(i, "A:F").Value = strValue
End Sub

[Richard Buttrey]

Hi,

Can you upload your workbook, anonymised if necessary?

Rgds

[jomili]

You bet.

Essentially what I want to do at this step is: whenever I add a line into Masterlist, and click the dropdown for whatever region it belongs to, the macro will pick up that line and paste it into the appropriate worksheet. Later on I'll put more routines in to calculate the time remaining and send out emails of the individual worksheets to the responsible parties.

The reports on the Masterlist will change as new reports get added, and as some become no longer necessary (is there a way to delete the corresponding line on the appropriate worksheet if I delete it in the Masterlist? Something else to think about...).

I appreciate your help getting me started. I'm still a neophyte at this VBA stuff, so get lost pretty easily.

[Richard Buttrey]

Hi,

Try the following procedure

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("I:I"), Target) Is Nothing Then
        ActiveCell.EntireRow.Copy Destination:=Sheets(Target.Value).Range("A" & Application.Rows.Count).End(xlUp).Offset(1, 0)
    End If

End Sub

This will update the relevant sheet when you make a change in column I.

Note however that it will add the MasterList row whenever you change column I cells, so you'll probably want to build in an additional check that the row doesn't already exist in the destination sheet, but hopefully this will get you started.

Rgds