VB Copy without VB Code or Formulas

[Rocky0201]

Hi...

My following code works great Howver, how can I have the smae fumction without copying the VB code or the Formulas:

                        If Not WorksheetExists(NewSheetName, TWSWorkBook) Then
                            'Worksheet does not exist - Add
                            With ThisWorkbook.Sheets(NewSheetName)
                                .Copy after:=Sheets(Sheets.Count)
                            End With
                           Else
                            'Worksheet exists - Delete then Add
                            Application.DisplayAlerts = False
                            Sheets(NewSheetName).Delete
                            Application.DisplayAlerts = True
                            With ThisWorkbook.Sheets(NewSheetName)
                                .Copy after:=Sheets(Sheets.Count)
 
                            End With
                        End If

 

[Rocky0201]

Hi...

I reread my original post and realized that, besides spelling errors, I was not very explicit with my issue.

I currently have VB code that runs through a spreadsheet (Sheet1) of locations and I build spreadsheets (Sheets 2 through Sheet x) in the same workbook with the information I find from Sheet1. I also open/create workbooks and update these workbooks with the same spreadsheets Sheets 2 through Sheet x.

All of this code works as I hoped it would. My problem is that once I create the spreadsheet in any of the workbooks that I open/create, the formula's and VB code in the source spreadsheet is also copied to the new spreadsheet.

Since I will be sending the spreadsheets to technicians, I cannot have the formula's in the new spreadsheets since the links in the formula's will point to files that the techs will not have. At the same time, I would like to disable or remove the macro's from the spreadhseets so that the technician's will not have to be concerned about enabling macros when they open their workbooks.

I tried doing range copies and although the format of the targeted spreadsheet is correct, the row heights never seem to be carried from the source spreadsheet to the target spreadsheet. If I do a copy/paste special, Column widths then values then format, this works fine except the row heights are not correct.

Any help would greatly be appreciated.

 

[shg]

Maybe ...

    Dim sSht As String
    
    sSht = "something or other"
    
    With ThisWorkbook
        On Error Resume Next
        Application.DisplayAlerts = False
        .Worksheets(sSht).Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        
        .Worksheets.Add(After:=.Sheets.Count).Name = sSht
        .Worksheets("Sheet1").Cells.Copy ActiveSheet.Range("A1")
        With .ActiveSheet.UsedRange
            .Value = .Value
        End With
    End With

 

[Rocky0201]

Thanks shg...

I get the runtime error 1004 Method 'Add' of object 'Sheets' failed on statement .Worksheets.Add(After:=.Sheets.Count).Name = NewSheetName

NewSheetName is a String with the new name of the sheet that I am trying to create.

I think that I am not handling which workbook is active at a given time. I will try to change how I am handling the copy process over several Workbooks with keeping better track of which Workbook is opened.

This is what I think my problem is...

Like I stated before in my sencond post. The actual copy routine of a spreadsheet to another workbook works great. What I have to do now is manually open each worksheet in each workbook that i created and select each form that I copied from the source (A1 to Lx x = variable). I then right click copy, right click paste special valuse. The other task I need to do is go into each sheets macro and delete the code that cam from the sorce worksheet.

I have 150 sheets to do on this project and over 300 sheets for my next project. I'm trying to find a way to automate as much as possible.

Thanks shg...

 

[shg]

You are copying a worksheet from ThisWorks to TWSWorkbook, and then need a way to delete the VBA code in that sheet.

My suggestion is to add a virgin sheet to TWSWorkbook, and then copy the formatting and values to the new sheet.

I get the runtime error 1004 Method 'Add' of object 'Sheets'

Fixed below, I think.

    Dim sSht As String
    
    sSht = "something or other"
    
    With [COLOR=red]TWSWorkBook[/COLOR]
        On Error Resume Next
        Application.DisplayAlerts = False
        .Worksheets(sSht).Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        
        .Worksheets.Add(After:=[COLOR=red]Sheets(.Sheets.Count)[/COLOR]).Name = sSht
        [COLOR=red]ThisWorkbook[/COLOR].Worksheets("Sheet1").Cells.Copy ActiveSheet.Range("A1")
        With .ActiveSheet.UsedRange
            .Value = .Value
        End With
    End With

 

[Rocky0201]

Hi shg...

Here is what i've changed and the runtime error i am now receiving.

From my Userform:

Call BuildExternalWB(TWSWorkBook, NewSheetName, ThisWorkbook.Name, NewSheetName)

Where: TWSWorkBook = "workbook name.xls", NewSheetName = "149 City", ThisWorkBook.Name = "Source Workbook.xls"

My BuildExternalWB routine:

Sub BuildExternalWB(TWSWorkBook, TWSWorkSheet, SourceWorkBook, SourceWorkSheet)
    
    With ThisWorkbook.Sheets(SourceWorkSheet)
        .Copy After:=Sheets(Sheets.Count)
    End With
    
    With TWSWorkBook
        
        On Error GoTo 0
        TWSWorkBook.Worksheets(TWSWorkSheet).Cells.Copy ActiveSheet.Range("A1")
        With TWSWorkBook.ActiveSheet.UsedRange
            .Value = .Value
        End With
        
    End With
    
End Sub

The runtime error is on TWSWorkBook.Worksheets(TWSWorkSheet).Cells.Copy ActiveSheet.Range("A1")

Object Required

Any help would be great... Thx shg

 

[shg]

I've lost the bubble, Rocky. In this code,

 With ThisWorkbook.Sheets(SourceWorkSheet)
    .Copy After:=Sheets(Sheets.Count)
End With

you're copying a sheet from thisWorkbook to some unspecified workbook, whatever is the active workbook. That may be what you want, but if so, your code should specify:

ThisWorkbook.Sheets(SourceWorkSheet).Copy _
    After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

But the larger point is, you're still trying to copy a sheet from ThisWorkbook, and then you say to need to remove code from the worksheet, which has not been addressed. That would require trusted access to the VBA project in Excel, which makes some virus scanners very nervous.

Further, Excel has a long-standing bug that results in a run-time error when you try to copy a worksheet a large number (10, 20, 50) of times.

My suggestion is to create a new worksheet in the workbook of interest, and then copy formats and values from the source worksheet. That's what the code I posted does.

 

[Rocky0201]

Thanks shg...

I think you are correct with an earlier statement you made. It probably would be best to create a tmp sheet by performing a copy values and format. I could them use this tmp sheet to do my .copy as I have been doing.

I will see if I can figure out how to do paste specials... Values then formats.

Thanks again for all of your help...