macro to list created tabs on to a worksheet

MATEAPOT

New Member
Joined
Jul 5, 2008
Messages
14
I have adapted this code to create tabs in my workbook.i need to make it display the created tabs in a worksheet on the workbook.is this possible?



With ActiveSheet
lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
For lngLoopCtr = 1 To lngLastRow Step 1
strNewSheetname = Sheets(ws.Name).Cells(lngLoopCtr, "A")
On Error Resume Next
Sheets(strNewSheetname).Select
If Err Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = strNewSheetname
Worksheets(ws.Name).UsedRange.Copy
ActiveSheet.Range("a2").PasteSpecial

End If
Sheets("ws.Name").Cells(lngLoopCtr, "AH:H8").Copy Sheets(strNewSheetname).Cells(1, "AH:H8")
On Error GoTo 0
Next lngLoopCtr
End With
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I think if you add a worksheet to your workbook called "List", then the following should work:

Code:
Worksheets("List").Select
Range("A" & Rows.count).End(xlDown).row.Value = strNewSheetname
 

MATEAPOT

New Member
Joined
Jul 5, 2008
Messages
14
Thanks for the quick reply.after running this macro several times i realised that there is a possibilty that it can replace an already existing sheet in the workbook.How can i prevent this?.I need a macro that will ,using a value in sheet1 cell A1 creates 3 unique tabs ,copy the range in sheet1 to these sheets,then paste the names of the newly created tabs to a sheet called List.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,325
Members
414,053
Latest member
Dual Showman

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
Top