VBA Cell Value used in Worksheet("")

[ChloeDage22]

This is my code. Im trying to refer to a cell A1 which holds the name of the worksheet DATA. And i want that cell to be referenced in my code. Could anyone help me as to why this is not working

Sub CommandButton1_Click()
a = Worksheets(Range("A1")).Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To a
        If Worksheets(Range("A1")).Cells(i, 6).Value = "NHOLOHAN" Then
            Worksheets(Range("A1")).Rows(i).Copy
           
           Worksheets("DAYS").Activate
            b = Worksheets("DAYS").Cells(Rows.Count, 1).End(xlUp).Row
                Worksheets("DAYS").Cells(b + 1, 1).Select
            ActiveSheet.Paste
          
                    Worksheets(Range("A1")).Activate
End If

 

[Rick Rothstein]

Please describe "not working" for us.

 

[ChloeDage22]

It wont compile

 

[RoryA]

Do you have at least a Next and an End Sub line after that?

 

[ChloeDage22]

Yes i do

 

[Rick Rothstein]

This is my code. Im trying to refer to a cell A1 which holds the name of the worksheet DATA. And i want that cell to be referenced in my code. Could anyone help me as to why this is not working

Sub CommandButton1_Click()
a = Worksheets(Range("A1").Value).Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To a
        If Worksheets(Range("A1").Value).Cells(i, 6).Value = "NHOLOHAN" Then
            Worksheets(Range("A1").Value).Rows(i).Copy
         
           Worksheets("DAYS").Activate
            b = Worksheets("DAYS").Cells(Rows.Count, 1).End(xlUp).Row
                Worksheets("DAYS").Cells(b + 1, 1).Select
            ActiveSheet.Paste
        
                    Worksheets(Range("A1").Value).Activate
End If

Try adding all of the ".Value" property designations that I show in red above.

 

[Eric W]

You might also consider a non-looping variation of your macro like this:

Sub test1()
    On Error Resume Next
    With Worksheets(Range("A1").Value)
        .Range("F:F").AutoFilter Field:=1, Criteria1:="NHOLOHAN"
        .Range("A2:A10000").SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("DAYS").Cells(Rows.Count, "A").End(xlUp).Offset(1)
        .Range("F:F").AutoFilter
    End With
    
End Sub

 

[ChloeDage22]

That worked! Thank you

 

[ChloeDage22]

I'm now getting this error '9' Subscript out of range for this line. I'm not quite sure why

Sub CommandButton1_Click()
a = Worksheets(Range("A1").Value).Cells(Rows.Count, 1).End(xlUp).Row

For i = 3 To a
If Worksheets(Range("A1").Value).Cells(i, 6).Value = "NHOLOHAN" Then
Worksheets(Range("A1").Value).Rows(i).Copy

Worksheets(Range("G1").Value).Activate
b = Worksheets(Range("G1").Value).Cells(Rows.Count, 1).End(xlUp).Row
Worksheets(Range("G1").Value).Cells(b + 1, 1).Select
ActiveSheet.Paste

Worksheets(Range("A1").Value).Activate
End If
Next

For i = 3 To a
If Worksheets(Range("A1").Value).Cells(i, 6).Value = "JODENTHAL" Then
Worksheets(Range("A1").Value).Rows(i).Copy

Worksheets(Range("G1").Value).Activate
b = Worksheets(Range("G1").Value).Cells(Rows.Count, 1).End(xlUp).Row
Worksheets(Range("G1").Value).Cells(b + 1, 1).Select
ActiveSheet.Paste



Worksheets(Range("A1").Value).Activate
End If