Complete data of a master table without overwriting given data

[KaiA]

Hi all,

I try to achieve the following:




I have one master table in a worksheet
And an identical table in another worksheet
If a cell in the master sheet table is empty, the code should check the corresponding cell in the other worksheet table
If the corresponding cell contains a value, this value should be copied to the empty cell of the master sheet table
This should be repeated for all cells in the master sheet table
It is important that given values in the master sheet are not being overwritten.
Is it possible to set the active sheet as the sheet that contains the master table and to always copy the data from the worksheet tab next to it (to the left or right, does not matter). I would like to avoid to always update worksheet names in the VBA code.


Many thanks,
Kai

[Bob Phillips]

How do you know whether previous or next sheet if there are both?

[p45cal]

Assuming:

you want to copy from the next visible sheet tab to the right
the tables share the same location on each sheet
that the cells on the master sheet are truly empty

then something along the lines of:
Sub blah()
For Each cll In Range("A2:D9").SpecialCells(xlCellTypeBlanks).Cells 'adjust range to suit
cll.Value = ActiveSheet.Next.Range(cll.Address).Value
Next cll
End Sub
I suspect the real life scenario is more complicated, so to get a better response, attach a file as close to your real life scenario as you can.

[KaiA]

How do I attach a file here?

[p45cal]

http://www.vbaexpress.com/forum/faq.php?faq=vb3_reading_posting#faq_vb3_attachments

[KaiA]

Yes, thanks I had seen that:

"How do I attach a file to a post?To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread."

I cannot see my post under "New Post". I cannot find a "New Thread" page. I can find my post in the VBA code forum. But there is no "post reply" button. I can click on my post which leads me here again.

[KaiA]

Hi all,

Finally, I found out how to attach a file!

Please note:

all tables are identical
when I start the macro I would like that it checks the data in the sheet "Table1" to the left or to the right (does not matter) an copy the data to the master sheet without overwriting any given data in the master sheet
some of the cells in the master sheet contain values already, these values should not be overwritten
the target is to retrieve data for every empty cell in the master sheet
the table in the sheet "Table1" next to the master sheet will contain formulas which retrieve data from different sources
so once the "Table1" sheet retrieved data from one source, this data should be copied (just the data, not the formulas) to the master when I activate the macro, but without overwriting the existing data
then I will change the formulas in "Table1" so that new data is retrieved from a new data source
then I will hit the macro button again, in order to copy the new data to the master again, but only filling in the data there is the corresponding cell is empty


I hope that this is clearer.

Many thanks for your effort!

Best,
Kai

[p45cal]

This:
Sub blah()
For Each cll In Range("A3:G26").SpecialCells(xlCellTypeBlanks).Cells 'adjust range to suit
cll.Value = ActiveSheet.Next.Range(cll.Address).Value
Next cll
End Sub
seems to work in the attached. Make sure the sheet you're copying from is immediately to the right of the sheet you're copying to, and that the sheet you're copying to is the active sheet.

[KaiA]

Thanks p45cal,

it indeed works in the example on my private pc.

But somehow, when I try to run the script in another excel file or from another tab it shows the error "no cells found".

[p45cal]

which means they're not truly blank/empty.
Post a small sample file with a few cells of the master sheet from the area concerned (delete all other sheets, delete most of the contents of the master sheet, but retain a few cells from the table which are apparently empty which let me see what's actually in those cells. (They aren't formulae are they?)

You can begin to see why I said:
as close to your real life scenario as you can.

[KaiA]

Hi p45cal,

thanks, I will do that next week when I return to the office. Actually, the cells were empty, as I deleted the contents. But anyways.

Best
Kai

[KaiA]

Hi p45cal,

After I deleted all data in the Master Sheet and started to proceed with retrieving new data from different sources into the Master sheet from scratch it worked!

So, once again - thank you very much! I think, it is a very neat and elegant code.

How is the procedure now? Is there a way how I can "upvote" or "rate" your help?

I suppose, I should mark the thread as solved now, right?

Best regards,
Kai

[p45cal]

Just marking it as solved is enough.