BA code to copy multiple data in one worksheet leaving blank cells in to other worksheet

Hi ripan81
_ you have explained quite well what you want.
_ But for specific help here with getting a code for you, you would need to give us some more detail and test data to work on , reduced greatly in amount . Macros tend to repeat or loop and do stuff. So if there is some logic / pattern to your many tables or sheets, then we only need to see a few and any code we do for you would normally work then with little or no modification on much bigger files.
_ So We need to see what you have initially before anything is done ( again with reduced test data )
_ we call this typically the “Before” or “Befores”). But then importantly we then want to see hand filled result, which is what everything should then look like after running of the macro. We would call this the “After or Afters.
_ We need a clear picture ( Not IMAGES ) of what you have before and what you want after with reduced test data.

. So simply provide a clear before and after ... ( For example a "Before" and "After" sheet in an Excel File )

To summarise.

. The Before or Befores, should look just as it / they does before running of any macro.
.
. The After or Afters, should be hand filled by you so that it they / looks exactly as you want it to After running of any macro, based on the actual sample data in the Before / or Befores
. Make sure there is just enough data to demonstrate your need. Remember to desensitize the data if necessary, but use the typical Format type and choose your data carefully so that it can be used to test all possible scenarios.
...

Alan

P.s. some ways to provide that info: ( Most people use . 2 )
. 1 ) use the Forum Tools in my signature to produce screen shots we can copy to a spreadsheet ( NO IMAGES ! )
. 2 ) Post Files
To Attach a sample workbook:
View Pic
http://www.excelforum.com/members/da...ch-a-file.html
http://tinyurl.com/oenwprw

. 3 ) - Up left in the Thread editor is a Table icon. Click that, create an appropriately sized table and fill it in. (To get the table icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)

. 4) Only as a very last resort, P.M. me and i will reply with my Email Addressee so you can send me a file
** To PM me, click on my name in the left hand margin when you are logged in, the rest should be obvious.

Hi Ripan,
_ I think at first glance it seems OK
_ I will make as start Today or Tomorrow.
_ In the meantime can you do the same again with an even more reduced row size. I could just chop rows off myself, but it is better that you do that as you know the whole thing well and can make sure the data test all possible scenarios. That is all that is important.
_ It is a lot easier for me to write the code and check it for smaller data. There should be no problem then with much bigger data, but we could take it in steps, trying then the file I have now, then going up in size etc.
_ for now just about 40 rows in data sheet would be good

Alan

Hi

Originally Posted by ripan81

...
As regards reduced row size it will be really difficult for me. .....

_ OK, No problem. I have done an initial code which seems to be working initially with your File from Post #3.
_ I need to just check it and tidy it up a bit. I should get time later to do that.
_ Then I will post again
_ Alan

Hi Ripan,

_ OK, Here we go:

_ This was Not to difficult technically this one, just a bit of careful work to select the correct data to select and copy etc..

_ A few notes / assumptions I have made:

_ For no particular reason i determine the number of Tables to be considered from the Number of tables in the Output sheet. So AT LEAST as many tables as that must be in the Data Sheet, ( and in the same order. )

_ I have assumed the different Data Tables are all the same row size, ( but this can easily be changed to select the actual row size if it is different )

_ Assume Date column J ( Column 10 ) gives indication of last used row when formula returns “”

_ I assume you have the tables you want already there, all formatted, bit just with no data in Output sheet and only data is required .

_ Blank rows are determined by when Cash Flow Date has no date in ( Can also easily be modified to AND / OR check other columns )

_ You may need to do some adjusting of your sheet Formatting to get Date Formats etc Finally correct. It is difficult for me to predict what you may get as my Excel ( German ) has different default settings.

_ So I have a Code for you . It appears to work correctly.

_ To Summarise: For a small part of Table 2

_ Input given from you

Using Excel 2007

Row\Col	AC	AD	AE	AF	AG
12	Financial Year	Month	Cash Flow Date	Cash Flow Amt.	Cumulative Principal
13	FY 2013-14	Aug -13	26.Aug.13	-262,300,000	285,000,000
14	FY 2013-14	Aug -13			285,000,000
15	FY 2013-14	Aug -13			285,000,000
16	FY 2013-14	Aug -13			285,000,000
17	FY 2013-14	Aug -13			285,000,000
18	FY 2013-14	Aug -13	31.Aug.13	890,137	285,000,000
19	FY 2013-14	Sep -13			285,000,000
20	FY 2013-14	Sep -13			285,000,000

