PatWForbes
03-16-2009, 07:57 PM
Hi,
I'm quite new to VBA and would appreciate any help you could give.
In the below macro, I am working with two separate spreadsheets. One spreadsheet (from which I am running the macro), is a summary sheet. The second spreadsheet is variable in that its the format is the same everyday but the data and filename are different. My goal is to aggregate information from the daily spreadsheet into the summary spreadsheet. Because the name changes everyday on the daily spreadsheet, I've used the GetOpenFilename command. Once I get the data I want to collect, I open the summary workbook inside the macro, create criteria about where I want it pasted (using the find method) and paste. I then try to switch back to the daily sheet (which I opened originally opened via GetOpenFilename) and am unable to figure out how to reference it. The purpose of switching back is to copy more material to then paste in the summary sheet. Any help in activating to the daily file would be greatly appreciated.
Best regards,
Pat
Sub MacroinWorkbook()
'
Application.ScreenUpdating = False
ChDrive "C"
FlName = Application.GetOpenFilename
Workbooks.Open FlName
Range("M11").Select
ActiveCell.Replace What:="cob ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="cob ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=True, SearchFormat:=False, ReplaceFormat:= _
False
Range("M11").Select
Selection.NumberFormat = "mm/dd/yy;@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("M11:M149").Name = "Total"
Range("Total").Select
Selection.Copy
Workbooks("Summary sheet.xls").Activate
Sheets("Summary").Select
Cells.Find(What:="!!!!", SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
:=True, SearchFormat:=False).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks (FlName).Activate
Application.ScreenUpdating = True
End Sub
I'm quite new to VBA and would appreciate any help you could give.
In the below macro, I am working with two separate spreadsheets. One spreadsheet (from which I am running the macro), is a summary sheet. The second spreadsheet is variable in that its the format is the same everyday but the data and filename are different. My goal is to aggregate information from the daily spreadsheet into the summary spreadsheet. Because the name changes everyday on the daily spreadsheet, I've used the GetOpenFilename command. Once I get the data I want to collect, I open the summary workbook inside the macro, create criteria about where I want it pasted (using the find method) and paste. I then try to switch back to the daily sheet (which I opened originally opened via GetOpenFilename) and am unable to figure out how to reference it. The purpose of switching back is to copy more material to then paste in the summary sheet. Any help in activating to the daily file would be greatly appreciated.
Best regards,
Pat
Sub MacroinWorkbook()
'
Application.ScreenUpdating = False
ChDrive "C"
FlName = Application.GetOpenFilename
Workbooks.Open FlName
Range("M11").Select
ActiveCell.Replace What:="cob ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="cob ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=True, SearchFormat:=False, ReplaceFormat:= _
False
Range("M11").Select
Selection.NumberFormat = "mm/dd/yy;@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("M11:M149").Name = "Total"
Range("Total").Select
Selection.Copy
Workbooks("Summary sheet.xls").Activate
Sheets("Summary").Select
Cells.Find(What:="!!!!", SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
:=True, SearchFormat:=False).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks (FlName).Activate
Application.ScreenUpdating = True
End Sub