How to modify the macro code for new structure of table Closed

Question

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
Configuration: Windows 7 / Firefox 22.0

TrowaD · Answer

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) Theninto
If Wks.Name >= 2010 ThenDon't know what you mean by: "range of head for some new structure table Z?"

Best regards,
Trowa

jpppol · Answer

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

TrowaD · Answer

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

jpppol · Answer

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

TrowaD · Answer

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

jpppol · Answer

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

TrowaD · Answer

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 integerinto:
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

jpppol · Answer

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

TrowaD · Answer

Hi Peter, Change: If wYear >= 2010 And wYear <= Year(Date) ThenInto: If wYear >= 2010 And wYear <= Year(Date) And Left(Wks.Name, 13) = "Price average" ThenOr: 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

How to modify the macro code for new structure of table

9 responses

Similar discussions

Subscribe To Our Newsletter!