Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance needed with Macro Modification
Some of the workbooks that I use have anywhere between 5-15 spreadsheets, but
when its open only a few of those will actually be used or have any changes made in them. So... Is there a way to adjust the macro below so that it will only run in the spreadsheets that had changes made while the workbook was open? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim c As Range For Each wS In Worksheets For Each c In wS.UsedRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c Next wS ThisWorkbook.Save End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance needed with Macro Modification
AFAIK changes to any sheet is only known at workbook level.
I have not tried this but use the worksheet change event to set a boolean or a cell on the sheet to indicate change On each worksheet code add this...., (I used cell A1 but it needs to be somewhere out of user eyesight or all sheet changes stored on another sheet!) Private Sub Worksheet_Change(ByVal Target As Range) Me.Cells(1, 1) = True End Sub Then use your code modified as follows.... Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim c As Range For Each wS In Worksheets If wS.Cells(1, 1) Then wS.Cells(1, 1) = False For Each c In wS.UsedRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c End If Next wS ThisWorkbook.save End Sub -- Regards, Nigel "akemeny" wrote in message ... Some of the workbooks that I use have anywhere between 5-15 spreadsheets, but when its open only a few of those will actually be used or have any changes made in them. So... Is there a way to adjust the macro below so that it will only run in the spreadsheets that had changes made while the workbook was open? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim c As Range For Each wS In Worksheets For Each c In wS.UsedRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c Next wS ThisWorkbook.Save End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance needed with Macro Modification
Hi,
You could set a marker cell to indicate whether a sheet had changed and only run your code on that sheet if the marker cell is populated. You would need to clear the marker cell when the workbook is opened. When choosing a marker cell bear in mind that if you chose (say) Iv65536 then that increases the used range and the whole thing could be counter productive in saving time which is what I presume you are attempting to do. Private Sub Workbook_Open() For x = 1 To Worksheets.Count Application.EnableEvents = False Sheets(x).Range("D1").ClearContents Application.EnableEvents = True Next End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Application.EnableEvents = False ActiveSheet.Range("D1").Value = "changed" Application.EnableEvents = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim c As Range For Each wS In Worksheets If wS.Range("D1").Value = "changed" Then For Each c In wS.UsedRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c End If Next wS ThisWorkbook.Save End Sub Mike "akemeny" wrote: Some of the workbooks that I use have anywhere between 5-15 spreadsheets, but when its open only a few of those will actually be used or have any changes made in them. So... Is there a way to adjust the macro below so that it will only run in the spreadsheets that had changes made while the workbook was open? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim c As Range For Each wS In Worksheets For Each c In wS.UsedRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c Next wS ThisWorkbook.Save End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance needed with Macro Modification
Ok... so I use (for instance) cell c1 to set the boolean then have the code
set for cells(1, 3)... correct? I've never set a boolean in this type of setting, how would I set it to track if there were any changes made to the spreadsheet? "Nigel" wrote: AFAIK changes to any sheet is only known at workbook level. I have not tried this but use the worksheet change event to set a boolean or a cell on the sheet to indicate change On each worksheet code add this...., (I used cell A1 but it needs to be somewhere out of user eyesight or all sheet changes stored on another sheet!) Private Sub Worksheet_Change(ByVal Target As Range) Me.Cells(1, 1) = True End Sub Then use your code modified as follows.... Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim c As Range For Each wS In Worksheets If wS.Cells(1, 1) Then wS.Cells(1, 1) = False For Each c In wS.UsedRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c End If Next wS ThisWorkbook.save End Sub -- Regards, Nigel "akemeny" wrote in message ... Some of the workbooks that I use have anywhere between 5-15 spreadsheets, but when its open only a few of those will actually be used or have any changes made in them. So... Is there a way to adjust the macro below so that it will only run in the spreadsheets that had changes made while the workbook was open? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim c As Range For Each wS In Worksheets For Each c In wS.UsedRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c Next wS ThisWorkbook.Save End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance needed with Macro Modification
This works great, but it still takes a bit of time when closing for it to go
through every cell. Is there a way that I can narrow down the UsedRange portion to specific columns in the UsedRange. For Example: I really only need it to check columns X:BL in the UsedRange "Mike H" wrote: Hi, You could set a marker cell to indicate whether a sheet had changed and only run your code on that sheet if the marker cell is populated. You would need to clear the marker cell when the workbook is opened. When choosing a marker cell bear in mind that if you chose (say) Iv65536 then that increases the used range and the whole thing could be counter productive in saving time which is what I presume you are attempting to do. Private Sub Workbook_Open() For x = 1 To Worksheets.Count Application.EnableEvents = False Sheets(x).Range("D1").ClearContents Application.EnableEvents = True Next End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Application.EnableEvents = False ActiveSheet.Range("D1").Value = "changed" Application.EnableEvents = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim c As Range For Each wS In Worksheets If wS.Range("D1").Value = "changed" Then For Each c In wS.UsedRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c End If Next wS ThisWorkbook.Save End Sub Mike "akemeny" wrote: Some of the workbooks that I use have anywhere between 5-15 spreadsheets, but when its open only a few of those will actually be used or have any changes made in them. So... Is there a way to adjust the macro below so that it will only run in the spreadsheets that had changes made while the workbook was open? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim c As Range For Each wS In Worksheets For Each c In wS.UsedRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c Next wS ThisWorkbook.Save End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance needed with Macro Modification
Typo
try this instead Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim MyRange As Range Dim c As Range For Each wS In Worksheets If wS.Range("D1").Value = "changed" Then lastrow = Cells(Cells.Rows.Count, "X").End(xlUp).Row lastrow1 = Cells(Cells.Rows.Count, "BL").End(xlUp).Row Set MyRange = Range("X1:BL" & WorksheetFunction.Max(lastrow, lastrow1)) For Each c In MyRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c End If Next wS ThisWorkbook.Save End Sub Mike "akemeny" wrote: This works great, but it still takes a bit of time when closing for it to go through every cell. Is there a way that I can narrow down the UsedRange portion to specific columns in the UsedRange. For Example: I really only need it to check columns X:BL in the UsedRange "Mike H" wrote: Hi, You could set a marker cell to indicate whether a sheet had changed and only run your code on that sheet if the marker cell is populated. You would need to clear the marker cell when the workbook is opened. When choosing a marker cell bear in mind that if you chose (say) Iv65536 then that increases the used range and the whole thing could be counter productive in saving time which is what I presume you are attempting to do. Private Sub Workbook_Open() For x = 1 To Worksheets.Count Application.EnableEvents = False Sheets(x).Range("D1").ClearContents Application.EnableEvents = True Next End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Application.EnableEvents = False ActiveSheet.Range("D1").Value = "changed" Application.EnableEvents = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim c As Range For Each wS In Worksheets If wS.Range("D1").Value = "changed" Then For Each c In wS.UsedRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c End If Next wS ThisWorkbook.Save End Sub Mike "akemeny" wrote: Some of the workbooks that I use have anywhere between 5-15 spreadsheets, but when its open only a few of those will actually be used or have any changes made in them. So... Is there a way to adjust the macro below so that it will only run in the spreadsheets that had changes made while the workbook was open? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim c As Range For Each wS In Worksheets For Each c In wS.UsedRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c Next wS ThisWorkbook.Save End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assistance needed with Macro Modification
One last thing that I just thought might help to lower the amount of time...
I have 4 spreadsheets in each workbook (all named the same in each one) that I don't need checked. How could I get the macro below to exclude those 4 spreadsheets? Summary, RAC, FI, QIC Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim MyRange As Range Dim c As Range For Each wS In Worksheets If wS.Range("C1").Value = "changed" Then lastrow = Cells(Cells.Rows.Count, "X").End(xlUp).Row lastrow2 = Cells(Cells.Rows.Count, "Z").End(xlUp).Row lastrow3 = Cells(Cells.Rows.Count, "AA").End(xlUp).Row lastrow4 = Cells(Cells.Rows.Count, "AH").End(xlUp).Row lastrow5 = Cells(Cells.Rows.Count, "AI").End(xlUp).Row lastrow6 = Cells(Cells.Rows.Count, "AO").End(xlUp).Row lastrow8 = Cells(Cells.Rows.Count, "AQ").End(xlUp).Row lastrow9 = Cells(Cells.Rows.Count, "AY").End(xlUp).Row lastrow10 = Cells(Cells.Rows.Count, "AZ").End(xlUp).Row lastrow11 = Cells(Cells.Rows.Count, "BF").End(xlUp).Row lastrow12 = Cells(Cells.Rows.Count, "BG").End(xlUp).Row lastrow13 = Cells(Cells.Rows.Count, "BL").End(xlUp).Row Set MyRange = Range("X1:BL" & WorksheetFunction.Max(lastrow, lastrow2, _ lastrow3, lastrow4, lastrow5, lastrow6, lastrow8, lastrow9, _ lastrow10, lastrow11, lastrow12, lastrow13)) For Each c In MyRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c End If Next wS ThisWorkbook.Save End Sub "akemeny" wrote: Some of the workbooks that I use have anywhere between 5-15 spreadsheets, but when its open only a few of those will actually be used or have any changes made in them. So... Is there a way to adjust the macro below so that it will only run in the spreadsheets that had changes made while the workbook was open? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wS As Worksheet Dim c As Range For Each wS In Worksheets For Each c In wS.UsedRange If Not c.HasFormula Then c.Value = Trim(c.Value) End If Next c Next wS ThisWorkbook.Save End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Special Macro Assistance needed | Excel Programming | |||
Ron De Bruin Code modification needed when naming copied worksheet | Excel Programming | |||
Macro Compiles Sheets to One Book...Small Modification Needed | Excel Programming | |||
Macro assistance needed | Excel Programming | |||
Formula Modification Needed | Excel Programming |