How to do an automation for copy paste, added new row (macro)
Solved/Closed
jpppol
Posts
13
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
-
Feb 28, 2013 at 02:41 PM
jpppol Posts 13 Registration date Wednesday February 27, 2013 Status Member Last seen July 30, 2013 - Mar 22, 2013 at 04:59 PM
jpppol Posts 13 Registration date Wednesday February 27, 2013 Status Member Last seen July 30, 2013 - Mar 22, 2013 at 04:59 PM
Related:
- How to do an automation for copy paste, added new row (macro)
- Saints row 2 cheats - Guide
- How to delete a row in a table in word - Guide
- Spell number in excel without macro - Guide
- How to paste photo in resume - Guide
- Excel macro to create new sheet based on value in cells - Guide
11 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Mar 5, 2013 at 11:05 AM
Mar 5, 2013 at 11:05 AM
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 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:
Does this solve your query?
Best regards,
Trowa
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Mar 11, 2013 at 11:58 AM
Mar 11, 2013 at 11:58 AM
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
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Mar 4, 2013 at 12:09 PM
Mar 4, 2013 at 12:09 PM
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
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
Posts
13
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
Mar 4, 2013 at 01:20 PM
Mar 4, 2013 at 01:20 PM
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.
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.
Didn't find the answer you are looking for?
Ask a question
jpppol
Posts
13
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
Mar 6, 2013 at 04:53 AM
Mar 6, 2013 at 04:53 AM
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?
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Mar 7, 2013 at 10:18 AM
Mar 7, 2013 at 10:18 AM
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
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
Posts
13
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
Mar 8, 2013 at 05:23 AM
Mar 8, 2013 at 05:23 AM
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
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
jpppol
Posts
13
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
Mar 11, 2013 at 04:54 PM
Mar 11, 2013 at 04:54 PM
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
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Mar 12, 2013 at 11:24 AM
Mar 12, 2013 at 11:24 AM
Hi Jpppol,
Good to see my button explanation did it's job.
For the code I made some minor adjustments, check it out:
Best regards,
Trowa
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
Posts
13
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
Mar 12, 2013 at 02:45 PM
Mar 12, 2013 at 02:45 PM
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
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
Posts
13
Registration date
Wednesday February 27, 2013
Status
Member
Last seen
July 30, 2013
1
Mar 22, 2013 at 04:59 PM
Mar 22, 2013 at 04:59 PM
OK I've solved it
https://www.sendspace.com/file/apx4mw
Best regards
https://www.sendspace.com/file/apx4mw
Best regards