[X]My Assistant

[aggiemarine07]

So I have a working macro (code below) that successfully copies a worksheet from another workbook, adjust the formulas, and breaks the links from the previous workbook (all while accounting for if the workbook is protected or not). However, I am now trying to change the values in the newly copied sheet from formulas to value's and am having difficulty implementing the new code.

I searched the internet and found some simple code that will do the job but its not working (i'll highlight below where I feel it should be implemented) as I keep getting an "end with without with statement" error but it has both the with and the end with; can a With Statement not be within another With statement? Is that bad coding?

End goal: After the new worksheet has been copied and formulas have been adjusted to the new sheet, to then change those formulas to values.

What am I missing? What noob mistakes did I make?

--------------------

Code I want to implement that will change all formulas to values:

CODE

with range("A1").currentregion
    .value = .value
end with

Working Code:

CODE

Sub Command0_Click()
Dim xl As Excel.Application
Dim wbMaster As Excel.Workbook
Set xl = New Excel.Application
Set wbMaster = xl.Workbooks.Open(CurrentProject.path & "\ProjectBabelfish")

Dim wsCopy As Excel.Worksheet
Set wsCopy = wbMaster.Worksheets("Babelfish")

Dim sFolderPath As String
sFolderPath = wbMaster.path & "\PLOGs\"
If Right(sFolderPath, 1) <> "\" Then sFolderPath = sFolderPath & "\"

Dim sFileName As String
sFileName = Dir(sFolderPath & "*.xlsx")

'Disable screenupdating (to prevent "screen flickering" so macro runs smoother)
'Disable alerts (to suppress "Are you sure?" prompts during worksheet deletion)
xl.ScreenUpdating = False
xl.DisplayAlerts = False

'Begin loop through files in the folder
Do While Len(sFileName) > 0

    Dim sWBOpenPassword As String
    Dim sWBProtectPassword As String
    Select Case sFileName
        'Specify workbook names that require passwords here
        Case "book1.xlsx"
            sWBOpenPassword = "550"
            sWBProtectPassword = "550"

        'If different books require different passwords, can specify additional names with their unique passwords
        Case "Book3.xlsx"
            sWBOpenPassword = "book3openpassword"
            sWBProtectPassword = "book3protectionpassword"

        'Keep specifying excel file names and their passwords until completed
        Case "Book10.xlsx", "Book257.xlsx"
            sWBOpenPassword = "GenericOpenPW2"
            sWBProtectPassword = "GenericProtectPW2"

        'Case Else will handle the remaining workbooks that don't require passwords
        Case Else
            'MsgBox sFileName & " has a wierd password"
            sWBOpenPassword = "550"
            sWBProtectPassword = "550"

    End Select

    'Open file using password (if any)
    On Error Resume Next
    With xl.Workbooks.Open(sFolderPath & sFileName, UpdateLinks:=False, Password:=sWBOpenPassword)
        If Err.Number = 1004 Then MsgBox "The workbook " & sFileName & " is at a middle school dance."
        Dim bProtectedWB As Boolean
        bProtectedWB = False    'Reset protected wb check to false

        'Check if workbook is protected and if so unprotect it using the specified protection password
        If .ProtectStructure = True Then bProtectedWB = True
        If bProtectedWB = True Then .Unprotect sWBProtectPassword
            .Sheets(1).Name = "PLOG"
            
        On Error Resume Next    'Suppress error if copied worksheet does not yet exist
        .Worksheets(wsCopy.Name).Delete 'Delete existing sheet if it exists
        On Error GoTo 0         'Remove "On Error Resume Next" condition
    
            wsCopy.Copy After:=.Worksheets(.Worksheets.Count)   'Copy template into the workbook
            .Worksheets(wsCopy.Name).Cells.Replace wbMaster.Name, .Name 'Change references from master workbook to current workbook

'---->code should be placed here correct? Since I want to change the formulas to values after the sheet has been copied and before it protects the workbook again
with .Worksheets(wsCopy.Name).Range("A1:M10").currentregion
    .value = .value
end with
            
            'If workbook was protected, reprotect it with same protection password
            If bProtectedWB = True Then .Protect sWBProtectPassword
    
            'Close file and save the changes
        
        If Not IsEmpty(varLinks) Then
            For i = 1 To UBound(varLinks)
                .BreakLink _
                    Name:=varLinks(i), _
                    Type:=xlLinkTypeExcelLinks
            Next i
        End If
        
        .Close True

    End With
      
    sFileName = Dir 'Advance to next file in the folder
    
Loop

'Re-enable screenupdating and alerts
xl.ScreenUpdating = True
xl.DisplayAlerts = True
xl.Quit

MsgBox "Added Tab to all PLOG files"

End Sub

[June7]

With certainly can be nested and nothing wrong with doing it. I don't see any missing End statements. I tested the .Value = .Value and that works.

I copy/pasted your entire procedure into a module and it does compile. I did find variables varLinks and i are not declared.

The procedure worked until you added the inner With? Must be something wrong with way the new worksheet is referenced.



This post has been edited by June7: Aug 20 2019, 11:52 PM

[June7]

If you want to provide the Excel file, I will do some more tests.

[aggiemarine07]

@June7 thanks so much for your help, I did manage to get the code to run without error but now its simply not doing what I need it to at this point: change the formulas to their values in the worksheet of "Babelfish" aka wsCopy.Name. When I open the excel file after it finishes, I still see the formulas in the cells instead of their values.

Is there an easier method to accomplish this?

I have attached my DB and the corresponding excel files for testing.
This post has been edited by aggiemarine07: Aug 21 2019, 07:19 AM

Attached File(s)

 Project_Babelfish.zip ( 329.7K )Number of downloads: 7

 

[PaulBrand]

I think you should copy the range then

CODE

with .Worksheets(wsCopy.Name).Range("A1:M10").Copy
    .Worksheets(wsCopy.Name).Range("A1").PasteSpecial Paste:=xlPasteValues
end with

This post has been edited by PaulBrand: Aug 21 2019, 07:51 AM

[June7]

I am surprised code works at all because there is a space in folder name and that space in not in the coded folder name. But apparently the code handles that.

I ran code with the addition of .Value = .Value code and it executes without error. I even deleted worksheet Babelfish first. Worksheet is created and it has no formulas, replaced with values. Which worksheet are you looking at with formulas?

Haven't figured out how workbook ProjectBabelfish.xlsm figures in this process. I see it gets referenced in code but it is neither modified nor its data copied. Perhaps you can clarify.

This post has been edited by June7: Aug 21 2019, 03:04 PM

[aggiemarine07]

@PaulBrand - tried your method and it still did not work

@June7 - the code should go through every excel file within the "PLOG" folder and do this:
1) open first workbook (EX:WORKBOOK1.XLSX) in "PLOG" folder
2) test whether it is protected or not
2a) if protected, then it will unprotect it
3) Copy worksheet "Babelfish" from "Project Babelfish.xlsx" in root folder into the open workbook (EX: WORKBOOK1.XLSX)
4) Update the formulas in the copied worksheet from the master workbook (PROJECT BABELFIS.XLSX) to the open workbook (WORKBOOK1.XLSX)
5) **This is where I want it to copy everything and paste as values (keeping the formatting from the PROJECT BABELFISH.XLSX file)
6) Break the links to the previous workbook (PROJECT BABELFISH.XLSX)
7) Protect the workbook, Save, Close, Quit

