PDA

View Full Version : Solved: How to cut one sheet's values and paste it in other sheet?



snoopies
06-11-2005, 08:21 PM
Hi all,

I have a problem here.. pls also refer to attached file for reference.

There are 2 sheets. What I have to do is to cut the codes in code sheet, and paste it to another worksheet (in col B).

I will skip writing the code if the cell in col A is empty. And I have to make sure to cut the codes (not copy the codes), to prevent using the codes next time.(p.s I didn't cut the codes in attached example). Thus, I have another problem here, the starting position of codes (to be cut) is different every time, I don't know how to deal it with VBA...

Pls help... thx a lot.. :dunno

rbrhodes
06-12-2005, 12:17 AM
Hi,

Sorry to say your example is ???

- Cell B1 starts with a circular reference =B$1&A$1.

- Other cells reference blank rows for no discernable reason...

Did you want to cut a choosable range of 'codes' from Sheet "code" and paste them into sheet3, skipping any blank rows in Col A?

Cheers,

dr

Brandtrock
06-12-2005, 04:12 AM
Cross posted. (http://www.ozgrid.com/forum/showthread.php?p=174804#post174804)

Regards,

snoopies
06-12-2005, 07:21 AM
sorry, I'm a new joiner, really don't know this rule...I cannot delete the thread in other forum, could Brandtrock help me, or advise me where to delete..?

sorry...

snoopies
06-12-2005, 07:29 AM
Hello,

sorry, I made a mistake.. I forgot to change the formula to values in column B...

Yes... just like what you said.. any suggestions?

thx!

Brandtrock
06-12-2005, 01:31 PM
First, you need not delete either thread, just indicate that you have cross posted a question when that is the case. The reason for this is two fold:


Persons helping find a solution will not duplicate efforts.
Persons reading the thread in the forum where the solution is ultimately arrived at will see how it got solved, readers in any non solution forums will not, indicating where it is posted allows those interested to get all of the information.
Now, on to the question you have asked, as I see it:


The only condition for a code to be cut and pasted from the code sheet to Sheet3 is that A1 has something in it.
The codes in the code sheet are to only be used one time.

A few questions for you:

Do the codes have to be on a separate sheet and cut and pasted?
Or, could the codes be created at run time and then inserted in column B when column A has something in it?
This should be fairly simple to get accomplished.

Regards,

snoopies
06-12-2005, 03:47 PM
Hi, pls refer to below:

The only condition for a code to be cut and pasted from the code sheet to Sheet3 is that A1 has something in it. Yes!
The codes in the code sheet are to only be used one time. Yes!
Do the codes have to be on a separate sheet and cut and pasted? Yes!
Or, could the codes be created at run time and then inserted in column B when column A has something in it? Only yes if I know how to do it..
Regards,
snoopies

Brandtrock
06-12-2005, 04:36 PM
Your codes are in this format:

4 characters, from 0 to Z (0,1,2,3,4,5,6,7,8,9,A,...,Z)

In your example, you start with 031L. Is this where the codes will start? Or should they begin at 0000?

Do you want the new codes to appear after each entry is made in column A?

Or

Do you want the new codes to be input after a button or control is clicked/checked?

Or

Do you want the new codes to be input when the user saves or closes the workbook?

Sorry to ask so many questions, but I like to get an understanding of what the final solution should do before attempting to code it. Hope you understand.

Regards,

snoopies
06-13-2005, 07:38 AM
hello, I'm so glad to answer following questions...


4 characters, from 0 to Z (0,1,2,3,4,5,6,7,8,9,A,...,Z)

In your example, you start with 031L. Is this where the codes will start? Or should they begin at 0000?

It depends. :( Sometimes, I may use a new sheet which will start at 0000.. However, I'm required to use the existing (old) spreadsheet in some situations.. There is no standard, which varies in different jobs. ( In this example, the codes before 031L have all been used and cut out to avoid any duplications, and I will cut the new codes from this spreadsheet).

I want to write a marco which can fit to both cases, which can copy and cut from the first non-blank cell (exclude entire column A and row 1).

Do you want the new codes to appear after each entry is made in column A?

Or

Do you want the new codes to be input after a button or control is clicked/checked?

Absolutely Yes!

Or

Do you want the new codes to be input when the user saves or closes the workbook?

Sorry to ask so many questions, but I like to get an understanding of what the final solution should do before attempting to code it. Hope you understand.

Regards

snoopies
06-15-2005, 04:18 PM
Could anyone help? pls..

Brandtrock
06-16-2005, 12:06 AM
Sorry I got sidetracked. Here is one way to do this. Hope it works like you want it to.

Regards,

snoopies
06-16-2005, 08:39 AM
It's Great! Thanks you so much!
thx thx thx thx thx !!!!:clap:

Brandtrock
06-16-2005, 02:33 PM
You are of course, very

:welcome: