NEED TO CORRECT THIS CODE

W

wndncg

Board Regular
Joined
Mar 24, 2017
Messages
84
Office Version
  1. 2019
  2. 2016
  3. 2013
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
 
Sort by date Sort by votes
I have not changed your code much but try this:
It is working on the basis of having the sheet and cell selected which I would advise against.

VBA Code: 
Sub DATA_DATE_LAST_OP()

    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
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Alex Blakenburg said:
I have not changed your code much but try this:
It is working on the basis of having the sheet and cell selected which I would advise against.

VBA Code: 
Sub DATA_DATE_LAST_OP()

    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
Click to expand...
sir the idea was it will pull out the data from template then it will paste to the current row (example is A17) on FIN_sheet. the code giving me "we cant do that on a merge cell"
 
Upvote 0
That kind of proves my point about relying on the user selecting a cell for where to put the output.
You should only get that error message when the Activecell is the heading row, which is where you have your merged cells (which by the way is a really bad idea).

Try selecting a cell in your data body range below the heading.

Assuming that works, then consider how you can get the macro to decide an output destination rather than have the user select a cell.
 
Upvote 0
Alex Blakenburg said:
That kind of proves my point about relying on the user selecting a cell for where to put the output.
You should only get that error message when the Activecell is the heading row, which is where you have your merged cells (which by the way is a really bad idea).

Try selecting a cell in your data body range below the heading.

Assuming that works, then consider how you can get the macro to decide an output destination rather than have the user select a cell.
Click to expand...
did you download my file sir? i did not stated there i already make a macro where after the user pressed the a button it will go to the last date (A) and based on (B) then it will the script Sub DATA_DATE_LAST_OP()

sorry for long post
 
Upvote 0
Check what the active cell is on the sheet FIN after running the 1st macro and before running the Date_Last macro.

You can do this visually and also by when it errors out and goes into debug mode copy the below including the "?" into the immediate window and hit enter.
(Ctrl+G if the window is not visible)
VBA Code: 
? Activecell.Address
 
Upvote 0
Alex Blakenburg said:
Check what the active cell is on the sheet FIN after running the 1st macro and before running the Date_Last macro.

You can do this visually and also by when it errors out and goes into debug mode copy the below including the "?" into the immediate window and hit enter.
(Ctrl+G if the window is not visible)
VBA Code: 
? Activecell.Address
Click to expand...
still getting errors :(, is it not possible to copy the data on TEMPLATE to FIN?
 
Upvote 0
You can definitely copy the data.
Try manually selecting an cell in the sheet FIN on any row below row 5 (your last heading row) and then run the DATA_DATE_LAST_OP() macro.
Then tell if that worked. If it didn't show me the error message and also tell me which line was highlighted when you hit the debug button.

If you are using a button try running it directly from VBA.
 
Upvote 0
Alex Blakenburg said:
You can definitely copy the data.
Try manually selecting an cell in the sheet FIN on any row below row 5 (your last heading row) and then run the DATA_DATE_LAST_OP() macro.
Then tell if that worked. If it didn't show me the error message and also tell me which line was highlighted when you hit the debug button.

If you are using a button try running it directly from VBA.
Click to expand...
assigned it to a macro key it does run but no data added.
 
Upvote 0
Do you want to share another copy of your workbook that has the code and your button ?
Also tell me exactly the steps you do when you run it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
VBA EXCEL TO ADD DATA BASED ON A CELL DATA AND DATE DATA
Replies
3
Views
715
mohadin
M
W
  • Question Question
UPDATING EXCEL SHEET BASED ON CELL DATA
Replies
1
Views
474
wndncg
W
C
  • Solved
VBA - Copy and paste a range of values N times
Replies
6
Views
439
cydmm
C
S
  • Solved
VBA - User defined type not defined error
Replies
4
Views
595
ssh99
S
G
  • Solved
Skip Row When Column Word Meet
2
Replies
14
Views
389
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top