Problem which name in the macro code

[jpol]

question, I added four variable and can not be set name for G. How to do it?

I've increased the range:
. Range ("B15: B18")

It takes G directly bleow Z


Sub aaa()
Dim Wks As Worksheet
Dim i As Byte

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:B18").Value = Application.Transpose(Array("X", "Y", "Z", "G"))

OstW = .Cells(Rows.Count, 4).End(xlUp).Row + 1
For i = 1 To 4
For Each Wks In ThisWorkbook.Worksheets
If IsNumeric(Wks.Name) Then
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
OstW = OstW + 1
ElseIf i = 4 Then
Wks.Range("D27:E27").Copy .Range("D" & OstW + 3)
.Range("C" & OstW + 3).Value = Wks.Name
OstW = OstW + 1
End If
End If
Next Wks
Next i
End With
End Sub

 

[Andrew Poulsom]

What do you mean by "can not be set name for G" exactly? This line of code worked for me:

Range("B15:B18").Value = Application.Transpose(Array("X", "Y", "Z", "G"))

 

[jpol]

I've added fourth variable G and then name G has get lost a position. Should be like others X Y Z

X 2010 Values
2011 etc
2012

Y 2010
2011
2012

Z 2010
G 2011
2012

2010
2011
2012

 

[Andrew Poulsom]

Sorry, I don't understand. I can't see where you are using what's in B15:B18 in your code.

 

[jpol]

For more clarity I will attach a filie in this evening

 

[jpol]

www.sendspace.com/file/xzch9s

In table Z The G is in cell B24 should be in B27 and after macro below

I've got an advice to look what is in part when i = 1 or 2, and what is missing when i = 3

 

[Andrew Poulsom]

Is it because you are missing a line like this if i is 3 or 4?

.Rows(OstW + 2).Insert Shift:=xlDown

 

[jpol]

Yes, thank u. I thought it should be correct cod for i = 4 because had worked well with i=3
But it must be so that

.Rows(OstW + 3).Insert Shift:=xlDown