Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-28-2022, 04:56 AM
pantakos pantakos is offline Create / Rename Worksheet based on other worksheets Windows 10 Create / Rename Worksheet based on other worksheets Office 2013
Novice
Create / Rename Worksheet based on other worksheets
 
Join Date: Feb 2018
Posts: 3
pantakos is on a distinguished road
Default Create / Rename Worksheet based on other worksheets

Hello all,



What I need to do is to rename the created sheet (Products) with the name of the sheets in ws , that are used for the calculations (fill Products worksheet with data).
The format will be TEMPLATE-games-play2 etc , only the sheets with data
Please find attached an example

Thank you

Cross posted Oops! We ran into some problems. | MrExcel Message Board and Rename Worksheet based on worksheets - Excel VBA / Macros - OzGrid Free Excel/VBA Help Forum
Attached Files
File Type: xlsm test6.xlsm (119.0 KB, 5 views)
Reply With Quote
  #2  
Old 04-28-2022, 06:02 AM
p45cal's Avatar
p45cal p45cal is offline Create / Rename Worksheet based on other worksheets Windows 10 Create / Rename Worksheet based on other worksheets Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Your mrexcel link is broken, it's:
Rename Worksheet based on calculated worksheets | MrExcel Message Board



try:
Code:
Sub BuildTemplateINm()
Application.ScreenUpdating = False
Dim Ws As Worksheet, desWS As Worksheet, rng As Range, lRow As Long, NweShtName As String, ALOIFOTS As Boolean

'unconditionally delete all sheets whose name begins 'TEMPLATE':
Application.DisplayAlerts = False
For Each Ws In Worksheets
  If Left(Ws.Name, 8) = "TEMPLATE" Then Ws.Delete
Next Ws
Application.DisplayAlerts = True

'Always add a new TEMPLATE sheet:
Set desWS = Sheets.Add(after:=Sheets(Sheets.Count))
With desWS
  .Name = "TEMPLATE"
  .Range("A1").Resize(, 2).Value = Array("Description", "Quantity")
End With

NweShtName = "TEMPLATE"    'the future name of this worksheet which will be built upon.
For Each Ws In Sheets
  ALOIFOTS = False    'At Least One Item Found On This Sheet
  If Left(Ws.Name, 8) <> "TEMPLATE" Then
    With Ws
      If .Range("H" & .Rows.Count).End(xlUp).Row > 12 Then
        For Each rng In .Range("H14", .Range("H" & .Rows.Count).End(xlUp))
          If (IsNumeric(rng.Value)) And (rng.Value <> 0) Then
            ALOIFOTS = True
            With desWS
              .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2).Value = Array(rng.Offset(, -6), rng)
            End With
          End If
        Next rng
      End If
      If .Range("O" & .Rows.Count).End(xlUp).Row > 12 Then
        For Each rng In .Range("O14", .Range("O" & .Rows.Count).End(xlUp))
          If (IsNumeric(rng.Value)) And (rng.Value <> 0) Then
            ALOIFOTS = True
            With desWS
              .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2).Value = Array(rng.Offset(, -6), rng)
            End With
          End If
        Next rng
      End If
    End With
  End If
  If ALOIFOTS Then NweShtName = NweShtName & "-" & Ws.Name
Next Ws
With desWS
  .Columns.AutoFit
  .Name = NweShtName
End With
Application.ScreenUpdating = True
End Sub
Reply With Quote
  #3  
Old 04-28-2022, 06:17 AM
pantakos pantakos is offline Create / Rename Worksheet based on other worksheets Windows 10 Create / Rename Worksheet based on other worksheets Office 2013
Novice
Create / Rename Worksheet based on other worksheets
 
Join Date: Feb 2018
Posts: 3
pantakos is on a distinguished road
Default

@p45cal

Thank you for your reply!
Thsi is working great!

Thank you!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
combining worksheets of same xls into a single worksheet pavan.polish Excel 2 03-14-2018 06:48 PM
Create / Rename Worksheet based on other worksheets How to copy workbooks from different worksheets into one new worksheet abbani Excel 3 12-12-2016 04:09 AM
Create / Rename Worksheet based on other worksheets Combining different worksheets into a single worksheet. jimmy2016 Excel Programming 4 10-15-2016 09:05 AM
Create / Rename Worksheet based on other worksheets copy cells from a worksheet into other worksheets based on Criteria Elton Wolter Excel Programming 4 04-16-2016 08:44 AM
Variable to rename a tab in a worksheet via VBA Chayes Excel Programming 5 08-07-2012 02:03 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft