Help with VB for excel file (2003)

Topic

New Reply

Hello everyone,
I need help with a VB coding I am trying to write for an excel file. Attached is the code:

Sub SCOMacro()

‘Delete sheet if already exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets(“SCO Positions”).Delete
On Error GoTo 0
Application.DisplayAlerts = True

myfile = Application.GetOpenFilename
Workbooks.Open Filename:=myfile
Selection.AutoFilter Field:=1, Criteria1:=”804″
Selection.AutoFilter Field:=2, Criteria1:=”999″
Range(“A1”).Select
Selection.CurrentRegion.Select
Selection.Copy
Windows(“positions-Master file for Nov 05-Mary(copy).xls”).Activate
Sheets.Add
ActiveSheet.Paste
Range(“J1”).Select
Application.CutCopyMode = False
Selection.Copy
Range(“A1:U1”).Select
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Columns(“A:U”).Select
Columns(“A:U”).EntireColumn.AutoFit
ActiveSheet.Name = “SCO Positions”
Sheets(“SCO Positions”).Move after:=Sheets(“BCP PC-08 Positions”)
Range(“A1”).Select

End Sub

The problems I have are that the file choosen by the getfile command stays open and I need to close it. Also the Windows(“positions-Master file for Nov 05-Mary(copy).xls”).Activate can chance (for example next month will be Dec 05). Last, Once the macro is run, I would like to create another macro to do a sum for a column two rows below the last data information. Any help would be great. Thanks.

Reply | Quote

Replies

The code below should close the workbook that the code opens. We will need some additional information to fix the other requests:

1- At what point does the name of the workbook (“positions-Master file for Nov 05-Mary(copy).xls”) change? Since it is already Dec 05, can we assume that the name is for the previous month? In other words, is the Nov 05 workbook processed in Dec 05, and the Dec 05 workbook in Jan 06. If not, what is the rule for knowing the name?

2- What is the column that you want to sum? Do you want to insert a formula two rows below the last data in that column that sums the column, or do you want to put the numeric value of the sum into that cell? Does the sum start in the first row of the column, or are there some header rows that do not get summed?


Sub SCOMacro()
Dim myfile As Variant
Dim oWB As Workbook
myfile = Application.GetOpenFilename
If myfile = False Then Exit Sub
Set oWB = Workbooks.Open(Filename:=myfile)
'Delete sheet if already exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("SCO Positions").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Selection.AutoFilter Field:=1, Criteria1:="804"
Selection.AutoFilter Field:=2, Criteria1:="999"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Windows("positions-Master file for Nov 05-Mary(copy).xls").Activate
Sheets.Add
ActiveSheet.Paste
Range("J1").Select
Application.CutCopyMode = False
Selection.Copy
Range("A1:U1").Select
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Columns("A:U").Select
Columns("A:U").EntireColumn.AutoFit
ActiveSheet.Name = "SCO Positions"
Sheets("SCO Positions").Move after:=Sheets("BCP PC-08 Positions")
Range("A1").Select
oWB.Close
End Sub


Reply | Quote

Thanks Coleman for the code. Regarding your questions:
1. The positions-Master file for Nov 05-Mary(copy).xls will be use as a base and it will be copy to next month but rename Dec 05. My fear is that since the code referes to a particular window(“position….”) which has the file name, it will give them an error. Am I wrong to assume that?
2. The column I want to sum is F. I will like to put it two rows below the last data entry.

Any Feedback would be great.

Reply | Quote

You are correct, if the file name is changed to “positions-Master file for Dec 05-Mary(copy).xls”, then that statement will give an error (specifically a “Subscript out of range” error). However, you did not answer all of my questions, so I can not tell you how to fix the error. One additional question that I just thought of: is that file the active file when you open the file that you get the name of with the GetOpenFilename method? if it is, then the code below should solve that problem.


Sub SCOMacro()
Dim myfile As Variant
Dim oOriginalWB As Workbook, oWB As Workbook
Set oOriginalWB = ActiveWorkbook
myfile = Application.GetOpenFilename
If myfile = False Then Exit Sub
Set oWB = Workbooks.Open(Filename:=myfile)
'Delete sheet if already exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("SCO Positions").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Selection.AutoFilter Field:=1, Criteria1:="804"
Selection.AutoFilter Field:=2, Criteria1:="999"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
oOriginalWB.Activate
Sheets.Add
ActiveSheet.Paste
Range("J1").Select
Application.CutCopyMode = False
Selection.Copy
Range("A1:U1").Select
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Columns("A:U").Select
Columns("A:U").EntireColumn.AutoFit
ActiveSheet.Name = "SCO Positions"
Sheets("SCO Positions").Move after:=Sheets("BCP PC-08 Positions")
Range("A1").Select
oWB.Close
End Sub


You also did not answer all of my questions about what you want to sum and how you want to sum it. Does the data start in row 1 or are there header rows? Do you want a formula in the cell to sum the cells above or do you want the value that is the sum?

Reply | Quote

You are correct in your assumptions. The active file is the one that we are in when we do the getopenfilename method. I am sorry to be such a pest, but If the sheet “SCO Positions” is already created, I thought the ‘Delete sheet if already exists will take care of it, but when I run the macro it stops at ActiveSheet.Name = “SCO Positions”. Any ideas? Regarding the sum, all I wanted to do is to sum column F from cell f1 to where the data ends. Thanks for all your help.

Reply | Quote

When you say that the code stops at that statement, what do you mean. Do you get an error message? If so, what is it? If I set up a test workbook, the code does delete that sheet if it exists, and the statement where you say it stops executes OK.

You still have not answered the question whether you want a SUM formula in the cell in Column F or if you want the numeric value of the SUM. Again, guessing at what you want, the code below will put a formula in the cell two cells below the last value in column F that sums the cells above.


Dim lLastRow As Long
lLastRow = Range("F65536").End(xlUp).Row - 1
Range("F1").Offset(lLastRow + 2).Formula = "=SUM(F1:" & _
Range("F1").Offset(lLastRow, 0).Address(False, False) & ")"


Reply | Quote

Thanks LegareColeman. I made the mistake of editing a line in the coding and that is why it didn’t work. By the way. The statement below did what I need to. Thanks again.

Reply | Quote