Snasui Excel Forum

Posted: **Mon Nov 16, 2020 12:56 pm**

Hi Dear

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

Thanks

Posted: **Mon Nov 16, 2020 1:06 pm**

Herewith attached

Posted: **Mon Nov 16, 2020 8:46 pm**

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

Posted: **Mon Nov 16, 2020 9:25 pm**

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

Posted: **Mon Nov 16, 2020 10:22 pm**

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

Posted: **Mon Nov 16, 2020 11:20 pm**

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

Posted: **Tue Nov 17, 2020 12:19 am**

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

Posted: **Tue Nov 17, 2020 9:26 am**

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,

Posted: **Tue Nov 17, 2020 9:54 am**

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

Posted: **Tue Nov 17, 2020 7:51 pm**

sna wrote: Tue Nov 17, 2020 9:26 am 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

Posted: **Tue Nov 17, 2020 9:01 pm**

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

Posted: **Tue Nov 17, 2020 9:14 pm**

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

Posted: **Tue Nov 17, 2020 9:50 pm**

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

Posted: **Tue Nov 17, 2020 9:54 pm**

Here is an attachment

Posted: **Wed Nov 18, 2020 9:51 am**

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

Posted: **Wed Nov 18, 2020 10:47 pm**

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

Posted: **Wed Nov 18, 2020 10:48 pm**

Here is a new update template

Posted: **Thu Nov 19, 2020 8:11 pm**

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.

Posted: **Thu Nov 19, 2020 8:32 pm**

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

Snasui Excel Forum

JV

JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV

Re: JV