98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel 2007 VBA code
Excel 2007 VBA code
Resolved · Urgent Priority · Version 2007
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
Fri 23 Oct 2009, 10:37Trainer 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: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
Fri 23 Oct 2009, 15:20Trainer 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 WSRegards
Stephen
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 tip:Removing custom dictionary entriesIf you add something to the custom dictionary in Excel you cannot remove it. The way to get around this is to go into word and remove it there. |
