Urgent help please in macro, VB [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
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 >

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
798
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!