Replies

Perfect!
Thanks

This works great. Thank you!

Is there any way to make this adaptable for the ending column? The last column is not always E, but could be different for the rows. The sample I sent had most of the coulmns trimmed out to remove sensitive data.

tia,
Greg

Thanks Rory! That was a great help

Greg

Hans,
Sorry for the months long delay, but this project got pushed back.
I have the following code established:

Code:

Sub Merit_Sheet_Setup()
'
' Merit_Sheet_Setup Macro
' this macro formats the merit sheets to be pushed out to the program areas.
  Dim wbk As Workbook
  Dim wsh As Worksheet
  Set wbk = ActiveWorkbook
  
  For Each wsh In wbk.Worksheets
' format individual sheets
    wsh.Cells.Select
    wsh.Cells.EntireColumn.AutoFit
    wsh.Range("A5").Select
    Selection.EntireColumn.Insert
    wsh.Columns("A:A").Select
    Selection.ColumnWidth = 1
    wsh.Columns("B:F").Select
    Selection.EntireColumn.Hidden = True
    wsh.Columns("J:L").Select
    Selection.EntireColumn.Hidden = True
    wsh.Range("G2").Select
    ActiveWindow.FreezePanes = True

' lock the sheets leaving the Score & Delete columns editable and copy each sheet to a new workbook
    
    wsh.Columns("O:P").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    wsh.Range("G2").Select
    wsh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"
    wsh.EnableSelection = xlUnlockedCells
    wsh.Copy
    ActiveWorkbook.Close SaveChanges:=True, Filename:=wsh.Name & ".xlsx"
  Next wsh
End Sub

But I am finding that it is not walking through the sheets in the workbook. It goes through the first sheet just fine but after saving the first sheet, it is not moving to the second sheet. Any ideas?

Thanks is advance,
Greg

Perfect Hans!
Thank You
Greg

Hans,
I am trying to adapt this custom function to a project I am working on. I need to determine the last column used within columns X:AZ for each row of a spreadsheet and then return the value of row 1 for that column into a cell. It seems as if this function is looking at the entire worksheet even if I enter a range in the formula. Any suggestions? Would another formula work better?

I have attached a sample workbook. The column I am looking to populate is W.
Thanks!
Greg

Is there a way to modify this code to copy EACH sheet in a workbook to a separate file, then save the file using the sheet name as the filename?
Thanks!

John,
I tried using your suggested code, and while I do get a text file for each of my sheets, the txt files are being saved as sheet1.txt & sheet2.txt rather than the name of the sheet. This is an important part of the process. I also noticed that the name of one of the sheets remained as I named it, and the other became sheet2 and the name of the workbook became sheet2.txt.

I have attached my test version of the file that has 2 sheets in it to see if you have any luck figuring out why.
Thanks!

Hans,
I tried copying the sheets over to a new workbook and it worked. Thanks anyway.

With my trials, that code only cleared about 100 rows of the original 3800 I had in the table. I cut the number of rows for my post to the lounge. The error I get is on the line:
m = wshSource.Range(“A” & Row.Count).End(xlUp).Row

Greg

Perfect as usual Hans! I was making it too complicated. Thanks for pointing me to this simpler solution.

Sure thing. I have deleted a lot of data from other coulmns, leaveing the ones that are salient to this need.

Hans,
Thank you. As always you have a quick an elegant solution. I have applied this and have run into one small glitch. There is the possibility that column ‘J’ may have no numeric entries, only text entries. When I ran it on a report that had no numeric entries I received a runtime 1004 error: No cells were found.

Is there a modification I can make to the code that will allow for this situation?

Thank you Michael!

That did exactly what I wanted it to do. When you have the code for recording changes I would also be interested in that.

The workbooks I am using this in contain budget information that is provided by my office to managers of the individual budgets. It helps us in scheduling budget meetings to know when and if they have reviewed the reports or have made entries that we ned to act on.

If I wanted to save it immediately after opening, how would I do that? What might be undesirable about this?

I tried this code but it started the BeforeSave code which then failed because the protection is password protected.

Private Sub Workbook_Open()
With Worksheets(“View”)
.Unprotect (“xxxxxxx”)
.EnableOutlining = True
FormatUsageLog
EnterOpenData
.Protect UserInterfaceOnly:=True, Password:=(“xxxxxxx”)
End With
ActiveWorkbook.Save

End Sub

Thanks in advance,