Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 5 FirstFirst 1234 ... LastLast
Results 11 to 20 of 43

Thread: excel to excel, excel to word

  1. #11
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would define ws as a worksheet and fname as a string in my loop, like the following:

    Code:
    Dim ws As Worksheet, fname As String
    For Each ws In ThisWorkbook.Worksheets
    fname = ws.Name 'now save files as fname & ".xls"
    Next
    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-30 11:55 ]

  2. #12
    New Member
    Join Date
    Apr 2002
    Location
    NY, NY
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK- I got it to save a new workbook using each subsequent worksheet's tab name. But it saved sheet 1 under each name instead of saving every sheet with each name.

    I believe the code problem resides in Sheets(1).Copy. I tried Sheets.Copy and got a complete copy of the whole workbook under each sheet name...

    Thanks again---this will be a real time saver!

  3. #13
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think you'll need to incorporate the following into your macro, taking advatage of the definitions in place:

    Code:
    Sub test6()
    Dim ws As Worksheet, fname As String
    For Each ws In ThisWorkbook.Worksheets
    ws.Copy
    fname = ws.Name
    Set ws = nothing
    Next
    End Sub
    Where you copy ws.



    _________________
    Cheers, NateO

    [ This Message was edited by: nateo on 2002-05-01 08:45 ]

  4. #14
    New Member
    Join Date
    Apr 2002
    Location
    NY, NY
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    PUNT!

    I've tried to incorporate the code into my macro and can't get it to work... Better to start fresh?

    I have a master workbook of financial statements for 15 individuals. I'd like to copy each of the 15 worksheets to its own workbook and save that new workbook as the person's name (located on the worksheet tab of the master).

    Sorry to be such a know nothing but I'm learning thanks to you!

  5. #15
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ni ine has approached the excel to word bit,

    suggest looking at samples.xls in iffice ... the file give VBA to do this ..

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  6. #16
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ExecEd, and I thought we were getting closer with my choppy advice. I've taken the liberty of compiling my choppy advice, hope this helps:

    Code:
    Sub alShts()
    Dim ws As Worksheet, fname As String
    ChDrive ("C") ' drive letter
    ChDir ("c:temp") ' target directory
    For Each ws In ThisWorkbook.Worksheets
    fname = ws.Name
    ws.Copy
    ActiveWorkbook.Close True, Filename:=fname & ".xls"
    Next ws
    End Sub
    No need to worry, glad to hear you're learning. Make sure the directory in your vba module shows sing s (where this post may show 2 or 1). Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: nateo on 2002-04-30 14:36 ]

  7. #17
    New Member
    Join Date
    Apr 2002
    Location
    NY, NY
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    GOT IT!!!!!

    Sub CopyWorksheettoNewFile()
    Dim ws As Worksheet, fname As String
    current = 1
    For Each Worksheet In ThisWorkbook.Worksheets
    Set ws = Sheets(current)
    fname = ws.Name 'now save files as fname & ".xls"
    ws.Copy 'Copy first sheet to a new workbook
    'On Error GoTo errorhandler
    ChDrive ("C") ' drive letter
    ChDir ("c:statements") ' target directory
    If fname <> "" Then 'make sure fname is not blank
    ActiveWorkbook.SaveAs Filename:=fname & ".xls"
    Else: MsgBox "Please Enter a value in A1 and retry."
    End If
    Windows("FY 2002 200204.xls").Activate
    'Exit Sub
    'errorhandler:
    'MsgBox "Can 't find your target directory"
    current = current + 1
    If current > 15 Then Exit For
    Next
    End Sub

  8. #18
    New Member
    Join Date
    Apr 2002
    Location
    NY, NY
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    yours is much more elegant---

    Thanks for all the help!

    Hours of time saved!

  9. #19
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ed, this looks good, couple of comments:
    Code:
    ChDrive ("C") ' drive letter 
    ChDir ("c:statements") ' target directory
    -You may want this above your loop so as not too repeat this command for each worksheet


    Code:
    If fname <> "" Then 'make sure fname is not blank 
    ActiveWorkbook.SaveAs Filename:=fname & ".xls" 
    Else: MsgBox "Please Enter a value in A1 and retry."
    -You can't have a blank worksheet tab (I think), so there's no need for this if statement


    Job well done. Glad to be of help. Have a good one.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-30 14:59 ]

  10. #20
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    NateO: I tried your code, keeps stopping at

    sheets("name")? 'Run time error 9'

    Any suggestions?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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