debauch
12-03-2006, 07:18 AM
Hello,
I am working on some code that will remove private information from our work files to adhere to a privacy act. I have been trying to streamline the process and need a little help.
There is about 20 files, where I am using a open file dialog , then the code finds certain columns, deletes the private info, takes the first tab only, and saves it to one of our public drives for viewing.
Right now in my example, it only deletes what I need, specified in column H:H , however the same column(s) will appears in different spots for each file.
Here is what I have so far:
Selection.AutoFilter 'take off filter
Cells.Find(What:="SSN", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Columns("H:H").Select
'ActiveSheet.Cells(Rows.Count, 1).End(xlUp).EntireRow.Copy << tried _
this but it didnt work
Selection.Delete Shift:=xlToLeft
Sheets("Users").Select 'copy whole thing
Sheets("Users").Copy ' make new seperate front page
Range("A2").Select
ChDir "L:\Hal Reports"
ActiveWorkbook.SaveAs Filename:="DB 1" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
' Windows("DB 1.xls").Activate
I think something similar to below might work? I dont want it to look to H:H but rather, look to the column named SSN in each book.
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).EntireRow.Copy
Thanks.
I am working on some code that will remove private information from our work files to adhere to a privacy act. I have been trying to streamline the process and need a little help.
There is about 20 files, where I am using a open file dialog , then the code finds certain columns, deletes the private info, takes the first tab only, and saves it to one of our public drives for viewing.
Right now in my example, it only deletes what I need, specified in column H:H , however the same column(s) will appears in different spots for each file.
Here is what I have so far:
Selection.AutoFilter 'take off filter
Cells.Find(What:="SSN", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Columns("H:H").Select
'ActiveSheet.Cells(Rows.Count, 1).End(xlUp).EntireRow.Copy << tried _
this but it didnt work
Selection.Delete Shift:=xlToLeft
Sheets("Users").Select 'copy whole thing
Sheets("Users").Copy ' make new seperate front page
Range("A2").Select
ChDir "L:\Hal Reports"
ActiveWorkbook.SaveAs Filename:="DB 1" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
' Windows("DB 1.xls").Activate
I think something similar to below might work? I dont want it to look to H:H but rather, look to the column named SSN in each book.
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).EntireRow.Copy
Thanks.