Copy cell data within workbook to a different worksheet

[Eville]

Hello everyone :D
I was member of VBAexpress.com but it folded so hope i get alot of usefull help here as well!
As for my question;
I have a workbook 'logsheet' were i enter data like 'coil, supplier, thickness, width' and 'lot'. This happens 7 times on one logsheet. When the logsheet is filled, a new copy is created (from a hidden empty logsheet).
I want this data to copied to a seperate sheet - eg called data - in same workbook so that it can be used in other workbooks as external data.
I want to enter a formula on the logsheet worksheets and not on the data logsheet because the amount of logsheets (and coils) is unkown and can vary each lot.
Is this possible with formulas eg copyto? (if that formula exists ><)
Is it possible to add data from worksheets that still have to be created from the empty logsheet?

Thx in advance!

Eville

Edit: adjusted title as requested. hope this is alright?

[shg]

Welcome to the forum.

Please take a few minutes to read the Forum Rules about thread titles, and then edit yours to make it descriptive of your problem.

Thanks.

[Eville]

Welcome to the forum.

Please take a few minutes to read the Forum Rules about thread titles, and then edit yours to make it descriptive of your problem.

Thanks.

Im sorry i was too enthusiastic with me 1st post lol. readed rules, i try 2 memorise them :P Hope this title is better? else let me know :D

[Cheeky Charlie]

Lol @ cute edit retaining "unnecessary" chat...

What you're suggesting is probably best done with VBA (and is probably quite simple, form what I can tell from the question). To help us, it would really help to have a couple of examples of your day-to-day and your master sheet (however they're titled), and perhaps further explanation.

HTH

PS, I would remove the friendliness from your thread title altogether - I fully appreciate the sentiment, don't get me wrong, It's just the reason for the rule is to make the forum easy and practical to search...

[Eville]

Lol @ cute edit retaining "unnecessary" chat...

What you're suggesting is probably best done with VBA (and is probably quite simple, form what I can tell from the question). To help us, it would really help to have a couple of examples of your day-to-day and your master sheet (however they're titled), and perhaps further explanation.

HTH

PS, I would remove the friendliness from your thread title altogether - I fully appreciate the sentiment, don't get me wrong, It's just the reason for the rule is to make the forum easy and practical to search...

Hi,

I added an example so u kinda get an idea what i mean. Simply click on 'nieuw logsheet' button to make a next logsheet. Idea is to get data from coil supplier etc on to the data sheet tab in a way that the data from there can be used onto other excel sheets with export/import.
Sorry for the wrong start in title again, i should have read the rules about making threats more carefull :P

Hope this attachement is more usefull & gives an idea what im looking for

Ev

[Eville]

Does any1 have a clue on a possible sollution or something please? Cheeky Charlie maybe because he stated its probably pretty simple?
Im kinda stucked here lol ><
Thx again in advance!

Ev

[Cheeky Charlie]

Hi Ev,

How about this? Not entirely sure I've got it right, but it can't be far wrong. It's neat programming (unlike the rest of the workbook contents... )

Please Login or Register  to view this content.

Not sure if you want to remove the data from the LOGSHEET or use your existing macros to replace it. Obviously, the macro I've written makes it possible to move data into the Data sheet more than once...

HTH

[Eville]

Hi Ev,

How about this? Not entirely sure I've got it right, but it can't be far wrong. It's neat programming (unlike the rest of the workbook contents... )


Not sure if you want to remove the data from the LOGSHEET or use your existing macros to replace it. Obviously, the macro I've written makes it possible to move data into the Data sheet more than once...

HTH

Its a start. I know its not neat programmed but it works lol :p
I want to keep the data on the logsheet yes. And on the data sheet i want the data show only once. I dont mind to run the macro by hand but if possible i want the data to be inserted from the logsheet to the data sheet automatically. There about 25 users who are using this sheet so it needs to be pretty user friendly thats why i like to have most things automatically.
With the macro you made every time i use it, it places the data into the data sheet over and over so i get double data there.
I had this in mind: (on the data sheet paste link the data to the logsheet like [=LOGSHEET!$B$8]) and it works well on the 1st logsheet. But my problem is when i make start making a new logsheet (with new logsheet button) that on the data sheet i cant place linked data because that logsheet doesnt exist yet. I hope 2 find a formula or vba code so i can link data i insert into the logsheet, regardless wich logsheet, that it copy that data to the next empty cell in stated column.

