Changing structure of table in the code of macro - insert rows

[jpol]

Hello,

I've attached file 1: 5 variable which adding new year between the same numbers of empty rows
Now I would like chcnage sturture for table (file 2: 5 variable change structure) various empty rows between variables X Y Z G H. I don't know how to modify this macro.

Thank u for help.

file 1 Link

file 2 Link

 

[SkipVought]

Hi,

Please post your examples here directly. I cannot download your files.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpol]

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

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

OstW = .Cells(Rows.Count, 4).End(xlUp).Row + 1
For i = 1 To 5
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

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
.Rows(OstW + 3).Insert Shift:=xlDown
OstW = OstW + 1
ElseIf i = 4 Then
Wks.Range("D27:E27").Copy .Range("D" & OstW + 3)
.Range("C" & OstW + 3).Value = Wks.Name
.Rows(OstW + 4).Insert Shift:=xlDown
OstW = OstW + 1
ElseIf i = 5 Then
Wks.Range("D31:E31").Copy .Range("D" & OstW + 4)
.Range("C" & OstW + 4).Value = Wks.Name
.Rows(OstW + 5).Insert Shift:=xlDown
OstW = OstW + 1
End If
End If
End If
Next Wks
Next i
End With
End Sub

I have sheets: 2010, 2011, 2012, and table Z where is macro. When I run macro I've got in table Z below 2010 and 2011 new year 2012 together.

 

[SkipVought]

So what is the table structure of each sheet?

When you refer to changing table structure, that says to me that you want to change the number of columns in your table. If that is true, then that is not a best and accepted practice except if the table is a final report, like a cross-tab.pivot table.

When you say that you have "numbers of empty rows" and "various empty rows" that does not make sense in a proper table. Can you explain?

Separate tabs for different years is also not a best and accepted practice for maintaining data and being able to use Excel's abundant analysis and reporting features to their full extent without having to go to extreme measures to coerce a correct solution.

Please answer all these questions completely.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Are you doing a REPORT or SUMMARY in sheet Table Z?

Looks as if you clear the table and then rebuild it.

From each of the Year sheets you copy a single literal range. For instance for the first year you copy row 15; second sheet, 19, third sheet row 23, fourth sheet row 27, fifth sheet row 31. Seems like a pattern, but will those rows ALWAYS be that? Now in my world, the years add up. I've already seen 71 of them. So tell me, what happens to your code NEXT YEAR?

I suspect that your design and approch are not general enough. You will FOREVER be adjusting your code to aacount for new data points and THAT is not a best and accepted practice! Code should NEVER need to be modified to accomodate normal accumulation of data points!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpol]

In sheets 2010, 2011, 2012, I have source of date which structure is always constant.

In table Z I have got raport:
X 2010
2011
2012

Y 2010
2011
2012

Z 2010
etc.

So if I add sheet 2013 (where I have date XYZ) etc. after use macro I have been getting in table new year 2013 together and also one empty row below. So I don't need adjusting the code forever. Just I need added sheet and put date.


But now I need to modify code macro because the structure of table Z has changed.

In table Z I have got raport:
X 2010
2011
2012


Name
Y 2010
2011
2012

Z 2010
etc.

It is what I mean for various empty rows.(3 empty, 1 empty) Of corse the reason for add empty rows is sometimes create a "name" between X Y Z. But post before I treat this as empty row.


My question is how to modify the macro code that adjusted its for new structure in table Z.
Best regards

 

[SkipVought]

I set up a test workbook whti a table Z sheet and sheets 2010 thru 2014 and data in D15:E38.

My resulat

.........A        B
X 2010	2010-154 2010-155
  2011	2011-154 2011-155
  2012	2012-154 2012-155
  2013	2013-154 2013-155
  		
Y 2010	2010-194 2010-195
  2011	2011-194 2011-195
  2012	2012-194 2012-195
  2013	2013-194 2013-195
			
Z 2010	2010-234 2010-235
  2011	2011-234 2011-235
  2012	2012-234 2012-235
  2013	2013-234 2013-235
 		
