![]() |
#1
|
|||
|
|||
![]()
I have the following code:-
Code:
Sub CopyRows() ' copy all rows of data in worksheet 'temp' to the appropriate worksheets Dim iLooper As Long, NextRow As Long, ws As Worksheet, strSheet As String With Worksheets("temp") For iLooper = 1 To .Cells(Rows.Count, 1).End(xlUp).Row If UCase(Trim(.Cells(iLooper, "B").Value)) Like "ENROLLMENT*" Then strSheet = "ENROLLMENT" ElseIf UCase(Trim(.Cells(iLooper, "B").Value)) Like "WEEK*" Then strSheet = .Cells(iLooper, "a").Text Else strSheet = "" End If If strSheet <> "" Then Set ws = Worksheets(strSheet) NextRow = ws.Columns("B").Find("*", , xlValues, , 1, 2).Row + 1 ws.Rows(NextRow).Insert ws.Cells(NextRow, "A").Resize(, 8).Value = _ .Cells(iLooper, "A").Resize(, 8).Value End If Next End With ' delete all rows of data in worksheet 'temp' Dim r As Long For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 If Cells(r, 1) = "" Then Rows(r).Delete Next r End Sub The routine processes all the lines as they should be - and so the error occurs at the end of processing and, of course, the lines of data in 'temp' are not erased Would welcome any comments on how to correct the code.......... Bob M Last edited by H28Sailor; 12-06-2016 at 02:01 AM. Reason: Add code tags |
#2
|
|||
|
|||
![]()
At the time the procedure faults do you have a sheet with the name in the strSheet variable?
Be sure to check for leading and/or trailing spaces. |
#3
|
|||
|
|||
![]()
Nor sure how I identify the value of the strSheet variable at the time it falls over?
|
#4
|
|||
|
|||
![]()
Mouse over strSheet and VBA should tell you.
Have a look at http://www.cpearson.com/excel/DebuggingVBA.aspx for info regarding debugging code. |
#5
|
|||
|
|||
![]()
yes, there are several rows with the value that strSheet has including the final row of data to be processed
|
#6
|
|||
|
|||
![]()
OK - have realised that I do not have a tab in the workbook with that value
All sorted :0 Bob M |
#7
|
|||
|
|||
![]()
Glad you got things rectified.
|
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA Routine debug question | H28Sailor | Excel | 1 | 12-05-2016 10:51 PM |
![]() |
gbaker | Excel Programming | 5 | 10-01-2016 10:35 AM |
![]() |
Smithy02468 | Excel Programming | 1 | 11-08-2014 11:29 PM |
![]() |
Hwyn | Excel Programming | 5 | 01-18-2012 08:02 AM |
Macro or routine required | evanhughes | Outlook | 0 | 11-18-2011 02:56 AM |