Formula to copy data in one sheet to another

[Mike]

Sheet1 'Transaction Database' stores all transactions by column from row 5
to row 59.
Sheet1 Cell Row 6 Column C, D, E, F, G etc. is the type of
transaction--Open, Pending, Closed, Withdrawn, Expired.
Then I have a seperate sheet for each titled--Open, Pending, Closed,
Withdrawn, Expired.

I have entered this formula in each sheet--Open, Pending, Closed, Withdrawn,
Expired-- to move the type of transaction from sheet1 'Transaction Database'
to the appropriate sheet:
=IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"")
The only thing that changes is "Open" Pending" "Closed" "Withdrawn"
"Expired"...

My problem is, the type of transaction is copied to the appropriate sheet
but in the same column as it is in sheet1. I need the transaction to copy
to the first available column from column C on in each sheet.

Is this possible???

 

[Eduardo]

Hi Mike,
try

'Copy information in the new Tab
Worksheets(Your Sheet).Select
Set ws1 = Worksheets(Your Sheet)

'find first empty row in database starting column C
With ws1
iRow1 = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row

 

[Mike]

Hi Eduardo,

Sorry, I don't understand your response...

The formula I included in my question is in the 5 seperate sheets titled
Open, Pending, Closed, Withdrawn, Expired. They all pull the files from the
files in Sheet1 titled 'Transaction Database'. But, lets say the file in
sheet 1 column F5:59 has Open in F5 the the formula in the 'Open' sheet pulls
the data into the 'Open' sheet, column F5:59 and leaves C and D blank.
I need it to go to column C or the first available column.

Sorry if I don't understand or if I am not explaining myself properly...

 

[Eduardo]

Hi Mike,
Sorry I misunderstood you, just a question when you say you have entered the
formula, where have you enter it, is that a formula in a cell or a code, if
that is part of a code could you please post your code

 

[Mike]

Hi Eduardo,

The Formula is:
=IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"")
I enter it in cell C5 and copy/paste down the column to C59 then copy/paste
C5:C59 to all columns...
The formula in each sheet is changed for "Open to Pending, Closed,
Withdrawn, Expired for each coresponding sheet

 

[Eduardo]

Hi Mike, I don't understand something,
1) Sheet 1 is named Transaction Database??,
2) if Data starts in C5, as per below why sheet1 cell c6 = open
3) could you send an example just a few # to make sense, thank you

 

[Mike]

Hi Eduardo,
Sheet1 is named 'Transaction Database...
Data starts in C5.
C5 is the transaction number
C6 is the status of a transaction Open,Pending,Closed,Withdrawn or Expired.
C7 is Sellers Name
C8 is Buyers Name
C9 is Listing Date
C10 is Agents Name
etc...
Column D would be say Transaction #2
Column E would be Transaction #3
and so on...

Hope this helps!

 

[Eduardo]

Hi Mike,
you mention before that in F5 you might have Open, do you have the status in
two different columns or in both of them C and F

 

[Mike]

No, Row 5 is allways the Transaction Number. Row 6 is always the Status of
the transaction which is either Open,Pending,Closed,Withdrawn,Expired. Each
transaction file is entered in a column (C,D,E,F,G,H,I,J,K,l and so on) in
sheet1 "Transaction Data Base" The status of each file (Which is always in
row 6 column (C,D,E,F,G,H,I,J,K,l and so on) changes to either
Open,Pending,Closed,Withdrawn,Expired The formula in each of the other 5
sheets idenifies the status of each transaction and pulls it into the
approproate sheet Open,Pending,Closed,Withdrawn,Expired