Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel 2007 VBA code
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel 2007 VBA code
Resolved · Urgent Priority · Version 2007
Reply Thu 22 Oct 2009, 12:58Delegate 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
For upcoming training course dates see: Pricing & availability
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
Reply Fri 23 Oct 2009, 14:25Delegate 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
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
Regards
Stephen
Reply Mon 26 Oct 2009, 10:32Delegate Trevor said...
RE: Excel 2007 VBA code
Stephen
Thank you very much indeed - works like a dream - I am as ever sitting on the shoulders of giants !
Hope to see you soon.
Regards
Trevor
|