Page 1 of 1
ReconcileTxn
Posted: Wed Oct 07, 2020 6:27 pm
by sna
Hi Dear
I have an issue I need to copy data from sheet Reconcile Txn to Backup Sheet but not empty.
what should I add or edit my code to do that?
Re: ReconcileTxn
Posted: Wed Oct 07, 2020 6:29 pm
by sna
Please refer to this template .I also need your help how to clear data more dynamic(Module 1 and 2)
Thanks
Re: ReconcileTxn
Posted: Wed Oct 07, 2020 8:43 pm
by snasui
The example code is below:
Code: Select all
Sub CL()
'RCL2.[A6:Y100].ClearContents
With RCL2
.Range("a6:y" & .Range("a" & .Rows.Count).End(xlUp).Row).ClearContents
End With
End Sub
Re: ReconcileTxn
Posted: Wed Oct 07, 2020 10:27 pm
by sna
thank how about copy and paste without blank rows in Module 3?
Re: ReconcileTxn
Posted: Wed Oct 07, 2020 10:52 pm
by snasui
The example code is below:
Code: Select all
With RCL1
r = [Sumproduct(--('RECONCILE TXN'!b12:b100<>""))]
.Range("b12:z12").Resize(r).Copy
RCL4.Range("a1").PasteSpecial xlPasteValues
End With
Re: ReconcileTxn
Posted: Thu Oct 08, 2020 7:55 am
by sna
Thank you
Re: ReconcileTxn
Posted: Thu Oct 08, 2020 8:52 pm
by sna
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
Range("B" & Target.Row).Resize(, 20).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End Sub
your code work fine,is it possible like this??
or we can use special cell constant?
Thank you once again
Re: ReconcileTxn
Posted: Fri Oct 09, 2020 6:28 am
by snasui
It possible to do everything with code. Try with yourself for your task, post the error and attach file to here.
Re: ReconcileTxn
Posted: Fri Oct 09, 2020 7:32 am
by sna
Thank you
Re: ReconcileTxn
Posted: Fri Oct 09, 2020 12:11 pm
by sna
I test
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
Range("B" & Target.Row).Resize(1, 20).Copy RCL4.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End Sub
it is not working !
Re: ReconcileTxn
Posted: Sat Oct 10, 2020 6:58 am
by snasui
You need to post code with code style such as post #7.
Attach the updated file that have your latest code inside and explain what you want to do and point to the expected result from your code.
Re: ReconcileTxn
Posted: Sat Oct 10, 2020 8:37 am
by sna
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
Range("B" & Target.Row).Resize(, 20).Copy RCL4.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End Sub
I need to copy from RCL1 to RCL4 without empty cells
Best wishes
Re: ReconcileTxn
Posted: Sat Oct 10, 2020 9:30 am
by snasui
snasui wrote: Sat Oct 10, 2020 6:58 am
Attach the updated file that have your latest code inside and explain what you want to do and point to the expected result from your code.
Please read my post above carefully.
Re: ReconcileTxn
Posted: Sat Oct 10, 2020 11:50 am
by sna
Here's the attached file for your reference
Thanks
Re: ReconcileTxn
Posted: Sat Oct 10, 2020 2:11 pm
by snasui
You can call 'CopyNonBlankValue' as below.
Code: Select all
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
' Range("B" & Target.Row).Resize(, 24).Copy RCL4.Cells(Rows.Count).End(xlUp).Offset(1)
Call Module3.CopyNonBlankValue
End Sub
Re: ReconcileTxn
Posted: Sat Oct 10, 2020 4:30 pm
by sna
Ok.thank will update you
