logo
down
shadow

Copy worksheet to new workbook and change the name of the worksheet to a compose name


Copy worksheet to new workbook and change the name of the worksheet to a compose name

By : Ellen
Date : October 17 2020, 03:08 PM
help you fix your problem I'm copying a lot of workbooks to 1 workbook, but since all the worksheets on the different workbook have the same name, and each workbook have the name of the file on B1, so I want to name the new sheet as "violation(name)" , You cannot "SET" as string variable:
code :
Set SheetNewName = SheetName & "(" & Parname & ")"
SheetNewName = SheetName & "(" & Parname & ")"
Set Parname = wc.Range("B1")
Parname = wc.Range("B1")


Share : facebook icon twitter icon
Worksheet.copy() fails for _only_ one worksheet in a workbook

Worksheet.copy() fails for _only_ one worksheet in a workbook


By : user3422816
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , It turns out the the PivotCharts were broken.
When I attempted to edit the data source for the PivotCharts, this is what Excel showed me:
Copy a template worksheet multiple times in a new workbook with different worksheet names

Copy a template worksheet multiple times in a new workbook with different worksheet names


By : Phuong Tran-Gia
Date : March 29 2020, 07:55 AM
I hope this helps . I think we cannot add a workbook with a specified name as it is not yet saved. So just add workbook do all the operations and in the end save it with the desired name.
code :
Sub AddWorksheet()
Application.DefaultSaveFormat = xlOpenXMLWorkbook
Dim plage As Range
Dim i As Integer
Dim OldBook As Workbook, NewBook As Workbook 'declare both workbooks
Set OldBook = ActiveWorkbook
spath = ThisWorkbook.Path
Set plage = OldBook.Sheets("Sheet Names").Range("E6:E24") 'Assuming that sheet names are in range E6:E24 in "Sheet Names" sheet in old workbook
Set NewBook = Workbooks.Add 'adding new workbook so as to copy the template sheet but this workbook is not saved yet
For i = 1 To plage.Height
    If plage.Cells(i).Value <> "" Then 'for each non blank cell in range
        OldBook.Sheets("FeuilleTemplate").Copy After:=NewBook.Sheets(NewBook.Sheets.Count) 'Copy "FeuilleTemplate" sheet in workbook after last sheet
        NewBook.Sheets("FeuilleTemplate").Name = plage.Cells(i).Value 'Rename the sheet to the desired names from range E6:E24 in "Sheet Names" sheet in old workbook
    End If
Next i
With NewBook
    .SaveAs Filename:=spath & "\" & "New Workbook with Templates"
    .Close SaveChanges:=True
     End With
End Sub
Copy worksheet to another workbook and close the source worksheet

Copy worksheet to another workbook and close the source worksheet


By : Dmitriy
Date : March 29 2020, 07:55 AM
help you fix your problem In the Code Below i an Example how you can close the Workbook. Use a variable for the Workbook you are going to Open. After that you can close it without Saving it.
code :
Dim wbLatestFile As Workbook

'Open the latest file and copy Pivot Data Tab
Set wbLatestFile = Workbooks.Open MyPath & LatestFile
wbLatestFile .Worksheets("Pivot Data").Copy After:=CopiedData

wbLatestFile.Close SaveChanges:=False
VBA: If Worksheet name in Workbook equals Combo Box value selected from Userform then copy that Worksheet and paste it i

VBA: If Worksheet name in Workbook equals Combo Box value selected from Userform then copy that Worksheet and paste it i


By : user2324731
Date : March 29 2020, 07:55 AM
Hope that helps The root of your error is improper refenceing of the workbook. There are a lot of other issues, too.
Unnecassary reference to ThisWorkbook Unnecassary loop through all worksheets Unnecassary renaming of copied sheet Unnecassry / incorrect references to the ActiveWorkbook and ActiveSheet No Error Handling Improper indenting
code :
Option Explicit

