Need help with error in macro

[aashish83]

getting error in the last line....my range is dynamic and may have blank cells in it

Sub Create_Central()

Dim sh1 As Worksheet, sh2 As Worksheet, c As Range

Set sh1 = Sheets("FSR Level A")

Set sh2 = Sheets("BI")



For Each c In sh2.Range("A18:A2700")

sh1.Copy After:=Sheets(Sheets.Count)

Activesheet.Name = c.Value

Next



End Sub

 

[aashish83]

For Each a In sh2.Range("A35:A500", sh2.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)

I am assuming the bold part is meant to address the range A35 down to the last cell in column A with data?
If so, it really should be like this

For Each a In sh2.Range("A35", sh2.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)

Similar for the other columns.

Understood will make the necessary changes. Thanks Peter for all your help and Guidance!

 

[Peter_SSs]

Thanks Peter for all your help and Guidance!

No problem.

 

[aashish83]

No problem.

Hi Peter

Need your help again is it possible if i can hide the sheets "FSR Level A" "FSR Level B" and "FSR Level C" in the new created workbook "Multi" and just have the ones created by the range along with "BI", "Instructions" & "Central Function"

Sub Multi()
With ActiveWorkbook.Sheets(Array("FSR Level B", "BI", "FSR Level A", "FSR Level C", "Instructions", "Central Function"))
    .Copy
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "Multi", FileFormat:=52
End With

Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet
  Dim a As Range, b As Range, c As Range
 
 
  Set sh1 = Sheets("FSR Level A")
  Set sh2 = Sheets("BI")
  Set sh3 = Sheets("FSR Level B")
  Set sh4 = Sheets("FSR Level C")
 
  Application.ScreenUpdating = False
  For Each a In sh2.Range("A35:A500", sh2.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      sh1.Copy After:=Sheets(Sheets.Count)
      Sheets(Sheets.Count).Name = a.Value
  Next a
  Application.ScreenUpdating = True
 
Application.ScreenUpdating = False
  For Each b In sh2.Range("B35:B500", sh2.Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      sh3.Copy After:=Sheets(Sheets.Count)
      Sheets(Sheets.Count).Name = b.Value
  Next b
 
    Application.ScreenUpdating = True
 
Application.ScreenUpdating = False
  For Each c In sh2.Range("C35:C500", sh2.Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      sh4.Copy After:=Sheets(Sheets.Count)
     Sheets(Sheets.Count).Name = c.Value
  Next c
 
    Application.ScreenUpdating = True
  
End Sub

 

[Peter_SSs]

Can you please post the actual code that you are currently using?