It would help if you indent your code so that you can easily see when you BEGIN
a block of code like WITH and IF statements, then have a matching CLOSING
statemtent.
>From your code, I see that you end up with a couple of "End IF" statements
>that
don't have an "IF" statement that they are ending! (hence the "End If without
Block If" error)
also, you seem to have an extra "Next Wks" statement.
I think you're going to hyave several other problems too.
I haven't followed the original thread, so I don't know what you're trying to
accomplish,
but if you've changed any numeric name to "Year " & wks.name,
then you cannot use: Wks.Name >= 2010 because Wks.Name is a STRING, not a
number!
Sub aaa()
Dim Wks As Worksheet
Dim i As Integer
With ThisWorkbook.Worksheets("table Z")
Dim OstW As Long: OstW = .Cells(Rows.Count, 4).End(xlUp).Row
If OstW > 14 Then
.Range("B14:E" & OstW).ClearContents
End If
.Range("D14:E14").Value = Array("A", "B")
.Range("B15:B19").Value = Application.Transpose(Array("X", "Y", "Z",
"G", "H"))
OstW = .Cells(Rows.Count, 4).End(xlUp).Row + 1
For i = 1 To 5
For Each Wks In ThisWorkbook.Worksheets
If IsNumeric(Wks.Name) Then
Wks.Name = "Year " & Wks.Name
End If
If Wks.Name >= 2010 And Wks.Name <= Year(Date) Then
End If
Next Wks
If i = 1 Then
Wks.Range("D15:E15").Copy .Range("D" & OstW)
.Range("C" & OstW).Value = Wks.Name
.Rows(OstW + 1).Insert Shift:=xlDown
OstW = OstW + 1
ElseIf i = 2 Then
Wks.Range("D19:E19").Copy .Range("D" & OstW + 1)
.Range("C" & OstW + 1).Value = Wks.Name
.Rows(OstW + 2).Insert Shift:=xlDown
OstW = OstW + 1
ElseIf i = 3 Then
Wks.Range("D23:E23").Copy .Range("D" & OstW + 2)
.Range("C" & OstW + 2).Value = Wks.Name
.Rows(OstW + 3).Insert Shift:=xlDown
OstW = OstW + 1
ElseIf i = 4 Then
Wks.Range("D27:E27").Copy .Range("D" & OstW + 3)
.Range("C" & OstW + 3).Value = Wks.Name
.Rows(OstW + 4).Insert Shift:=xlDown
OstW = OstW + 1
ElseIf i = 5 Then
Wks.Range("D31:E31").Copy .Range("D" & OstW + 4)
.Range("C" & OstW + 4).Value = Wks.Name
.Rows(OstW + 5).Insert Shift:=xlDown
OstW = OstW + 1
End If
End If
End If
Next Wks
Next i
End With
End Sub
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: artforart artforart <artforart...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Wed, April 3, 2013 3:07:49 PM
Subject: $$Excel-Macros$$ Re: Arrangement head of the table in the macro code
Thank u for an answer. But unfortunately I have not been able to applied it.
Pls
find attached file. I've got again error: End if without block if.
W dniu środa, 3 kwietnia 2013 05:10:42 UTC+2 użytkownik Lalit Mohan Pandey
napisał:
You can change your code as mentioned below:
>
>
>
>For Each Wks In ThisWorkbook.Worksheets
>If IsNumeric(Wks.Name) Then
>Wks.Name = "Year " & Wks.Name
>end if
>If Wks.Name >= 2010 And Wks.Name <= Year(Date) Then
>end if
>next wks
>On Tuesday, 2 April 2013 20:01:41 UTC+5:30, artforart artforart wrote:
>If I add this bold part in code I've got End if without block if
>>
>>For Each Wks In ThisWorkbook.Worksheets
>>If IsNumeric(Wks.Name) Then Wks.Name = "Year " & Wks.Name
>>
>>If Wks.Name >= 2010 And Wks.Name <= Year(Date) Then
>>
>>Thank u for help
>>--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members
are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
