Yet another excel macro

doctor · #1 (**permalink**) 07-02-04, 09:29

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

DavidCoutts · #2 (**permalink**) 07-02-04, 10:18

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

doctor · #3 (**permalink**) 07-02-04, 10:42

CHAAAA CHIING .. you the man!

ioclio · #4 (**permalink**) 07-02-04, 10:45

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"

ioclio · #5 (**permalink**) 07-02-04, 10:47

Come on David,
you answered while I was writing...that's not fair :-)
By the way, that's a good solution

DavidCoutts · #6 (**permalink**) 07-02-04, 10:58

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

ioclio · #7 (**permalink**) 07-02-04, 11:57

Thanks for the advise.
FatherXmas

doctor · #8 (**permalink**) 07-15-04, 14:41

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

doctor · #9 (**permalink**) 07-15-04, 14:42

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

DavidCoutts · #10 (**permalink**) 07-16-04, 04:29

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****n ("yourfilename.xls'!yourmacroname")
    
    xlApp.Visible = True
    
    Set xlbook = Nothing
    Set xlApp = Nothing
    
End Sub

ive atached this to a command button
HTH
David

Internet Services

Web Development

Digital Marketing

Consumer Tech