Transfer cells range to another sheet if first cell of destination range is empty

[kopapa]

Howdy all!

I've got a project which is quite complex (at least for me). So, I'm taking it one step at a time
The first step is creating a macro evoked by a push button (which is already in place) which will achieve the following:

Sheet1 is the source worksheet
Sheet2 is the destination sheet

With the push of the macro button (present in Sheet1) I want to calculate formulas in specific cells (B4, C4, B6, L61) AND
copy the cells' values to specific cells in Sheet2 (M10, N10, O10, S10) BUT only if the FIRST cell of this range (i.e. M10) is empty. If it contains
data then paste the values to the cells below (M11, N11, O11, S11) etc etc. The "check for empty cell" routine is necessary because the macro will be evoked

continuously (push the button on Sheet1 - Calculate - Transfer to empty cells on Sheet2 - Loop)

Any help will be greatly appreciated!
Thanks!

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

It assumes you already have data in M9 on sheet2.

[kopapa]

Well, I think it does!
I put it in, I checked and everything seems to be OK.
So many thanks for your precious help!
First step is (more or less) gone... 99 to go!

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. In accordance with Forum Rule No. 9, please mark this thread as solved. Also, good luck on the other 99.

[kopapa]

You're welcome. Glad to help out and thanks for the feedback. In accordance with Forum Rule No. 9, please mark this thread as solved. Also, good luck on the other 99.

Hmm, I think I rushed a little bit.
I seemed to work OK BUT when I try to clear the destination cells (either manually or by code) so that I can start over, it seems to glitch...
It starts again in M10, copies the first two cells (B4 and C4) to M10 & N10 and ignores the other two (B6 and L61)...
Any thoughts?

[JOHN H. DAVIS]

Can you provide a sample?

[kopapa]

Can you provide a sample?

Ok, this is a sample xlsm... I changed it a little bit and translated most of the info in case you needed to understand sth.

Data entered in "PRICES" and transfered to "TOTALS" with the help of the macro you suplied.

The problem is (as mentioned in a previous message) when I clear data from "TOTALS" I cannot repeat the process... (only two cells transfer correctly).

You'll see.

Hope you can help.
Thanks.

[JOHN H. DAVIS]

In range O and S on Sheets("TOTAL") the last row with values is a merged cell. In order for the macro to function properly you would have to redesign those cells (don't know if that would be a problem for you). I did and it seemed to work.

[kopapa]

In range O and S on Sheets("TOTAL") the last row with values is a merged cell. In order for the macro to function properly you would have to redesign those cells (don't know if that would be a problem for you). I did and it seemed to work.

This does seem to do the trick! Yes, I do have to redesign the cells but still it's a small price to pay...
Thank you very much man. Greatly appreciated!

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.


9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

[kopapa]

Ok, this appeared to have been solved (see above posts) until a problem occured (out of nowhere)

PRICES2013_B.xlsm


In the attached excel file, macro searches for the first empy row based on column M and then copies specific cells
from another worksheet (PRICES) to cells in TOTALS.
BUT, I figured out that macro searches for the first empty row but fills in the first empty cells with the transferred values, not the first empty row!

For example,
In worksheet PRICES we enter a period (1.1.13-1.2.13) and a blank consumption (not zero) and ALL 4 values (B4, C4, B6 & L46) are copied to the first empty row (M10) in "TOTALS" worksheet (actually B6 is just blank).
If we go back to "PRICES" and enter another period (1.2.13-1.3.13) but this time enter a consumption then the first two values (B4 & C4) and the 4th value (L46) are copied in the same row but 3rd value (B6) is copied to the first empty cell of "O" column which is of the previous row.

This is a problem. I would like for the macro to search for the first empty row (based on "M") and copy ALL cell values to THAT row even if there are some empty cells in other columns.

This is really needed in the file, not a luxury...

So any ideas how the below code must be changed?

Please Login or Register  to view this content.

Thanks in advance for your feedback!

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[kopapa]

My mistake.
Your solution is up to the point. But it's not what I want.

See attached file to understand.

PRICES2013_C.xlsm

Let's assume I have two source worksheets. PRICES (where B4, C4, B6, B10 & L46) should be copied and PRICES2 (where there are less cells to be copied; B4, C4, B6 & L46).

If I transfer values from PRICES2 and then from PRICES, cell B10 (from PRICES) gets copied to the first row (R10) and not its own row (R11)...

My description of the problem was misleading. Your solution was ok.

Any ideas?

(Many thanks for your patience)

[JOHN H. DAVIS]

I would have to know how many different variations your Source Sheets may have? Or you could do something like Activesheet. And then include all the variations in one macro. Don't know if that helps.

Please Login or Register  to view this content.

[kopapa]

Unfortunately this will not work. There will be many variations in Source Sheets so there is probably no way that one macro will cover them. For example Β6 value in one sheet will copy to O in TOTALS but B6 in another sheet will have to copy to R in TOTALS etc.
Isn't there a way to force rest of cell values to copy to the same row with the B4 and/or C4 value? These two values are stable (always present)

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[kopapa]

Sorry, I don't get it. Isn't this the same with your previous post?
Is it different?

Besides, I think that cell values B4 & C4 will always be <> ""
I was thinking about the condition that when B4 & C4 find the first empty row & are transferred then all the rest cell values (B6, B10, L46 etc) should occupy the same row (in the appropriate cells of course)...

Maybe:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[kopapa]

Again the same code! Ok.

For anyone interested the solution was setting the destination row into a variable and using that. Pick a column that you know will always be populated.
Thanks goes to Sam Ward for his valuable advice & solution.

Please Login or Register  to view this content.