Const ArchiveFilePath As String = "C:\Path\To\ArchiveBook.xlsx"

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim WorkbookA As Workbook
    Dim WorkbookB As Workbook
    Dim wsName As String

    Application.ScreenUpdating = False

    Set WorkbookA = ActiveWorkbook

    wsName = UserForm1.ComboBox1.Text
    If wsName = vbNullString Then Exit Sub

    On Error Resume Next 'Handle possibility that Open fails
    Set WorkbookB = Workbooks.Open(ArchiveFilePath)
    On Error GoTo 0
    If WorkbookB Is Nothing Then
        MsgBox "Failed to open " & ArchiveFilePath, vbOKOnly, "Error"
        Exit Sub
    End If

    'Check if specified ws already exists in WorkbookB
    Set ws = GetWorksheet(WorkbookB, wsName)
    If Not ws Is Nothing Then
        ' Sheet already exists.  What now?
        MsgBox "Sheet " & wsName & " already exists in " & WorkbookB.Name & ".  What now?", vbOKOnly, "Error"
        WorkbookB.Close
        Exit Sub
    End If

    Set ws = GetWorksheet(WorkbookA, wsName)
    If ws Is Nothing Then
        MsgBox "Sheet " & wsName & " does not exist in " & WorkbookA.Name, vbOKOnly, "Error"
        WorkbookB.Close
        Exit Sub
    End If

    ws.Copy After:=WorkbookB.Sheets(WorkbookB.Sheets.Count)

    WorkbookB.Save
    WorkbookB.Close

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Private Function GetWorksheet(wb As Workbook, wsName As String) As Worksheet
    On Error GoTo EH
    Set GetWorksheet = wb.Worksheets(wsName)
EH:
End Function
Copy worksheet from another workbook where the worksheet name can change depending on input

Copy worksheet from another workbook where the worksheet name can change depending on input


By : user3594745
Date : March 29 2020, 07:55 AM
Any of those help If you specify the name in cell A1 of a worksheet called "Sheet1" (just change the name to the worksheet where you're working)
Use this code:
code :
Dim sourceSheetName as String

sourceSheetName = Thisworkbook.Worksheets("Sheet1").Range("A1").Value

Workbooks.Open "C:\Macro Test\2019-2020 Reflecto Record.xlsm"

Workbooks("2019-2020 Reflecto Record.xlsm").Worksheets(sourceSheetName).Range("A1:BA10000").Copy _
        Workbooks("Macro Test Blank Sheet For new Data.xlsm").Worksheets("Data").Range("A1")

ActiveWorkbook.Close
Related Posts Related Posts :
  • VBA Script fails when trying to save as .XLSX
  • Excel - hide subs from being displayed in the 'Customize Ribbon' dialog
  • excel formula with sum offset and match doesn't work
  • To many if statements to handle
  • how do i get count of specific column used range
  • INDEX MATCH result is always the same
  • Data Extraction over multiple worksheets
  • Running multiple subs one at a time
  • Copy a variably named worksheet from one workbook to another
  • Editing date to DD-mmm-YYYY format
  • Add x number of formated cells based on cell value
  • Excel - Looking up for next available data
  • Cucumber feature file in an excel spreadsheet
  • How to extract the characters from a string in Excel
  • Excel - splitting multiline cells into columns but keep relevant data grouped on same column
  • More efficient method in order to replicate function VBA/Excel
  • How to select multiple cells in VBA script
  • Excel VBA "Like" not Selecting for "*M*"
  • Find and replace the whole string when a partial match is achieved multiple sheets
  • Removing Rows - Code Too Slow
  • Lookup a date, based on a variable, and then add a number of days to that date, to give a new date
  • Error while creating Pivot Table automatically
  • Find sheet name of a worksheet using relative reference
  • Excel Formula to work out incremental figure defined by various monthly results
  • Best way to check for duplicates in VBA
  • Error 1004: Unable to Get CountIf Property
  • Selecting the correct starting point for an offset sum
  • Deleting rows when two blank rows are concurrent
  • VBA: Renaming the active sheet
  • How Hide columns according to a cell value
  • VBA: Creating a Pivot Table
  • Dynamic Referencing in VBA Formula
  • Sort Dictionary Keys using ArrayList
  • Excel cell color formatting
  • Excel: Copying rows to table in another sheet using VBA
  • use macro enabled Excel file in OneDrive
  • Faster VBA to hide rows
  • Two Pivot Tables in the same excel sheet
  • For loop setting Font and Interior of Range taking way too long
  • shadow
    Privacy Policy - Terms - Contact Us © soohba.com