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

[jpol]

Hello
I have attached file

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

 

[macropod]

Cross-posted at:

http://www.msofficeforums.com/excel...utomation-copy-paste-added-new.html#post45589

For cross-posting etiquette, please read:

http://www.excelguru.ca/content.php?184

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

hi,

Yes you should not be cross posting like this.

But here are a few suggestions gleamed from nearly 20 years' experience in Excel.

1. There are pitfalls associated with using the INSERT method in tables. I nearlt always add new data at the bottom of my table and then SORT rows into the desired order. I would AVOID using the INSERT method!

2. Often you have FORMULAS in the row that need to propagate to any new row. In Excel 2007+ the Structure Table feature (Insert > Tables > Table and Excel HELP for info) make this a moot point when you add a new row AT THE BOTTOM OF YOUR TABLE, as ALL FORMULAS ARE PROPAGATED. There are so many slick features associated with Structured Tables, that I make nearly all my tables Structured Tables.

3. If you want to do any automation, your Macro Recorder is a great asset.

Skip,

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

 

[jpol]

Sorry for cross-posted. I've sent in this way because I've not got any responds. So it isn't idea: If you post on several boards, you have several groups of people working on the same issue, without necessarily knowing what the others are doing. Perhaps badly presented to the Board. By the way sorry. I would like to use Macro Records. I except some macro cod. I've tried doing something with offset, but it is misunderstanding for this example.(I've noticed that marko works well from 2012, but it puts them at the top.)

2012
2013
2011
2010
2010
2011

At the beginning of adding duplicates if the opposite:
2011
2010

It should be like this:

X
2010
2011
2012
2013

Y
2010
2011
2012
2013

I have attached correct file (inital state)

 

[SkipVought]

I've looked at your workbook and the data structure is a mess in my opinion.

Excel is designed to work using tables and correct table structures. If your data structure deviates from what Excel expects, then you will, without fail, experience lots and lots of problems manipulating your data and using formulas to accomplish anything useful in a reasonable period of time. Macros will not make anything easier.

faq68-5184



Skip,

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

 

[SkipVought]

okay, maybe your mess is salvageable.

1) I made each of the tables on your 3 sheets labeled 2010, 2011 & 2012 into Structured Tables using Insert > Tables > Table. The tables are named t_2010, t_2011, & t_2012 respectively.

2. on the Table Z sheet I used Data > Get external data > from other sources > from microsoft query... and drill down to your workbook. I use the following SQL in the SQL box
[tt]
select distinct *

from (

SELECT Column1, 2010 as year, A,B
FROM `C:\Users\Skip\Downloads\1.xls`.`'2010$'`

union all

SELECT Column1, 2011, A,B
FROM `C:\Users\Skip\Downloads\1.xls`.`'2011$'`

union all

SELECT Column1, 2012, A,B
FROM `C:\Users\Skip\Downloads\1.xls`.`'2012$'`

)

where a is not null
[/tt]
Of course your file path will be different than mine.

When I File > Retur data to microsoft Excel, it returns this...
[tt]
Column1 year A B
X 2010 100 200
X 2011 600 500
X 2012 900 1000
Y 2010 300 400
Y 2011 800 700
Y 2012 1200 1100
[/tt]
This is the result from the existing tables on the 3 sheets, that you wanted in the table Z sheet. Whenever any changes are made to any of these tables, simple REFRESH the querytable on the table Z sheet and your data will be current.

Skip,

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

 

[jpol]

Thank u for help. I am testing this one later because here I have only Excel 2000. Suppose that in the Excel 2010 a Structured Tables is availables also?

I've got some macro code. But 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 table Z etc. every years. When I want put 2014, It is something wrong (loop doesn't work)

Could u look on this cod of macro aaa?
Here is Link
Till 2013 is ok. How to do a loop for more years 2014, 2015 etc.? What should I added in code?

2) After used macro the formula bar (table Z) is lost. Formula bar shows a source date. Is it possible that the formula showed after used macro?

Regards. Thank you in advance for your answer.

 

[SkipVought]

1) Did you read the FAQ?

2) for Excel '95-2003, simply follow the specs for a table

Then the query method will work--NO VBA REQUIRED!

Skip,

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

 

[jpol]

Thank u very much for query method. I've been demonstrating VBA just for added value to this thread.