Hope it made sence ><

Ev :D

[Cheeky Charlie]

Well, we could rewrite the macro I provided to formulae, but this would take much longer (to write and to run) and, more to the point, doesn't stop the user adding the data twice.

We could easily put a button on the worksheet to transfer the data. The simplest way of ensuring no duplication would be to delete the logsheet contents after copy (or move the used logsheet to very hidden and add a new one)

CC

[Eville]

Well, we could rewrite the macro I provided to formulae, but this would take much longer (to write and to run) and, more to the point, doesn't stop the user adding the data twice.

We could easily put a button on the worksheet to transfer the data. The simplest way of ensuring no duplication would be to delete the logsheet contents after copy (or move the used logsheet to very hidden and add a new one)

CC

Sorry for delay but work taking much more time then expected ><
I did discuss at work tho and outcome is;
we only want the data once and a button to transfer data wont work because it needs updating instantly. Also removing or hiding the sheet is not a sollution because other departments also use the sheets. In read only mode but still using.
To make it even abit more complex; we are thinking about making the worksheet shared. So working with macro's will kinda mess up the macros i think but didnt test that yet.
Maybe its an idea to copy the inserted data to a new workbook (new file, eg called data.xls). Then in every worksheet this data can be imported again?
Or is it impossible to insert and save data into a file that isnt even open?
Im not sure how yet but i thought it was possible to check a column if a certain data already exists? (eg check data sheet column B if coil AA1 already exists. If not then place all data from that coil onto the data sheet. If the data exists then do nothing?) So many questions, but im in no rush on this one ^^

I thought it would be easier then this tbh lol ><
All i need is a "CopyDataTo" command in excel but that doesnt exist grrrr :P

Thx again in advance!

Ev

[Cheeky Charlie]

Goodness...

we only want the data once and a button to transfer data wont work because it needs updating instantly

We can do instantaneous

To make it even abit more complex; we are thinking about making the worksheet shared. So working with macro's will kinda mess up the macros i think but didnt test that yet.

I wouldn't recommend sharing the workbook, though it probably wouldn't make much difference to a macro to do what you're describing.

Maybe its an idea to copy the inserted data to a new workbook (new file, eg called data.xls). Then in every worksheet this data can be imported again?

I'm not sure why you'd suggest this.

Im not sure how yet but i thought it was possible to check a column if a certain data already exists? (eg check data sheet column B if coil AA1 already exists. If not then place all data from that coil onto the data sheet. If the data exists then do nothing?) So many questions, but im in no rush on this one ^^

Easy peasy but you need to make it more specific for me to show you how

All i need is a "CopyDataTo" command in excel but that doesnt exist grrrr :P

Yes it does; read the help:
Copy ([Destination])

CC

[Eville]

Goodness...

1. We can do instantaneous

2. I wouldn't recommend sharing the workbook, though it probably wouldn't make much difference to a macro to do what you're describing.

3. I'm not sure why you'd suggest this.

4. Easy peasy but you need to make it more specific for me to show you how

5. Yes it does; read the help:
Copy ([Destination])

CC

Hi again,
Sorry for the absence but was away for the weekend so didnt had time to answer.
I numbered ur answers/questions above and tried 2 give an answer/explanation below.

1. Automatically instant update of data to the datasheet would be nice.

2. So sod them then, make it abit easier and dont share the workbook. Its hard enough as it is already. And besides they can always open it as read only.

3. I suggest the other file because thats the whole idea behind it.
We enter data into the logsheet file, copy it to the datasheet file in the right format (so from vertical to horizontal, each data in one cell) and import that data into always the same excel sheet that is used as some kind of database for the running lot only. All data from there can be used to fill in other tables in other sheets, for example a measuring sheet that requires same data to start with.

4. Every coil name is used only once in every lot, wich makes it identically.
So if it is possible to make a little check on the coil name into the data sheet then that will prevent double data entrance on the data sheet.
The coil, supplier, width, thickness and K-L are all linked to each other.
So i can imagine that it is possible to make one single formula or vba that automatically checks and transfers the coil name and linked data (supplier etc)

5. The Copy ([Destination]) formula isnt working as i want it to be ><
Or i cant use it the way its supposted to be

Hope it helps abit again, i know i can be a pain lol.
Thx in advance, Kind regards

Ev