The end end goal that I want it do (which is outside of the scope of my original question but since we're here), is to keep all of the formatting that I designed in the PROJECT BABELFISH.XLSX file and have it applied to every excel file in the "PLOG" folder. This is why I want to do the whole paste as values method, unless you can think of a better way to accomplish this (I am open to suggestions).

Let me know if this doesnt make sense; thanks so much for all your help with this!

[June7]

Probably doesn't make difference, but ProjectBabelfish is an xlsm, not xlsx.

I understand the process and the code runs. Worksheet in PLOGs workbook is created with values, no formulas. However, you have not clarified what data the new worksheet should have.

ProjectBabelfish.xlsm shows 9 records. The new worksheet shows only 1 record. If this is correct, then the code works.

The PLOGs workbook is not protected because it wasn't to start with.

This post has been edited by June7: Aug 21 2019, 11:35 PM

[aggiemarine07]

I want the formulas in the new workbook, which the worksheet is being copied to (i.e. WORKBOOK1.XLSX) to have the values; see attached image, this is the result that I do NOT want.

Is there a way I step through the code and have access show me line by line what its doing? I feel like its working but somewhere else.

Attached File(s)

 Capture2.PNG ( 65.84K )Number of downloads: 2

 

[aggiemarine07]

Well I ended up solving it (I think) by using the below code:

CODE

            .Worksheets("Babelfish").Activate
            .Worksheets("Babelfish").Range("A2:M3").Select
            .Worksheets("Babelfish").Range("A2:M3").Copy
            .Worksheets("Babelfish").Range("A2:M3").PasteSpecial Paste:=xlPasteValues

[PaulBrand]

I'm glad you got it sorted :-)

By the way you can add a breakpoint anywhere in your code (F9 will set a breakpoint on the cursor line) and step through F8 or Run F5

[June7]

Glad you found solution. Interesting that your original code works for me.