Compile error: Invalid use of property

[onlyonekj]

Good Afternoon,

I am trying to copy a worksheet multiple times and rename them using a MasterList. The VBA code I've used in the past before worked but now I am receiving "Compile error: Invalid use of property". Below is the code I am using. Requesting assistance to fix this error. Thanks.

Sub MakeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("250")
Set sh2 = Sheets("MasterList")
For Each c In sh2.Range("A251:A300")
sh1.Copy After: Sheets (Sheet.Count)
ActiveSheet.Name = c.Value
Next
End Sub

 

[RoryA]

You need After:= not just After:

 

[bstory84]

Also, Sheets.count instead of Sheet.count

 

[onlyonekj]

You need After:= not just After:

Thank you for responding. I tried that and now I am getting "Run-time error '424': Object required" in the portion with red font.

Sub MakeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("250")
Set sh2 = Sheets("MasterList")
    For Each c In sh2.Range("A251:A300")
       [COLOR=rgb(184, 49, 47)] sh1.Copy After:=Sheets(Sheet.Count)[/COLOR]
        ActiveSheet.Name = c.Value
    Next
End Sub

 

[onlyonekj]

Thank you for responding. I tried that and now I am getting "Run-time error '424': Object required" in the portion with red font.

Sub MakeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("250")
Set sh2 = Sheets("MasterList")
    For Each c In sh2.Range("A251:A300")
       [COLOR=rgb(184, 49, 47)] sh1.Copy After:=Sheets(Sheet.Count)[/COLOR]
        ActiveSheet.Name = c.Value
    Next
End Sub

RoryA, I also made the other change you recommended sh1.Copy After:=Sheets(Sheet.Count) to sh1.Copy After:=Sheet(Sheet.Count) but still get the Run-time error

Sub MakeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("250")
Set sh2 = Sheets("MasterList")
    For Each c In sh2.Range("A251:A300")
        sh1.Copy After:=Sheet(Sheet.Count)
        ActiveSheet.Name = c.Value
    Next
End Sub

 

[onlyonekj]

RoryA, I also made the other change you recommended sh1.Copy After:=Sheets(Sheet.Count) to sh1.Copy After:=Sheet(Sheet.Count) but still get the Run-time error

Sub MakeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("250")
Set sh2 = Sheets("MasterList")
    For Each c In sh2.Range("A251:A300")
        sh1.Copy After:=Sheet(Sheet.Count)
        ActiveSheet.Name = c.Value
    Next
End Sub

RoryA, I saw where I deleted the wrong. I have fixed it and the formula works now.

 

[onlyonekj]

@RoryA and @bstory84
I just want to say thank you for your help. Your suggestions worked perfectly. I also wanted to post the correct coding below and check this reply as solved in case it can be of use for someone else.

Sub MakeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("250")
Set sh2 = Sheets("MasterList")
For Each c In sh2.Range("A251:A300")
sh1.CopyAfter:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value
Next
End Sub