How to modify the macro code for new structure of table

Closed
jpppol Posts 14 Registration date Wednesday February 27, 2013 Status Member Last seen July 30, 2013 - Jul 17, 2013 at 06:38 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Jul 30, 2013 at 10:17 AM
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

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jul 18, 2013 at 11:06 AM
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
jpppol Posts 14 Registration date Wednesday February 27, 2013 Status Member Last seen July 30, 2013 1
Jul 19, 2013 at 04:18 AM
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
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jul 23, 2013 at 11:07 AM
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
jpppol Posts 14 Registration date Wednesday February 27, 2013 Status Member Last seen July 30, 2013 1
Jul 25, 2013 at 07:25 AM
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

Didn't find the answer you are looking for?

Ask a question
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jul 25, 2013 at 10:34 AM
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
jpppol Posts 14 Registration date Wednesday February 27, 2013 Status Member Last seen July 30, 2013 1
Jul 26, 2013 at 04:16 AM
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
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jul 29, 2013 at 10:31 AM
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
jpppol Posts 14 Registration date Wednesday February 27, 2013 Status Member Last seen July 30, 2013 1
Jul 30, 2013 at 04:43 AM
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
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jul 30, 2013 at 10:17 AM
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