In excel I have a sheet named "sales", and another one named "factor".

In factor I have some fields for factor ID, customer name, product code, quantities and price.

I want when I add this data to factors it automaticly generate a new row in sales including this data. I don't know if I am clear or not.

For example in factor I type:

101 , mr johnson , m11 , 10 , 6600$

and in sales I want them to be saved.

How can I do this?


=IF(Factor!A1<>"";Factor!A1;"") in A1 (or wherever you wish to have the table / copy start) in the "Sales" sheet will display a duplicate of the A1 cell on the "Factor" sheet.
Copy this to fill an entire row as far as you need, then copy the row for any number of rows you need.

I don't know if that is the same as "Saved" for you.

Another way is to generate (record) a VBA macro:
Start recording a macro, select the A1 cell in the Factors sheet as first step, then set "Relative references" and press End immediately followed by Cursor down (this will move the cell selection to the last row that has data in the block of data starting at A1).

Then press End again, HOLD SHIFT and hit Cursor right and all data on that row, until the last empty cell will get selected.

Now do a Copy and paste to the "Sales"-sheet with the keyboard.
CTRL+C copies.
CTRL+PgDn switches to the next sheet.
Repeat the keyboard sequence above, to move the selection point to the end of the present data, move down one row; then do
CTRL+V does the paste.
Optionally do CTRL+PgUp to go back to the previous sheet.
End macro recording.

Adapt to your needs.

  • tnx bro that really helped:) – Hadi Aug 1 '15 at 14:44
  • and one more thing, is it possible that if i erase data from sheet factor, the data in sheet sales remain?( in first case) – Hadi Aug 1 '15 at 16:13
  • BTW, the second way you offer was exactly what i looking for :) i wish you good luck bro – Hadi Aug 1 '15 at 16:25
  • In the first case the data is available ONLY on the "Factors" sheet. The "Sales" sheet contains only formulas - which pick out and display the same data. – Hannu Aug 1 '15 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.