NEED TO CORRECT THIS CODE

[wndncg]

Basically it adds the data where it based on cell A1 & E1

What i want:

currently the code adds the data on the end of the cell, what i want to add on the current cell:

---

Sub DATA_DATE_LAST()

Dim wsData As Worksheet
Dim wsTemp As Worksheet
Dim v As Variant
Dim LR As Long
Dim r As Long

Application.ScreenUpdating = False

' Set worksheet variables
Set wsData = Sheets("TEMPLATE")
Set wsTemp = Sheets("FIN")

' Capture value to filter on
v = wsData.Range("A1")
dd = wsData.Range("E1")

' First clear range on TEMPLATE_SHEET
' wsTemp.Activate
' Rows("4:" & Rows.Count).Delete

' Find last row on DATA_SHEET
wsData.Activate
LR = Cells(Rows.Count, "B").End(xlUp).Row

' Loop through all rows on DATA_SHEET
For r = 1 To LR
' Check value in column A
If Cells(r, "B") = v And Cells(r, "A") = dd Then
' Copy columns B-D to TEMPLATE_SHEET
Range(Cells(r, "A"), Cells(r, "D")).Copy wsTemp.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Range(Cells(r, "E"), Cells(r, "K")).Copy wsTemp.Cells(Rows.Count, "N").End(xlUp).Offset(1, 0)

Range(Cells(r, "L"), Cells(r, "M")).Copy wsTemp.Cells(Rows.Count, "X").End(xlUp).Offset(1, 0)
End If
Next r
'Call ADD_SEQ_01
Application.Wait (Now + TimeValue("00:00:01"))
'Call ADD_SEQ_02
Application.ScreenUpdating = True
wsTemp.Activate
MsgBox "ADDED_-WNDNCG"
End Sub

 

[Alex Blakenburg]

In that file, I can see 3 buttons. None of the buttons point directly to the macro "DATA_DATE_LAST".
The only Sub that calls it has the Call statement commented out.

Can you clarify how you are running the macro ?

Sub RESET_INSERT()
    Range("A6").Select
    Application.SendKeys ("^{DOWN}")
    Application.SendKeys ("{DOWN}")
    Application.Wait (Now + TimeValue("00:00:02"))
    'Call DATA_DATE_LAST
End Sub

 

[Alex Blakenburg]

The code I gave you is not even in that workbook.

 

[Alex Blakenburg]

Although having separate subs for discrete components is generally good practice, relying on Active sheets and Active cells in the process is not.
I don't think the below is good practice it is just adjusting your code to make it work.

Replace your RESET_INSERT module with the one below.
And your DATA_DATE_LAST with the one below that.

I have commented out your Send Keys in the below and put in the alternative line to use.

Sub RESET_INSERT()
    Range("A6").End(xlDown).Offset(1).Select
    'Application.SendKeys ("^{DOWN}")
    'Application.SendKeys ("{DOWN}")
    
    Application.Wait (Now + TimeValue("00:00:02"))
    Call DATA_DATE_LAST
End Sub

Same as what I gave you before but I can't see it anywhere in your workbook.

Sub DATA_DATE_LAST()
    Dim wsData As Worksheet
    Dim wsTemp As Worksheet
    Dim v As Variant
    Dim LR As Long
    Dim r As Long
    Dim dd As Date
    Dim rowOutTemp As Long
    
    Application.ScreenUpdating = False
    
'   Set worksheet variables
    Set wsData = Sheets("TEMPLATE")
    Set wsTemp = Sheets("FIN")
    rowOutTemp = ActiveCell.Row                     ' XXX request was to output to Current Cell
    
'   Capture value to filter on
    v = wsData.Range("A1")
    dd = wsData.Range("E1")

'   First clear range on TEMPLATE_SHEET
'   wsTemp.Activate
'   Rows("4:" & Rows.Count).Delete
    
