Excel 2007 VBA code

Resolved · Urgent Priority · Version 2007

Thu 22 Oct 2009, 12:58 Delegate Trevor said...

Trevor has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Access Intermediate course
Excel VBA Advanced course

Excel 2007 VBA code

Help please!

I am trying the following to copy a master sheet at 'A&E Summary' to all the worksheets in the workbook from the beginning until the sheet called 'List'. The code below works fine for a simple workbork numered say Sheets1 to 6 but not with a bigger workbork where the worksheets have been renamed. Any ideas ? Most grateful this end.

Sub looper()

Dim iCurWS As Integer
Dim WS As Worksheet

Sheets("A&E Summary").Activate
Cells.Select
Selection.Copy
For iCurWS = 2 To Worksheets.Count
Set WS = Sheets(iCurWS)
If LCase$(WS.Name) = "List" Then Exit For
Cells.Select
ActiveSheet.Paste

Next iCurWS

End Sub

Fri 23 Oct 2009, 10:37 Trainer Stephen said...

RE: Excel 2007 VBA code

Hi Trevor

Thanks for your question

I have examined your code, and as posted there are two crucial errors. First, when you "Move to" a new worksheet, you are not activating it before pasting. As a result, the "A&E Summary sheet" is still active and the data is being pasted into that sheet.

A second problem is your use of the LCase$ function. This is fine, but if you are using it then you have to set it equal to "list" and not "List" as you have in the code you posted.

I have made the necessary change, and run the code and it seems to work fine, I have included it below

Dim iCurWS As Integer

    Dim WS As Worksheet

    

    Sheets("A&E Summary").Activate

    Cells.Select

    Selection.Copy

    

    For iCurWS = 2 To Worksheets.Count

    

        Set WS = Sheets(iCurWS)

        If LCase$(WS.Name) = "list" Then Exit For

        

        Sheets(iCurWS).Activate

        Cells.Select

        ActiveSheet.Paste

    

    

    Next iCurWS

Hope this helps. If you have any further problems please do not hesitate to get back to me

Regards

Stephen

Fri 23 Oct 2009, 14:25 Delegate Trevor said...

RE: Excel 2007 VBA code

Stephen

Good to hear from you.

Thanks for getting back to me I now understand my errors.

Unfortunately I am getting the error message "Type mismatch" at the following line even with your improved code.

Set WS = Sheets(iCurWS)

The debug shows there is nothing in WS.

Any ideas.

Thanks in anticipation

Trevor

Fri 23 Oct 2009, 15:20 Trainer Stephen said...

RE: Excel 2007 VBA code

Hi Trevor

The type mismatch will occur at that point in your code if the workbook contains chart sheets inside the sheets that the code is cycling through. You have declared WS as a worksheet and then you are trying to assign a chart sheet to it.

You might try the following which uses a for each loop to go through each worksheet in the book until it encounter list



Dim WS As Worksheet



Sheets("A&E Summary").Activate

Cells.Select

Selection.Copy



For Each WS In ActiveWorkbook.Worksheets



    If WS.Name <> "A&E Summary" Then

    

        If LCase$(WS.Name) = "list" Then Exit For

    

        WS.Activate

        WS.Cells.Select

        

          

        ActiveSheet.Paste



    End If





Next WS