# Problem which name in the macro code

#### jpol

##### New Member
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

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
What do you mean by "can not be set name for G" exactly? This line of code worked for me:

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

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

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

For more clarity I will attach a filie in this evening

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

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

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

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
Code:
``.Rows(OstW + 3).Insert Shift:=xlDown``

Replies
3
Views
146
Replies
14
Views
415
Replies
1
Views
82
Replies
2
Views
168
Replies
0
Views
174

1,203,675
Messages
6,056,683
Members
444,883
Latest member
garyarubin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back