JV

[sna]

Hi Dear

I need to show the result as in attached file in Menu Sheet Column K To L

Thanks

[sna]

Herewith attached

[snasui]

Please provide source of data and show its result for easy to understand then attach the updated file again.

[sna]

Hi Dear
Sorry for not clear explanation.
The source data is in Sheet Menu.The data from B11:Ixx coming from Source C1:C8 data entries.the main point of this template I want to create accrued expenses tracking by inputing data in Sheet Menu including PL Code and GL Code but the PL and GL in the same line(row) but in the JV it should be line separately.it means Dr PL(exp) and Cr GL(accrued expenses) and it should be like K10:M13.in JV sheet
Debit Credit amount
61095 100
USD17223 (100)


Thanks

[snasui]

The example code is below:

Code: Select all

Dim n As Long, t As Long
With S1
    t = .Range("h" & .Rows.Count).End(xlUp).Row + 1
    .Range("b" & t).Resize(1, 6).Value = _
        Application.Transpose(.Range("c1:c6").Value)
    .Range("h" & t + 1).Value = .Range("c7").Value
    .Range("c1:c8").ClearContents
    .Range("c1").Select
End With

[sna]

Hi Dear
Thank.got it but once more I need to click button JV it will copy data to JV as in attached
I attached new sheet

Best wishes

[snasui]

Try with this code:

Code: Select all

Dim r As Range
S1.Range("g11:h1000").Copy S2.Range("a11")
S1.Range("d11:d1000").Copy S2.Range("d11")
For Each r In S2.Range("d11:d" & S2.Range("d" & S2.Rows.Count).End(xlUp).Row + 1)
    If r.Value = "" Then
        r.Value = -r.Offset(-1, 0).Value
    End If
Next r
S1.Range("e11:e1000").Copy S2.Range("e11")
For Each r In S2.Range("e11:e" & S2.Range("e" & S2.Rows.Count).End(xlUp).Row + 1)
    If r.Value = "" Then
        r.Value = r.Offset(-1, 0).Value
    End If
Next r

[sna]

Code: Select all

Range("C11").FormulaR1C1="IFERROR(VLOOKUP (IF(RC[-1]="""",RC[-2],RC[-1]),table1,2,0),"""")"
Range("C11:C1000").AutoFill Destination:=Range("C11:C1000")

Thank you so much . another way I use above code to add to your code but
In your code provided,I need to look up account name from PL or GL in Sheet Journal but don't require formula just values


Best Regards,

[sna]

Hi dear

I update a file by creating a code to copy data from S1 To S4 but it has blank rows and another one is Vlookup account name in S2.

Could you please kindly take a look?


Best Regards

[snasui]

I need to look up account name from PL or GL in Sheet Journal but don't require formula just values

Copy your code and paste as values with this line after auto fill.

Code: Select all

Range("C11:C19").value = Range("C11:C19").value

[sna]

I mean I want to copy from Sheet menu to sheet Listing base on header.in JV I need to look up account name from COA but my code not work well in Module 2 and 3

[snasui]

Please attach file with the latest code (include my code above) and explain what the correct result that you want.

[sna]

I need to copy from data in sheet Menu to Sheet Listing without blank rows base on column headers.fill account name in Sheet Journal as in Sample attached file

Thanks

[sna]

Here is an attachment

[sna]

I upload sample with expected Result and code update in Book1(3).xlsm

In Sheet Listing I need to copy data from Sheet Menu matching column headers in Sheet Listing and paste here.In Sheet JV I need to look up account name from Sheet COA by using Vlookup in vba and convert to values.

Hope everything is fine tune

Thanks

[sna]

Hi Dear ,
Now come up with issue.I have withholding tax so how do I amend code to be like in Sheet Menu and Sheet Journal.I mean as soon as I put data entry click button add it will show like Menu and I click journal button it will show as journal sheet.
please kindly help
Thank in advance

[sna]

Here is a new update template

[snasui]

Please adjust sub procedure named AddNew() by yourself first if it has any problem you can post that code and attach the latest file again.

[sna]

Now my template intuitive change.just close it
BTW thanks you so much