How to modify the macro code for new structure of table

Closed
Report
Posts
14
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
Hello,

The short description an idea of macro code in the table Z is that if I add 2014 etc. (and other new sheets). It is directly moving me XYZGH to the table Z. So I don't have to bulid "manually" formulas in table Z from example 2014 data.

My problem is that I cannot modify the macro code - range of head for some new structure table Z? Could someone try adjustment this code pls? I attached file.

http://sendfile.pl/27679/5_variable_change_structure.xls

Thank u in adavance for help.
Regards Peter

9 replies

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Peter,

Not sure if I follow you.

You have a code line which prevents updates from any sheets higher then the current year. If this is what you meant then change:
If Wks.Name >= 2010 And Wks.Name <= Year(Date) Then
into
If Wks.Name >= 2010 Then
Don't know what you mean by: "range of head for some new structure table Z?"

Best regards,
Trowa
0
Posts
14
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
When u opening the sheet "table Z" U can see empty rows eg. 17,18,19, 22, 23 etc. It is places for new heads - names. For now I treat it as empty rows If u want u can write there something (I think It should beging from column D).

But if u run macro u can see that is only one empty rows between data. It is fault an old code of macro. I would like to do it that its well working with a new structure of table Z I want see empty rows eg. 18,19, 20, after run macro.

So that my problem is that I cannot modify the macro code.

Thank u in adavance for help.
Best regards
Peter
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Peter,

Paste the following part of code between the lines "Next wks" and "Next i":
If i = 1 Then
Rows(OstW).Insert
Rows(OstW).Insert
OstW = OstW + 2
ElseIf i = 2 Then
Rows(OstW + 1).Insert
Rows(OstW + 1).Insert
OstW = OstW + 2
ElseIf i = 3 Then
Rows(OstW + 2).Insert
Rows(OstW + 2).Insert
OstW = OstW + 2
ElseIf i = 4 Then
Rows(OstW + 3).Insert
Rows(OstW + 3).Insert
OstW = OstW + 2
ElseIf i = 5 Then
Rows(OstW + 4).Insert
Rows(OstW + 4).Insert
OstW = OstW + 2
End If

Let me know how that works out for you.

Best regards,
Trowa
0
Posts
14
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
Thank u very much for answer. When I pasted your code I've got 3 empty rows between all variables. But I would like to get a various number of empty rows.

According with the structure of empty rows - what u can see it in attachment file before run of macro i. e. between X and Y 3 empty rows (it is ok with your code),
between Y and Z 2 empty rows, between Z and G 4 empty rows, between G and H 1 empty row.

So that after run of macro I want see:
1) empty rows 18, 19, 20, between X and Y.
2) empty rows 24,25, between Y and Z.
3) empty rows 29, 30, 31, 32, between Z and G.
4) empty row 36 - between G and H.

Thank u in adavance for help.
Best regards
Peter
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Peter,

In that case use the following code part:
If i = 1 Then
Rows(OstW).Insert
Rows(OstW).Insert
OstW = OstW + 2
ElseIf i = 2 Then
Rows(OstW + 1).Insert
OstW = OstW + 1
ElseIf i = 3 Then
Rows(OstW + 2).Insert
Rows(OstW + 2).Insert
Rows(OstW + 2).Insert
OstW = OstW + 3
End If

Best regards,
Trowa
0
Posts
14
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
Hi Trowa

Thank u in advance for the time taken to answer and thanks for the control of the chaos in my microcosmos. :)

I have one more additional question.

What should I change in case If the name of sheets aren't numeric for example 2010, 2011, 2012, but are text and numeric for example names: price avarage 2010, price avarage 2011 ?

I suppose I should modify this part of the code.

For Each Wks In ThisWorkbook.Worksheets
If IsNumeric(Wks.Name) Then
If Wks.Name >= 2010 And Wks.Name <= Year(Date) Then
If i = 1 Then

Best regards
Peter
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Peter,

First we need to declare another variable to let Excel know that we want to work with numbers instead of text.

So change:
Dim i as integer
into:
Dim i, wYear As Integer

Then change the by you mentioned part of the code into:
For Each Wks In ThisWorkbook.Worksheets
If IsNumeric(Right(Wks.Name, 4)) Then
wYear = Right(Wks.Name, 4)
If wYear >= 2010 And wYear <= Year(Date) Then
If i = 1 Then

Best regards,
Trowa
0
Posts
14
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
And what If I have to use in macro a selected, concrete name. E.g. from two kinds of sheets name: price avarage 2010, price adjusted 2010. I have to use in macro only name: price avarage ?

How to determine it in code ?

Best regards,
Peter
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Peter,

Change:
If wYear >= 2010 And wYear <= Year(Date) Then
Into:
If wYear >= 2010 And wYear <= Year(Date) And Left(Wks.Name, 13) = "Price average" Then
Or:
If wYear >= 2010 And wYear <= Year(Date) And Left(Wks.Name, 14) <> "Price adjusted" Then

Please check spelling (avarage) and capitalization (price).

Best regards,
Trowa
0