Report

How to create New sheets by column with repeated values

Ask a question Military1 1Posts Wednesday November 8, 2017Registration date November 8, 2017 Last seen - Last answered on Nov 8, 2017 at 04:21 PM by ac3mark
Hi


Have lines with various tables in rooms. Rooms numbers are in column, but sometimes room have 2 or more tables so room numbers repeats. Code stop with 1004 error when reach same room number.need skip same number.

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Summary").Range("A10")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

Helpful
+0
plus moins
OK here is a programming trick: Put msgbox after each variable so the code will display the variable as the system knows it. Then as it loops through each one, you can visually see where it is failing, and either correct the code to work around the erroneous data, or change the data itself, and hope that when the next person enters the data, they don't enter it in the same way. This is called "trapping".

Please understand, we can cut and paste your code, but without the data example, we will not be able to recreate your error!
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!