Execl Help Closed

Question

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.


Configuration: Windows XP / Internet Explorer 8.0

venkat1926 · Answer

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

BJ · Answer

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.

venkat1926 · Answer

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

venkat1926 · Answer

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.

bj · Answer

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,

Execl Help

5 responses

Subscribe To Our Newsletter!