Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2004
    Posts
    20

    Unanswered: (RESOLVED)Yet another excel macro

    Hey I need some quick guidance for a excel macro..

    I need a macro that does this:

    1) Selects sheet2
    2) Copies all of sheet 2 to sheet 1
    3) Deletes sheet2
    3) Selects sheet 3
    3) Copies all fo sheet 2 into next available space on sheet 1
    4) Deletes sheet 3
    ......

    Here is the problem though... how can we automate this process because all the sheet names have no numbers and there are 99 sheets. is there a NextSheet command? Im Very new to VBA and dont have any books to reference so thats why im here Any help will be much appreicated.

    Doc
    Last edited by doctor; 07-02-04 at 11:42.

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Doc

    Try something like this

    Code:
    Sub Test()
        Dim I As Integer
        
        For I = 2 To Worksheets.Count
            'copy from worksheet
            Worksheets(I).Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) _
                    .EntireRow.Copy
            'paste to new worksheet
            Worksheets(1).Range("A" & Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row) _
                    .PasteSpecial
        Next I
        
        For I = Worksheets.Count To 2 Step -1
            Application.DisplayAlerts = False
            'delete all worksheets bar the first
            Worksheets(I).Delete
            Application.DisplayAlerts = True
        Next I
        
            
    End Sub
    Hope this helps
    David

  3. #3
    Join Date
    Jun 2004
    Posts
    20
    CHAAAA CHIING .. you the man!

  4. #4
    Join Date
    Jun 2004
    Location
    Italy
    Posts
    15
    Hi Doctor,

    to go through your sheets you can use:

    Do
    On error GoTo EndOfSheets
    Worksheets(1).Activate
    ActiveSheet.Next.Activate
    'You can also use ActiveSheet.Previous.Activate
    ...
    Loop


    EndOfSheets:
    MsgBox "End of sheets"

  5. #5
    Join Date
    Jun 2004
    Location
    Italy
    Posts
    15
    Come on David,
    you answered while I was writing...that's not fair :-)
    By the way, that's a good solution

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Quote Originally Posted by ioclio
    Come on David,
    you answered while I was writing...that's not fair :-)
    By the way, that's a good solution
    it comes with practice and iritation at how slow things are running

    it's best not to activate sheets select ranges etc slows thing way down
    trying to create macros without this is always a good idea



    David

  7. #7
    Join Date
    Jun 2004
    Location
    Italy
    Posts
    15
    Thanks for the advise.
    FatherXmas

  8. #8
    Join Date
    Jun 2004
    Posts
    20
    Hmm using

    Code:
    Sub Test()
        Dim I As Integer
        
        For I = 2 To Worksheets.Count
            'copy from worksheet
            Worksheets(I).Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) _
                    .EntireRow.Copy
            'paste to new worksheet
            Worksheets(1).Range("A" & Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row) _
                    .PasteSpecial
        Next I
        
        For I = Worksheets.Count To 2 Step -1
            Application.DisplayAlerts = False
            'delete all worksheets bar the first
            Worksheets(I).Delete
            Application.DisplayAlerts = True
        Next I
        
            
    End Sub
    In Excel works just fine. But now i need to call this from access, and to be honest im not good at calling excel macros from access, ive done it before but im having particular trouble with this one... can anyone help me out?? I know we need to append objects to their respective ".methods" but with the "Worksheets" its acting funny.

    Any help would be much appreciated.

    Thanks!
    Doc

  9. #9
    Join Date
    Jun 2004
    Posts
    20
    oops I think this should be in the Access VBA forum sorry ill repost there with a link to this one, but if any of you know how to solve my problem feel free to help me out
    Last edited by doctor; 07-15-04 at 16:24.

  10. #10
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Try this

    Add a reference to Excel

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cmdRun_Click()
        Dim xlApp As Excel.Application
        Dim xlbook As Workbook
        
        Set xlApp = New Excel.Application
        Set xlbook = xlApp.Workbooks.Open("C:\yourfilepath\yourfilename.xls")
        
        xlApp.Application.Run ("yourfilename.xls'!yourmacroname")
        
        xlApp.Visible = True
        
        Set xlbook = Nothing
        Set xlApp = Nothing
        
    End Sub
    ive atached this to a command button
    HTH
    David

Posting Permissions

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