Extract Data into a Summarized form

[Ricky Pang]

Hello Experts,
There are 6 steps needed in my question of how to pull data into a new
journal entry [Summary] tab:
1) How do you skip through the "" (blanks) and the zeros within column
[M], and extract each of the values? I have named this range as
[Values], current tab name is [Depreciation].

For each of the value that is extracted, on the corresponding row;
2) Go to column E (Debit account), copy and paste to the Summary tab.
3) Go to column F (Credit account), copy and paste to the Summary tab.
4) Go to column A (Description), copy and paste to the Summary tab.

5) In the Summary tab, inverse the extracted value from Step 3 to
represent a Credit entry.
6) Leave a blank row; then repeat all steps until no further values are
available.

The end result of the Summary tab would look like this:
Letters represent the Column. Numbers represent the steps above.

Starting at Row 5
(A) 51100 [step 2],(B) insert this function to Lookup the account code's
description
[=INDEX(Accounts!$A$1:$C$777,MATCH(A5,Accounts!$A$1:$A$777,0),3)]
(G) 20,000 [step 1]

Row 6
(A) 18720 [step 3],(B) insert this function to Lookup the account code's
description
[=INDEX(Accounts!$A$1:$C$777,MATCH(A6,Accounts!$A$1:$A$777,0),3)]
(G) -20,000 [step 5]

Row 7
(B) Building Depreciation for 10 years [step 4]

Row 9
(Blank Row in between). Loop all steps until no more values found within
[Depreciation] tab's [Values] range.


Your help is great appreciated.

Thanks in advance,
Ricky

 

[Ricky Pang]

Hello again,
I think it's better to rephrase and go about this one step at a time. I
have Debit account codes in Column E, Credit account codes in Column F,
$values in Column M.

How do you go down the list of Column M and pick each value (skipping
the "" Blanks and zeros) of Column M, extract the Debit account code in
E and the $value in M within that row; paste it to a new tab? Then,
extract the Account code in Column F and the same $value in M; paste it
to the new tab, next row?

Keep looping until no further values in column M.

I'd really appreciate your help.

Thanks so much,
Ricky

 

[Ricky Pang]

Hello,
So far, this code can hide the rows containing "0" value and blanks
within column M. However, how do you copy each Value and its
corresponding row data in column E and paste to a new tab? Next, copy
the same Value (but inverse the sign this time) and copy the row data in
column F to the new tab's next line? Insert 2 blank rows.

How do you continue to loop this copy and paste procedure until no
further values exist within the "Values" range of column M?

Dim C As Range

For Each C In Range("Values")
If Trim(C.Value) = "0" Or Trim(C.Value) = "" Then Rows(C.Row).Hidden
= True _
Else Rows(C.Row).Hidden = False
Next

Your help is greatly appreciated.

Thanks,
Ricky

 

[Ricky Pang]

Hello Experts,
I can't seem to build on top of this existing code to summarized the
data into a new tab. Any takers?

Thanks in advance,
Ricky