G 2010	2010-274 2010-275
  2011	2011-274 2011-275
  2012	2012-274 2012-275
  2013	2013-274 2013-275
			
H 2010	2010-314 2010-315
  2011	2011-314 2011-315
  2012	2012-314 2012-315
  2013	2013-314 2013-315

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

using my result, please show what you want to change.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpol]

.........A         by analogy B     after changes table:   A         by analogy B
X 	2010-154                                     X     2010-154
  	2011-164                                           2011-164
  	2012-174                                           2012-174
  	2013-184                                           2013-184
  	empty-194                                          empty-194
Y       2010-204                                           empty-204
  	2011-214                                           name-214
  	2012-224                                     Y     2010-224
   	2013-234                                           2011-234
	empty-244	                                   2012-244	
Z 	2010-254                                           2013-254
   	2011-264                                           empty-264
   	2012-274                                     Z     2010-274
  	2013-284                                           2011-284
 	empty-294                                          2012-294
G 	2010-304                                           2013-304
  	2011-314                                           empty-314
  	2012-324                                           empty-324
  	2013-334                                     G     2010-334
	empty-344	                                   2011-344
H 	2010-354                                           2012-354
  	2011-364                                           2013-364
  	2012-374                                           empty-374
  	2013-384                                     H     2010-384
                                                           2011-394
                                                           2012-404
                                                           2013-414

sign X Y Z G H are start from 152, and sign date eg.2010 from 153

 

[SkipVought]

152? 153? what does that mean?

What is the LOGIC for extra lines or NAME?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpol]

.........A         by analogy B     after changes table:   A         by analogy B
X-152 	2010-154                                     X-152 2010-154
  	2011-164                                           2011-164
  	2012-174                                           2012-174
  	2013-184                                           2013-184
  	empty-194                                          empty-194
Y-202   2010-204                                           empty-204
  	2011-214                                           name-214
  	2012-224                                     Y-222 2010-224
   	2013-234                                           2011-234
	empty-244	                                   2012-244	
Z-252 	2010-254                                           2013-254
   	2011-264                                           empty-264
   	2012-274                                     Z-272 2010-274
  	2013-284                                           2011-284
 	empty-294                                          2012-294
G-302 	2010-304                                           2013-304
  	2011-314                                           empty-314
  	2012-324                                           empty-324
  	2013-334                                     G-332 2010-334
	empty-344	                                   2011-344
H-352	2010-354                                           2012-354
  	2011-364                                           2013-364
  	2012-374                                           empty-374
  	2013-384                                     H-382 2010-384
                                                           2011-394
                                                           2012-404
                                                           2013-414

This new extra lines or NAME will be constant. I don't know how treat NAME in macro code. Is it necessary included it in code or treat it as empty row? So that all comes down to the conclusion how to modify an old the macro code for new structure in table Z.

 

[SkipVought]

I give up. It's like talking to the wall!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpol]

What is wrong? What part of my answer you don't understand?

 

[SkipVought]

152? 153? what does that mean?

What is the LOGIC for extra lines or NAME?

In other words, what did you mean by stating, "sign X Y Z G H are start from 152, and sign date eg.2010 from 153?"

LOGIC: Under what conditions is a name inserted? Under what condtions is more than one empty row inserted?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpol]

I mark it on last post in test workbook. For example X-152 etc. for 2010 it is -153 etc. date-154 etc. It's mean a number of columns and rows.

No condition is for name. Unfortunately I don't decide about conditions. If I could decide about it I would leave the old version tab Z. I don't want to change the number of columns in table Z I' am just want changing a cod of macro. There isn't any link between old and new tables Z It is just simple situation Old table Z or New table Z.

I have one more question why u cannot download my files? Perhaps then will be faster way to explain.

 

[SkipVought]

I asked you to use my result and to describe the LOGIC.

You refuse to use my result to demonstrate the changes you expect and you refuse to describe the logic of when to add name and when to add empty rows. Therefore, I cannot help you under these circumstances.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}