Data Sheet

_ ........
_ Working on the above, my code , given here:
http://www.excelforum.com/developmen...ml#post4201063

_ .... will return this ( Again just showing a small part of table 2 )

Using Excel 2007

Row\Col	K	L	M	N
12	Financial Year	Month	Cash Flow Date	Cash Flow Amt.
13	FY 2013-14	13.Aug.15	26.08.2013	-262,300,000
14	FY 2013-14	13.Aug.15	31.08.2013	890,137
15	FY 2013-14	13.Sep.15	10.09.2013	-200,000,000
16	FY 2013-14	13.Sep.15	13.09.2013	-215,000,000
17	FY 2013-14	13.Sep.15	18.09.2013	-300,000,000

Output Sheet

_ I recommend you try to apply the code to reduced test data first. Once successful, I would increase the Number of tables and row size in steps , just in case any unforeseen problems occur when dealing with lots of Data.

_ Please let me know how you get on.

Alan

Hi Ripan,
I am away from my Excel Computer just now. But I will look at all that for you as soon as i can if no one else picks it up.

Just a few points for you to consider in the meantime:

1)

Originally Posted by ripan81

....
But have couple of observation in Output sheet regarding Date format. .....

see again what I said about that in post 7. As I said, That may be difficult for me to do, and you will have to experiment due to Excels annoying habit of changing formats, which is further complicated by our different Excel language versions. . But I try to see if I am able to get the VBA code to "overwrite " any VBA made changes to date / Number formats in those columns. I will try. But cannot promise anything on that one..
NOTE
1) What comes up in my excel ( what i see ) for dates may be different then what you see!! So that makes it very difficult
2) ( I expect you know that for example 1 10 2013 means the first of October 2013 to me , and may mean to you tenth of January 2013 !!!!! - Different land conventions!!! )
_.................................

___2)

Originally Posted by ripan81

........
Also if you can help how to Paste screen shot of excel tables ? Somehow I could not figure out how to use it form Signature ?

_ - have another read of my notes on doing that:
https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
_ that is basically a summary of all that I know, so try again and then tell me at what point in trying to install the ADD - IN that you have difficulty.
See also maybe here:
http://www.excelforum.com/the-water-...ml#post4109080
or here:
http://www.mrexcel.com/forum/about-b...rder-help.html


If it is a problem with installing the ADD-In , then I could give you a File with the code in for one from one of those Add-In tools. You would then run that as a normal code rather than as an Add-In.

Alan

Hi

1)
Date issues
This is what I see for the first part of Table 1 Output:

Attachment 422131

For the reasons i mentioned already you may have to experiment yourself with the formats within both Input and Output sheets. I have not yet been successful in controlling this fully.

_.....................................
2)

Originally Posted by ripan81

..........
I require one more help in the macro.
If you can edit macro to meet table requirements as per " Revised Example Data- Table size.xlsx " that will be of great help. I tried but could not succeed......

These changes should meet this new requirement:

Please Login or Register  to view this content.

_ ..........
3 )

Originally Posted by ripan81

.....can help how to Paste screen shot of excel tables ? Somehow I could not figure out how to use it form Signature ?

See my reply in Post # 9 ___2)


_................

See how you get on and report back. Then I will see if I can help further

Alan

Hi Ripan,
Please also check the Data Sheet in " Revised Example Data- Table size.xlsx "

I see this strange - YY

Ripon revised Data Sheeet.JPG

_..

What do you see

Alan

Hi Ripon,
_ I have struggled in vain to overcome the mysterious Date Format changes and differences in your sheets. I still do not understand what is going on there!
_ However, by trial and error. I have come up with a code version of my original code which appears as though it may be giving the correct results. It is a very slow code compared to my original as it involves a lot of copying and pasting. It may however be useful as an intermediate solution. NOTE I HAVE based it on the original test data , so it is BASED ON FILE: „Example -1 3 customers.xlsx‎" from Post #3

Here is the code:
http://www.excelforum.com/developmen...ml#post4204335


Alan