Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Help with compile error please (Excel 2002)

    Hi

    I tried help but was unable to solve this compile error, can anyone oblige please.

    Sub SaveSheet_OPC_Det_Disinf()

    Dim varFile As Variant
    varFile = Application.GetSaveAsFilename("Export.xls", "Excel Workbooks (*.xls),*.xls")
    If varFile = False Then Exit Sub
    Worksheets ("OPC Det + Disinf OUTPUT") macro stops here (Worksheets is highlighted) invalid use of property?
    Range("A1:P71").Select
    Selection.Copy
    With ActiveWorkbook
    .SaveAs varFile
    .Close
    End With

    End Sub

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with compile error please (Excel 2002)

    Worksheets ("OPC Det + Disinf OUTPUT") is meaningless - it doesn't do anything. What do you want to accomplish? If you want to select the worksheet:

    Worksheets("OPC Det + Disinf OUTPUT").Select

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help with compile error please (Excel 2002)

    Hi Hans

    Thanks for that, I missed the .Select.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help with compile error please (Excel 2002)

    Hi Hans

    Whilst I was quick to say it worked, I overlooked one thing, instead of creating the new workbook with just the sheets OPC Det and Disinf OUPUT, if copies all sheets.
    can you guide me please
    Many thanks

    Braddy

    Sub SaveSheet_OPC_Det_Disinf()

    Dim varFile As Variant
    varFile = Application.GetSaveAsFilename("Export.xls", "Excel Workbooks (*.xls),*.xls")
    If varFile = False Then Exit Sub
    Sheets(Array("OPC Det", "Disinf OUTPUT")).Select
    Range("A1:P71").Select
    Selection.Copy
    With ActiveWorkbook
    .SaveAs varFile
    .Close
    End With

    End Sub
    If you are a fool at forty, you will always be a fool

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with compile error please (Excel 2002)

    That is because you don't tell Excel to create a new workbook. You select some sheets, select some cells, then copy those cells to the clipboard. How should Excel deduce from that that you want to create a new workbook?

    Replace the lines

    Sheets(Array("OPC Det", "Disinf OUTPUT")).Select
    Range("A1:P71").Select
    Selection.Copy

    with the following single line:

    Sheets(Array("OPC Det", "Disinf OUTPUT")).Copy

    Copying sheets (instead of selecting them) without specifying a destination copies them into a new workbook. See the VBA help for the Copy method as it applies to the Chart, Charts, Sheets, Worksheet, and Worksheets objects.

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help with compile error please (Excel 2002)

    Hi Hans

    Thank you for your time and patience, I don't want to copy the whole of these sheets, just a range say B2:O70


    Thanks Again

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with compile error please (Excel 2002)

    Does the following do what you want? (modify the ranges as needed)

    Sub SaveSheet_OPC_Det_Disinf()
    Dim varFile As Variant
    Dim wbkSrc As Workbook
    Dim wbkTrg As Workbook
    varFile = Application.GetSaveAsFilename _
    ("Export.xls", "Excel Workbooks (*.xls),*.xls")
    If varFile = False Then Exit Sub
    ' Current workbook
    Set wbkSrc = ActiveWorkbook
    ' New workbook with one sheet
    Set wbkTrg = Workbooks.Add(xlWBATWorksheet)
    ' Rename sheet
    wbkTrg.Worksheets(1).Name = "OPC Det"
    ' Copy range from OPC Det
    wbkSrc.Worksheets("OPC Det").Range("A1:P71").Copy _
    Destination:=wbkTrg.Worksheets("OPC Det").Range("A1")
    ' Add second sheet
    wbkTrg.Worksheets.Add After:=wbkTrg.Worksheets("OPC Det")
    ' Rename sheet
    wbkTrg.Worksheets(2).Name = "Disinf OUTPUT"
    ' Copy range from Disinf OUTPUT
    wbkSrc.Worksheets("Disinf OUTPUT").Range("A1:P71").Copy _
    Destination:=wbkTrg.Worksheets("Disinf OUTPUT").Range("A1")
    With wbkTrg
    .SaveAs varFile
    .Close
    End With
    End Sub

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help with compile error please (Excel 2002)

    Hi Hans

    Thats perfect, once again you leave me breathless.

    Grateful Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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