'   Find last row on DATA_SHEET
    ' wsData.Activate                               ' XXX remove activation of sheet
    

    LR = wsData.Cells(Rows.Count, "B").End(xlUp).Row
        
    '   Loop through all rows on DATA_SHEET
        For r = 1 To LR
            With wsData
        '       Check value in column A
                If .Cells(r, "B") = v And .Cells(r, "A") = dd Then
        '           Copy columns B-D to TEMPLATE_SHEET
                    .Range(.Cells(r, "A"), .Cells(r, "D")).Copy wsTemp.Cells(rowOutTemp, "A")
                    .Range(.Cells(r, "E"), .Cells(r, "K")).Copy wsTemp.Cells(rowOutTemp, "N")
                    .Range(.Cells(r, "L"), .Cells(r, "M")).Copy wsTemp.Cells(rowOutTemp, "X")
                    rowOutTemp = rowOutTemp + 1
                End If
            End With
        Next r

    'Call ADD_SEQ_01
    Application.Wait (Now + TimeValue("00:00:01"))
    'Call ADD_SEQ_02
    Application.ScreenUpdating = True
    wsTemp.Activate
    MsgBox "ADDED_-WNDNCG"
End Sub

 

[wndncg]

In that file, I can see 3 buttons. None of the buttons point directly to the macro "DATA_DATE_LAST".
The only Sub that calls it has the Call statement commented out.

Can you clarify how you are running the macro ?

Sub RESET_INSERT()
    Range("A6").Select
    Application.SendKeys ("^{DOWN}")
    Application.SendKeys ("{DOWN}")
    Application.Wait (Now + TimeValue("00:00:02"))
    'Call DATA_DATE_LAST
End Sub

This is the sequence sir:

User click insert DM > macro runs by find the data A1 & E1 then it will go to FIN_Sheet then will find the last data will insert rows depends on what the user inputs, then it will paste Sub DATA_DATE_LAST() thats why i need DATA_DATE_LAST() to paste on the current cell sample is A17 so that the data will not go beyond any rows.

 

[Alex Blakenburg]

I figured that out. Did you replace the 2 subs per my previous post and rerun it ?

 

[wndncg]

yes and reassigned it to the third button, what i did i just test the Sub DATA_DATE_LAST_OP() on FIN_sheet to a blank A17 line and nothing happens.

I figured that out. Did you replace the 2 subs per my previous post and rerun it ?

 

[Alex Blakenburg]

Unless the spreadsheet you are testing on is not the one you put in the link, you have not done the replacements as specified,

Get the file you put on the shared platform.
The the only changes you make are to fully replace the 2 Subs
• RESET_INSERT
• DATA_DATE_LAST
with the code in Post #23

Do not change the button or anything else.

Then retest.

In my test I used 17 as the insertion point an 10 for the number of rows to insert.

 

[wndncg]

Unless the spreadsheet you are testing on is not the one you put in the link, you have not done the replacements as specified,

Get the file you put on the shared platform.
The the only changes you make are to fully replace the 2 Subs
• RESET_INSERT
• DATA_DATE_LAST
with the code in Post #23

Do not change the button or anything else.

Then retest.

In my test I used 17 as the insertion point an 10 for the number of rows to insert.

first of all ty for the effort sir. why not ill just edit my request a bit..

1. User press INSERT_DM (the 3rd button)
2. Macro runs copy the data just like in this code:
.Range(.Cells(r, "A"), .Cells(r, "D")).Copy wsTemp.Cells(rowOutTemp, "A")
.Range(.Cells(r, "E"), .Cells(r, "K")).Copy wsTemp.Cells(rowOutTemp, "N")
.Range(.Cells(r, "L"), .Cells(r, "M")).Copy wsTemp.Cells(rowOutTemp, "X")

3. it will paste on FIN_Sheet

 

[Alex Blakenburg]

I am just a bit unclear on whether your last post indicates whether it is working for you now or not.
If not what it is doing and what is it not doing ?
Can you please clarify.

 

[wndncg]

I am just a bit unclear on whether your last post indicates whether it is working for you now or not.
If not what it is doing and what is it not doing ?
Can you please clarify.

not working atm.