Consolidating Multiple Protected files into one

[GUMBIE JAY]

Hello

I am able to record macros but am having SERIOUS issues with the below. I am sure it is possible to complete but i am at a loss :banghead:

I am trying to create a macro that will open, unprotect files, copy and paste data into a consolidated file . Once file "A" has copied it needs to be protected then saved into the completed folder in the same directory. The file "B" opens goes through the same steps and so on.

These files will need to be consolidated daily. All files are in the same directory and the file names will be updated daily.

For example a file to be consolidated that was completed by John Smith on Nov 9, 2012 will be saved as (users initials +month+day) JS1109.xls

For every file for Nov 9th will have "1109.xls" at the end.

If someone could help me out with this it would be greatly appreciated.

[GUMBIE JAY]

let me simplify this a little. The main issue i am having is to have vba only use the date portion of the file name and it will find all files with that ending.ie. "1109.xls"

If i can have someone help me with this i will try and work out the rest.

Thanks

[snb]

sub M_snb()
c00="G:\OF\"
c01=dir(c00 & "81109.xls")

do until c01=""
with getobject(c00 & c01)
with .sheets(1).currentregion
thisworkbook.sheets("import").cells(rows.count,1).end(xlup).offset(1).resize(.rows.count,.columns.count )=.value
.close false
end with
end with
c01=dir
loop
end sub

[GUMBIE JAY]

Thank you I appreciate the help. When i put the full macro together i will let you know the outcome

[GUMBIE JAY]

I am still having issues when i put this with my current macro. I have removed all the changes i made with your about suggestion. in my code below I have also changed the file names to "1.xls". In the code below the file name is hard coded. If someone could help out it would be greatly appreciated.


Sub Consolidate_User_Daily_Files()
'
' Consolidate_User_Daily_Files Macro
'

'
ChDir _
"C:\Documents and Settings\jasosmit\My Documents\ACCOUNTS RECEIVABLE\TEST"
Workbooks.Open Filename:= _
"C:\Documents and Settings\jasosmit\My Documents\ACCOUNTS RECEIVABLE\TEST\1.xls"
ActiveSheet.Unprotect
Application.Goto Reference:="CustomerName_Detail"
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows("CONSOLIDATED CREDIT CARD SHEET.XLS").Activate
Application.Goto Reference:="CustName_HdrRow"
ActiveCell.SpecialCells(xlLastCell).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("1.XLS").Activate
Application.Goto Reference:="UserName"
Application.CutCopyMode = False
Selection.Copy
Windows("CONSOLIDATED CREDIT CARD SHEET.XLS").Activate
Application.Goto Reference:="UserName_HdrRow"
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -3).Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("1.XLS").Activate
Application.Goto Reference:="Date"
Selection.Copy
Windows("CONSOLIDATED CREDIT CARD SHEET.XLS").Activate
Application.Goto Reference:="Date_HdrRow"
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.Goto Reference:="CustName_HdrRow"
Windows("1.XLS").Activate
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
ChDir _
"C:\Documents and Settings\jasosmit\My Documents\ACCOUNTS RECEIVABLE\TEST\DONE"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\jasosmit\My Documents\ACCOUNTS RECEIVABLE\TEST\DONE\1.XLS" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Windows("CONSOLIDATED CREDIT CARD SHEET.XLS").Activate

End Sub

[GUMBIE JAY]

.

[snb]

My suggestion is meant to completely replace your macro.
The only thing you have to do is to adapt it to your situation (i.c the path).

[GUMBIE JAY]

I am getting a run-time error '438'
Object doesn't support this property or method.

This relates to the below piece of code
With .Sheets(1).CurrentRegion

Any suggestions

Will this macro still work if the sheet is protected with a password?

[snb]

adapt it to:



With .Sheets(1).cells(1).CurrentRegion