Execl Help

Closed
Report
-
 bj -
Hello,

I'm a newbie in Excel Macro & VB, am an accountant I made new programe but I have a problem I need to copy row from sheet 1 to sheet 2 based on cell for example:

sheet one= Journal Entries include all transaction happened in the copmany
sheet two= General ledger include cell with the name of Account( cell no. E4

I need when cell E4 change, I want to look in sheet 1 all the rows that containe the same account (E4) & copy all the row that containe date, description ,Dr.Cr. amount all the row on sheet 2 (General Ledger)

please any one can help me on that.


5 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
the full requirement of your question is not clear. It would have been better if some small extract of the data had been posted.

anyhow try this.
open vb editor (alt + F11)
hit control+R
on the left "project window" you see all the open excel files
go to your file and right click sheet1 (suppose your primary data is there)
click view code
in the window that comes up copy this event code (if necessary modify the code)


Private Sub Worksheet_Change(ByVal Target As Range)
Dim x, cfind As Range, dest As Range, add As String
If Target.Address <> "$E$4" Then Exit Sub
x = Target.Value
Set cfind = Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then
MsgBox "this account is not available"
Exit Sub
Else
add = cfind.Address
cfind.EntireRow.Copy
Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End If
Do
Set cfind = Cells.FindNext(cfind)
If cfind Is Nothing Then Exit Do
If cfind.Address = add Then Exit Do
cfind.EntireRow.Copy
Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
Loop
End Sub
Ok I'll try that one.

I have five sheets in One Excel ok.

my question is in sheet 3 & 4.

sheet 3 name Journal Entries
sheet 4 name General Ledger

on sheet 3 I put all transaction happend in company

Transaction No. Date Dr. Cr. Account Name Account No.Acoount #Description

12 01/01/2011 12 Cashier 11100 withdraw
12 01/01/2011 12 Samba 99 11210 withdraw for cleaner

so I want in sheet for when I choose the account name for example cashier I want to look in sheet three all transaction for cashier and past all the row.

its clear now.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
still some points not clarified. in which sheet in cell E4 the account name is given. Obviously it is in sheet 4 because sheet 3 has the transaction data. then sheet 3 data for cashier is copied in sheet4. if more rows with cashier are there they also will be copied. test.

if this is so I slightly modified the event code.
open vb editor (alt+F11). hit control+e
in the project window go to this file and right click sheet4 and click view code and copy the following event code.
and save the file.
now go to sheet 4 and in cell E4 type a account name and enter e.g. cashier
be careful about spelling an no unnecessary spaces.

see what happens in sheet 4

the event code is

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x, cfind As Range, dest As Range, add As String
If Target.Address <> "$E$4" Then Exit Sub
x = Target.Value
With Worksheets("sheet3")
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then
MsgBox "this account is not available"
Exit Sub
Else
add = cfind.Address
cfind.EntireRow.Copy
Worksheets("sheet4").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Offset(0, -4).PasteSpecial
End If
Do
Set cfind = .Cells.FindNext(cfind)
If cfind Is Nothing Then Exit Do
If cfind.Address = add Then Exit Do
cfind.EntireRow.Copy
Worksheets("sheet4").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Offset(0, -4).PasteSpecial
Loop
End With
End Sub
yes E4 in sheet 3

I copy the code but there is an error massage(Block if without end if)
there is a question did the name of sheet affect the code ? meen sheet 3 = Journa Entries
in code should I write sheet 3 or Journal Entries??

please help me :(
dear venkate
I will make it more easier for you:
sheet 3 name=Journal Entries include Table starting from cell B7 as following:

B7=Transaction, C7=Date, D7= Dibet Amount, E7= Credit Amount, F7= Account Name, G7= Account Number, H7= Description.

sheet 4= Generl Ledger include table starting from cell C8 before that table in cell D4 there is a down list menue that I can choose the account name from it.

C8= Transaction, D8= Date, E8=Descreption, F8=Debit Amount, G8=Credit Amount


so what I wnat in shee4 which is General Ledger when I choose from down List menue e.g. Cashier ( Account Name) I wnat to copy paste all the data for Cashier in sheet 3 which is Journal Entries to the table in sheet 4 useing Macro & VB.

I hope now the information is Clear

Please help me its urgent

thanks alot,
BJ
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
try replacing sheet3 and sheet 4 in the EVENT CODE BY THE CORRECT NAMES


If there is problem send a very small extract of your sheet3 and sheet 4 withtheir corresponding names in the tab. you can upload your truncated file in speedyshare.com

remove pass word.
dear venkate
I will make it more easier for you:
sheet 3 name=Journal Entries include Table starting from cell B7 as following:

B7=Transaction, C7=Date, D7= Dibet Amount, E7= Credit Amount, F7= Account Name, G7= Account Number, H7= Description.

sheet 4= Generl Ledger include table starting from cell C8 before that table in cell D4 there is a down list menue that I can choose the account name from it.

C8= Transaction, D8= Date, E8=Descreption, F8=Debit Amount, G8=Credit Amount


so what I wnat in shee4 which is General Ledger when I choose from down List menue e.g. Cashier ( Account Name) I wnat to copy paste all the data for Cashier in sheet 3 which is Journal Entries to the table in sheet 4 useing Macro & VB.

I hope now the information is Clear

Please help me its urgent

thanks alot,