Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with VB for excel file (2003)

    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.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with VB for excel file (2003)

    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?


    <code>
    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
    </code>
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with VB for excel file (2003)

    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.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with VB for excel file (2003)

    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.

    <code>
    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
    </code>

    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?
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with VB for excel file (2003)

    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.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with VB for excel file (2003)

    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.

    <code>
    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) & ")"
    </code>
    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with VB for excel file (2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •