Need help with error in macro

aashish83

Board Regular
Joined
Feb 15, 2022
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
may have blank cells in it
So, what do you want to do when you come to those blank cells?
  1. Create the new sheet but do not try to rename it
  2. Don't create the new sheet
  3. Something else?
 
Upvote 0
Hi, the code will throw error if cell value is blank or have duplicate value
 
Upvote 0
So, what do you want to do when you come to those blank cells?
  1. Create the new sheet but do not try to rename it
  2. Don't create the new sheet
  3. Something else?
2. if its balnk don't create new sheet
 
Upvote 0
2. if its balnk don't create new sheet
Try this then

VBA Code:
Sub Create_Central_v2()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range
  
  Set sh1 = Sheets("FSR Level A")
  Set sh2 = Sheets("BI")
  Application.ScreenUpdating = False
  For Each c In sh2.Range("A18", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      sh1.Copy After:=Sheets(Sheets.Count)
      Sheets(Sheets.Count).Name = c.Value
  Next c
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this then

VBA Code:
Sub Create_Central_v2()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range
 
  Set sh1 = Sheets("FSR Level A")
  Set sh2 = Sheets("BI")
  Application.ScreenUpdating = False
  For Each c In sh2.Range("A18", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      sh1.Copy After:=Sheets(Sheets.Count)
      Sheets(Sheets.Count).Name = c.Value
  Next c
  Application.ScreenUpdating = True
End Sub
Its not renaming the sheets as per range how it was doing earlier now it is creating FSR Level 1, FSR Level A2 sheets
 
Upvote 0
There was a problem with that code if 'BI' was not the active sheet when the code was run.
Try this. It will still error under any of the circumstances mentioned in post #3 & #4

VBA Code:
Sub Create_Central_v3()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range
  
  Set sh1 = Sheets("FSR Level A")
  Set sh2 = Sheets("BI")
  Application.ScreenUpdating = False
  For Each c In sh2.Range("A18", sh2.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      sh1.Copy After:=Sheets(Sheets.Count)
      Sheets(Sheets.Count).Name = c.Value
  Next c
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
There was a problem with that code if 'BI' was not the active sheet when the code was run.
Try this. It will still error under any of the circumstances mentioned in post #3 & #4

VBA Code:
Sub Create_Central_v3()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range
 
  Set sh1 = Sheets("FSR Level A")
  Set sh2 = Sheets("BI")
  Application.ScreenUpdating = False
  For Each c In sh2.Range("A18", sh2.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      sh1.Copy After:=Sheets(Sheets.Count)
      Sheets(Sheets.Count).Name = c.Value
  Next c
  Application.ScreenUpdating = True
End Sub
Thank you it doesn't give erros now but the renaming issue still is not happening like the orignal code. Its not renaming the sheets as per range(for e.g. A18:A21 has three names London, Dublin, Poland and Munich) how it was doing earlier now it is creating FSR Level 1, FSR Level A2 sheets
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top