Results 1 to 9 of 9

Thread: Error in Macro

  1. #1
    Join Date
    Jun 2004
    Posts
    23

    Unhappy Unanswered: Error in Macro

    I Have a template. When you click the button it is supposed to :

    Select A3:c500
    Open a new sheet
    Paste values ( from above )
    Format Row C1:c500
    In a NUMBER format "0.00"
    Save sheet as c:\Name.CSV
    then CLOSE the CSV file. It is NOT working correctly..Can you assit?

    range("A3:C500").Select
    Selection.Copy
    range("A3:c500").Select
    Workbooks.Add
    ' Range("A3:C500").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    range("c1:c500").Select
    Selection.NumberFormat = "0.00"
    'range("A1").Select
    ChDir "C:\"
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.delete
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.delete
    range("A1").Select
    ActiveWorkbook.SaveAs Filename:="C:\looki.csv", FileFormat:=xlCSV, _
    CreateBackup:=False

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by scobad
    I Have a template. When you click the button it is supposed to :

    Select A3:c500
    Open a new sheet
    Paste values ( from above )
    Format Row C1:c500
    In a NUMBER format "0.00"
    Save sheet as c:\Name.CSV
    then CLOSE the CSV file. It is NOT working correctly..Can you assit?
    What kind of error are you getting? Which line is the problem?

    Note, sometimes it is better NOT to select to do work on a range. For instance, change

    Code:
    range("A3:C500").Select
        Selection.Copy
    to

    Code:
    Range("A3:A500").Copy
    So also, here:
    Code:
    range("c1:c500").Select
        Selection.NumberFormat = "0.00"
    to

    Code:
    range("c1:c500").NumberFormat = "0.00"
    Is there a need to select the range again? [3rd line - range("A3:c500").Select]
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    What is it doing or not doing?

    What is it doing or not doing that you want done?

    Is there an error message? Is it saving the file as looki.csv.xls? Is it not working at all?

  4. #4
    Join Date
    Jun 2004
    Posts
    23

    Exclamation Still Broke

    Ok - Here goes:

    Run-time error' -2147417848 (80010108)':

    Method 'Paste' of Object'_Worksheet'Failed

    Code:

    range("A3:C500").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    range("A1").Select
    Application.CutCopyMode = False
    ChDir "C:\Documents and Settings\badgsc00\Desktop"
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.delete
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.delete
    ChDir "C:\"
    ActiveWorkbook.SaveAs Filename:="C:\Working.csv", FileFormat:=xlCSV, _
    CreateBackup:=False
    ActiveWorkbook.Save
    ActiveWindow.Close
    range("A3:C500").Select
    Selection.ClearContents
    range("A3").Select
    ActiveWorkbook.Save
    End Sub

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    I'm in a hurry, but I suspect the error appears because you are trying to select the entire range in the new workbook. Try to paste using the range for one cell.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Jun 2004
    Posts
    23

    You are RIGHT...Now what

    When I fixed the FIRST line to read Select.range (A1:A1)

    It was PERFECT...So how do I grab the WHOLE range?
    A3 - c500

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    Here is your problem:

    range("A3:C500").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste

    If you are setting CutCopyMode to false prior to pasting doesn't that mean there is nothing to paste?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Jun 2004
    Posts
    23

    Still a NO

    I changed to TRUE - Still did NOT work... :-(

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I thing this should do it (I hope!)

    [code)
    Option Explicit

    Sub TestCopyPaste()

    Application.ScreenUpdating = False
    Range("A3:C500").Copy

    Workbooks.Add

    Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Range("c1:C500").NumberFormat = "0.00"
    Range("A1").Select

    ChDir "C:\"


    ActiveWorkbook.SaveAs FileName:="C:\looki.csv", FileFormat:=xlCSV, _
    CreateBackup:=False

    'ActiveWorkbook.Close SaveChanges:=False

    Application.ScreenUpdating = True
    End Sub

    [code/]


    Closing the csv file is optional!

    HTH

    MTB

Posting Permissions

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