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

Question

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

TrowaD · Answer

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

jpppol · Answer

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.

TrowaD · Answer

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) ThenSo 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

jpppol · Answer

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?

TrowaD · Answer

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

jpppol · Answer

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

TrowaD · Answer

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

jpppol · Answer

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

TrowaD · Answer

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

jpppol · Answer

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

jpppol · Answer

OK I've solved it

https://www.sendspace.com/file/apx4mw

Best regards

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

11 responses

Similar discussions

Subscribe To Our Newsletter!