How to do an automation for copy paste, added new row (macro)

[Solved/Closed]
Report
Posts
14
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
-
Posts
14
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
-
Hello,
I have attached file

http://www.sendspace.com/file/wjh0oe
I want to automated a some step (tab table Z)
Thus, for example, that in X 2012:

1) I want to copy D16:E16
2) added a new one blank below before the paste D17:E17
3) converted the 2011 to 2012 in cells D17: E17 (ctr+h function find 2011 replace the 2012)
and by analogy, 1) 2) 3) for Y

And just every year

In the point 3) conversion step, it can converts the 2011 to 2012 or 2010 to 2012 as comfortably.

Regards. Thank you in advance for your answer



11 replies

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Jpppol,

OK it's clear to me what you want.

1) You say the loop isn't working for the year 2014. This is true because of the code line:
If Wks.Name >= 2010 And Wks.Name <= Year(Date) Then
So only sheet names between 2010 and 2013 (this year) will get the copy paste treatment.
If you don't want this, then remove the line (as well as 1 End If).

2) Formula's get lost. The only problem with this is that when an adjustment is made to any of the year sheets it isn't updated in the "table z" sheet.
You can counter this by running the code each time you open/activate the "table z" sheet.
To do this you can right-click on the sheets "table z" tab and select view code.
Paste this code:
Private Sub Worksheet_Activate()
Call aaa
End Sub

Does this solve your query?

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Jpppol,

I will try to explain.
I will try because I'm using a dutch version and having some trouble giving the correct translations.

Top menu's goto view > toolbars > Toolset something.
Or
Top menu's goto view > toolbars > Visual Basic
On the Visual basic toolbar find the pictogram with a hammer crossed with another tool. (This will activate the toolbar "Toolset something")

On this toolbar you will find the Command Button.
Click on it and draw your button on the sheet.

Right-click on button and select properties to adjust text, font, text color, button color etc..

Right-click on button and select View Code.
You will goto Visual Basic Editor where two lines are already given.
Place the line:
Call aaa
between those two lines.

Now to use the button you need to close Design Mode. Do this by clicking on the correct button on the Visual Basic toolbar (The button has a set square (or geo triangle), pencil and a ruler (or measuring rod) displayed on it.

Hopefully you can work with this.

Please let me know if my explanation was helpfull and maybe provide me with some correct translations.

And remember I'm here to help, so it's no trouble assisting you.

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Jpppol,

Have you read your own question?

You want to copy D16:E16
Paste it to D17:E17
Then replace its value's by 2012???

You want to add a blank row before paste,
you mean
row 16: data
row 17: empty row
row 18: pasted data

Please try again explaining what you want to get done.

Best regards,
Trowa
Posts
14
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
row 17 empty row
row 16 copy
row 17 pasted date


https://www.sendspace.com/file/9aflb6


Start to test this get done macro. You has known what I did mean.

Unfortunately there is some problems
1) with loop macro:
I use macro only for sheet: table Z.

When I start macro then added me next year below from source date.
For example when I want to got 2012 I added a source date - new sheet 2012 (in attachment is ready added) and I start macro in table Z etc. every years. When I want put 2014, it is something wrong (loop don't working)
2) After used macro the formula bar (table Z) is lost. Formula shows a source date

Regards. Thank you in advance for your answer.
Posts
14
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
Thank u for first solution.

I need some explain for the second point.
I'v pasted your code in sheet "table z" and next run a macro and still formula's get lost. Do I must to add or change any code in aaa macro code?
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Jpppol,

For the second point I would ask you why you want to keep the formula's?

Your answer would probably be to keep the data in sheet "table z" up to date.
Meaning that when a change is made to one the "year" sheets, you want to see those changes back in the "table z" sheet.
Personally I can't think of another reason to keep the formula's. If you have one, then please enlighten me.

So to keep the data in sheet "table z" up to data, I thought of running the code each time you look (activate) at the "table z" sheet.
To do this you paste the Worksheet_Activate code as described in previous post.
When you have done this you will notice that the code is run every time you activate (click on) the "table z" sheet.

Hope this clarifies things.

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

I have not noticed that is working without formula's. I'm terrible sorry to bother you, but I wonder if you would mind helping me to create a button in table Z instead of an activate (click on) the "table z" sheet. If it's no trouble, of corse.

Best regards
Posts
14
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
The explanation is great.

And finally the last question. I am try to add a third variable Z (bold this)

I don't wrote in the cod of macro very well

This doesn't work here is compile error
Else without if

Do u know what should be changing?


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

OstW = .Cells(Rows.Count, 4).End(xlUp).Row + 1
For i = 1 To 2
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
Else
Wks.Range("D19:E19").Copy .Range("D" & OstW + 1)
.Range("C" & OstW + 1).Value = Wks.Name
OstW = OstW + 1
Else
Wks.Range("D21:E21").Copy .Range("D" & OstW + 1)
.Range("C" & OstW + 1).Value = Wks.Name
OstW = OstW + 1

End If
End If
Next Wks
Next i
End With
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Jpppol,

Good to see my button explanation did it's job.

For the code I made some minor adjustments, check it out:
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:B17").Value = Application.Transpose(Array("X", "Y", "Z"))

OstW = .Cells(Rows.Count, 4).End(xlUp).Row + 1
For i = 1 To 3
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("D21:E21").Copy .Range("D" & OstW + 2)
.Range("C" & OstW + 2).Value = Wks.Name
OstW = OstW + 1
End If
End If
Next Wks
Next i
End With


Best regards,
Trowa
Posts
14
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
https://www.sendspace.com/file/tk2jrz


I've applied this cod but I noticed that is something is wrong - values for Z are missed. Even I prepare identical start with values Z same like in X Y 2010, 2011.
The next year 2012 is missed and then I lost all values also for 2010 2011.

Additionally is something wrong with name variable Z If I start macro that is added me 2 rows instead 1?

For better clarity I've attached link

And again thank u very much

Best regards
Posts
14
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
OK I've solved it

https://www.sendspace.com/file/apx4mw

Best regards