urgent help please in macro, VBClosed

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) thanks lot,,,,

<config>Windows XP / Internet Explorer >

venkat1926 · Answer

It is not clear where in sheeet1 the account numbers are entered.  I assume it is column A.  If it is different column you modify the macro the line

With Worksheets("sheet1").Columns("A:A")

the sheet names are sheet1 and sheet 2 and not sheetone and sheettwo.


KEEP YOUR ORIGINAL FILE SAFELY SOMEWHERE SO THAT THE FILE CAN BE RETRIEVED IF THERE IS A MESS UP. 

THE MACRO IS

Sub test()
Dim j, cfind As Range, add As String
With Worksheets("sheet2")
j = .Range("E4").Value
End With
With Worksheets("sheet1").Columns("A:A")
Set cfind = .Cells.Find(what:=j, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.EntireRow.Copy
add = cfind.Address
Else
Exit Sub
End If
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
Do
Set cfind = .Cells.FindNext(cfind)
If cfind Is Nothing Then Exit Do
If cfind.Address = add Then Exit Do
cfind.EntireRow.Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
Loop
End With

Application.CutCopyMode = False
End Sub

Urgent help please in macro, VB

1 response

Similar discussions

Subscribe To Our Newsletter!