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]

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

Thank Peter the below code finally worked

Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("FSR Level A")
Set sh2 = Sheets("BI")

For Each c In sh2.Range("319:A2700", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
sh1.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value
Next

 

[Peter_SSs]

Thank Peter the below code finally worked

I'm glad that you have something working, but I don't think it is that code.

The code you posted is not valid code. For example "319:A2700" is not a valid range.
I assume that you typed the code into the forum rather than copy/paste from your vba editor window?

A couple of other points:

• It also looks like you may have changed the range of interest since the original information?
• Even if you fix the range mentioned above, the code would fail if the "BI" sheet was not the active sheet when the code is run.

 

[aashish83]

I'm glad that you have something working, but I don't think it is that code.

The code you posted is not valid code. For example "319:A2700" is not a valid range.
I assume that you typed the code into the forum rather than copy/paste from your vba editor window?

A couple of other points:

• It also looks like you may have changed the range of interest since the original information?
• Even if you fix the range mentioned above, the code would fail if the "BI" sheet was not the active sheet when the code is run.

Yes Peter it is a typo the range is A19:A2700

 

[Peter_SSs]

Yes Peter it is a typo the range is A19:A2700

Always best to actually copy/paste your code to avoid that sort of issue.

BYW, have you tested running the code when sheet 'FSR Level A' is the active sheet?

 

[aashish83]

Yes Peter it is a typo the range is A19:A2700

Hi Peter i have another query on the same is it possible to have this code work for Multiple Range for .e.g. my second range would be B19:B2700 and if that is so it should replica of Sheet FSR Level B instead of FSR Level A and third Range would be C19:C2700 and output to be FSR Level C sheet.

Also if instead of Creating this in the same workbook can i have open in new a workbook

 

[aashish83]

Always best to actually copy/paste your code to avoid that sort of issue.

BYW, have you tested running the code when sheet 'FSR Level A' is the active sheet?

No coz i have assigned a button on the BI sheet to run the Macro hence it will always be run from the BI sheeet

 

[aashish83]

Hi Peter, Request you to please look at the below code as i seem to be getting a error on the BlueText at the end of the code

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]

When posting code in the forum, please use the available code tags. My signature block below has more details. You can still format certain text as you want if you use the "RICH' tags - as you can see from the edit I made to your post above.

You say that you get an error at the blue line, but not what the actual error was?
As well as reporting the actual error, when you Debug, hover over c.Value in the code and tell us what value appears in the pop-up box.

There are some other issues in the code but I will address those together with the above points if they can be clarified.

 

[aashish83]

When posting code in the forum, please use the available code tags. My signature block below has more details. You can still format certain text as you want if you use the "RICH' tags - as you can see from the edit I made to your post above.

You say that you get an error at the blue line, but not what the actual error was?
As well as reporting the actual error, when you Debug, hover over c.Value in the code and tell us what value appears in the pop-up box.

There are some other issues in the code but I will address those together with the above points if they can be clarified.

After some working around i noticed that my part of c column was merged and it was not giving the desired results and showing the error. The c. Value was showing as empty

After unmerging the part of c column it worked. Curios to know what other issues are there with the code?

 

[Peter_SSs]

Curios to know what other issues are there with the code?

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.