Consulting

Results 1 to 10 of 10

Thread: MACRO - Create worksheet adding an apostrophe

  1. #1

    MACRO - Create worksheet adding an apostrophe

    Hi, I need some help with a problem that I am trying to solve. I hope someone can help me!

    I found two macros that I need to use one after another : one that creates a new Worksheet only with values (without formulas), and another macro that adds an apostrophe (') at the beginning of each cell, (so I can convert several columns to text).
    Is there any way to create a single macro that creates a new worksheet, without the formulas but with an apostrophe (') at the beginning of each cell?

    MACRO 1 (create new worksheet)
    Sub Test()
        Dim ws As Worksheet
         Application.CopyObjectsWithCells = False
    'do your copy
    ActiveSheet.Copy
    ActiveSheet.Unprotect
        For Each ws In ActiveWorkbook.Worksheets
    Application.CopyObjectsWithCells = True
            ws.UsedRange.Value = ws.UsedRange.Value
        Next ws
    End Sub
    MACRO 2 (add an apostrophe)
    Sub add_apostrophe()
    'will add an ' in front of whats in the used range in column A
    Set rng = Intersect(Range("A:F"), ActiveSheet.UsedRange)
    For Each c In rng
    c.Value = "'" & c
    Next
    End Sub

    Any suggestions would be greatly appreciated!!!

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Sub Test()
    Call Macro1
    Call Macro2
    End Sub
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    Hi,

    I created the macro to call the two macros and it does not work....

    I think the problem is the first macro creates a new worksheet only with values and the second macro do not work in this new worksheet..

    Is posible to make a macro that creates a new worksheet only with values but with an apostrophe at the beginning?

    Thanks in advanced!

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    As a copied sheet creates a single page workbook there is no need to loop through sheets
    Try
    Sub Test()
        Dim ws As Worksheet
        Application.CopyObjectsWithCells = False
         'do your copy
        ActiveSheet.Copy
        Set ws = ActiveWorkbook.Worksheets(1)
        With ws
        .Unprotect
        .Application.CopyObjectsWithCells = True
        .UsedRange.Value = ws.UsedRange.Value
        Set Rng = Intersect(.Range("A:F"), .UsedRange)
        For Each c In Rng
            c.Value = "'" & c
        Next
        End With
    End Sub
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    Hi,

    When I run the macro... i have to stop the Excel because is very slow..I do not understand what´s happenning
    I think is because the sheet that I need to create a new worksheet with values with apostrophe at the beginning, have many formulas that are being calculate with informations of the other tables...
    Is there any way to the macro run faster?

    Thank for your help!

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Can you post your workbook?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  7. #7
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Try this. It copies and effects changes within the original workbook, then moves the amended sheet to a new book
    Sub Test()
        Dim ws As Worksheet
        Dim Nm As String
        Application.CopyObjectsWithCells = False
         'do your copy
        Set ws = ActiveSheet
        With ws
            Nm = .Name
             .Copy after:=Sheets(Sheets.Count)
        End With
        Set ws = ActiveSheet
        With ws
             .Unprotect
            .Application.CopyObjectsWithCells = True
            .UsedRange.Value = ws.UsedRange.Value
            Set Rng = Intersect(.Range("A:F"), .UsedRange)
            For Each c In Rng
                c.Value = "'" & c
            Next
        End With
        ws.Move
        ActiveSheet.Name = Nm
        ActiveWorkbook.Save
    End Sub
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8
    Hi, I tried your last macro and it gave me some errors when is moving the amended sheet...the errors is about the new sheet have the same names in the cells..
    But I made some changes in the llayout of my workbook and I tryed again your first solution and it works great!

    Sub Test() 
        Dim ws As Worksheet 
        Application.CopyObjectsWithCells = False 
         'do your copy
        ActiveSheet.Copy 
        Set ws = ActiveWorkbook.Worksheets(1) 
        With ws 
            .Unprotect 
            .Application.CopyObjectsWithCells = True 
            .UsedRange.Value = ws.UsedRange.Value 
            Set Rng = Intersect(.Range("A:F"), .UsedRange) 
            For Each c In Rng 
                c.Value = "'" & c 
            Next
        End With
    End Sub
    Only one thing, in the original worksheet I have formulas that if there is no information the output is "" , so when it creates the new workbook the apostrophe also appears in this cells..

    Is possible to change the macro in order to only put the apostrophe in the cells that have only values (<>"")

    Thank you very much for your help,

  9. #9
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Try
    For Each c In Rng.SpecialCells(xlCellTypeConstants)
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  10. #10
    This is perfect! It works great!

    Thank you very much for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •