PDA

View Full Version : [SOLVED] MACRO - Create worksheet adding an apostrophe



PaulaGon
05-26-2017, 05:48 AM
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!!!

mdmackillop
05-26-2017, 06:13 AM
Sub Test()
Call Macro1
Call Macro2
End Sub

PaulaGon
05-26-2017, 07:10 AM
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!

mdmackillop
05-26-2017, 07:42 AM
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

PaulaGon
05-26-2017, 08:43 AM
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!

mdmackillop
05-26-2017, 09:09 AM
Can you post your workbook?

mdmackillop
05-26-2017, 09:20 AM
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

PaulaGon
05-26-2017, 10:11 AM
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,

mdmackillop
05-26-2017, 10:18 AM
Try

For Each c In Rng.SpecialCells(xlCellTypeConstants)

PaulaGon
05-26-2017, 10:27 AM
This is perfect! It works great!

Thank you very much for the help!