Sheet 2 is same format as sheet 1 without copy/paste special/formats

Topic

New Reply

Code:

Option Explicit
 
Sub add_Sheet_name()
    Dim sShtName As String
    
     Sheets(“SAVER”).Select
     
    sShtName = Sheet4.Cells(3, 1).Value
     
    If Not WksExists(sShtName) Then Worksheets.Add After:=Worksheets(Worksheets.Count)
     
    ActiveSheet.Name = sShtName
    ActiveSheet.Range(“A1:Z150”).Value = Sheets(“SAVER”).Range(“A1:Z150”).Value
    ActiveSheet.Range(“A1:Z150”).Format = Sheets(“SAVER”).Range(“A1:Z150”).Format
    
End Sub
Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

The above code, ( not mine ) will add a new sheet and name the sheet according to a cell range
in Sheets(SAVER). Usually the date being yyyymmdd-“reference code”
However, the new sheet is blank and the way this code is writen, no matter what sheet name it is, it will look for the sheet’s original number. In this case it’s Sheet4, even if I do re-name it “SAVER”.
In the VBA Project Window it shows “Sheet 4 ( SAVER )” and in Cell A3 is where the new sheet gets it’s
reference to name it.
No problems there, I have worked around it, some extra coding but it will do for now.

However, I need to fill the new blank re-named worksheet with the new data, and then it’s finally saved.
The workbook is in fact “storage” for later use if need be. ( it’s not important right now )

What’s important is to be able to read it if need be.
The method I use is with the following code to transfer the data to the new worksheet, but the new worksheet has no format.
So I figured, logic would assume if

Code:

ActiveSheet.Range(“A1:Z150”).Value = Sheets(“SAVER”).Range(“A1:Z150”).Value

Does work, why then does not the following code work to not only equal the Value between
A1:Z150, but also the Format ?

Code:

ActiveSheet.Range(“A1:Z150”).Format = Sheets(“SAVER”).Range(“A1:Z150”).Format

How can I transfer the format from sheet SAVER to the newly named worksheet ?

Thanks in advance

XP

Reply | Quote

Replies

XP,

Instead of adding a new worksheet, copy the existing sheet then just select the entire new sheet and use selection.clearcontents. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks for the reply,

RG.

The macro cannot Copy and Paste the format because it does not “know” the name of the worksheet
with it’s new name, to Select that Sheet.

Reply | Quote

XP,

This worked as I think you wanted?

Code:

Option Explicit

Sub add_Sheet_name()

    Dim sShtName As String
    
     Sheets("SAVER").Select
     
    sShtName = Sheet2.Cells(3, 1).Value  '** Changed to Sheet 2 for test ***
     
    If Not WksExists(sShtName) Then Sheets("SAVER").Copy After:=Sheets(Sheets.Count)
     
    ActiveSheet.Name = sShtName
    Cells.Select
    Selection.ClearContents
    [A1].Select
    ActiveSheet.Range("A1:Z150").Value = Sheets("SAVER").Range("A1:Z150").Value
    
End Sub  'add_Sheet_name

My Test Workbook: 33667-XPDiHards-New-Sheet
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

The macro cannot Copy and Paste the format because it does not “know” the name of the worksheet
with it’s new name, to Select that Sheet.

XP,
Another way is to select the sheet by its position. If you create the sheet and put it in front you can always reference it as Sheet(1). Then you can give it a name or do what you want with it.

Code:

ActiveSheet.Copy Before:=Sheets(1)
Sheets(1).Select
Cells.Select
Selection.ClearContents

Reply | Quote

RG
Thanks again,

It worked perfectly for over 25 test runs.
I was tweaking things as I tested, then all of sudden it stopped working.
There were no code changes or sheet(4) changes whatsoever.
I copied your code again and started from scratch in the test workbooks, same again for no obvious reason.

I have uploaded the 2 workbooks to show how this is suppose to work.
It’s referenced in C: ( folder name ) so for your own tests it may have to be changed.

Simply open workbook named: SOURCE-TEST.XLS and Press the macro button.
Everything else is automatic, supposedly. ( both workbooks have to be in the same folder/location.

It errors precisely at:

Code:

If Not WksExists(sShtName)

Compile Error, Sub or Function not Defined.

I regard this as solved, because it’s working in the main Worbooks.
But stopped working in the test workbooks.

Thanks Maudibe, I’ll test your suggestions to.

XP

Reply | Quote

XP,

The error is due to the fact that the function WksExists does NOT exist in either of the files! You had it in your original post but when I sent back my code I didn’t include it since I didn’t change it. You must have copied my code and then completely replaced yours instead of just the one Sub. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote