Trouble when using Paste to last line

[PVA0724]

Hi

The context, I have worksheet Costa Rica where I select data based on a multiple criteria (array) and copy it to workshet March, I was able to built in a code that partially works...because what I need is that the paste is done up to final active cell on column A of Worshet March on every copy and not replacin existing data.

So, basically I need to replace the ActiveSheet.Paste line (the only one I was able to work with) and I already tried ActiveCell.Offset but it didn't work...any other idea is more than welcome.

Any help kindly appreciated

Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim ArrayCo As Variant
Dim I As Integer
 
 
 
'Start search in row 4
LSearchRow = 3
'Start copying data to row 2 in Month (row counter variable)
LCopyToRow = 2
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
 
ArrayCo = Array("GL60", "GL62", "GL67", "GL69", "GL72", "GL75", "GL85", "GL86", "GL87", "GL88", "EBILLING60")
For I = 0 To UBound(ArrayCo)
 
'If value in column A = ArrayCo and Invoice Coding And PO Redistribution, copy entire row to Sheet2
If Range("A" & CStr(LSearchRow)).Value = ArrayCo(I) And _
Range("H" & CStr(LSearchRow)).Value = "Invoice Coding" Or _
Range("A" & CStr(LSearchRow)).Value = ArrayCo(I) And _
Range("H" & CStr(LSearchRow)).Value = "PO Redistribution" Then
 
'Select row in Costa Rica to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy
'Paste row into March in next row
Sheets("March").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste
 
'Move counter to next row
LCopyToRow = LCopyToRow + 1
'Go back to Costa Rica to continue searching
Sheets(" Costa Rica").Select
End If
LSearchRow = LSearchRow + 1
 
Next I
Wend
'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select
MsgBox "All matching data has been copied."
Exit Sub
 
End Sub

 

[Warship]

replace:

'Select row in Costa Rica to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy
'Paste row into March in next row
Sheets("March").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

with:

Sheets(" Costa Rica").Rows(LSearchRow).Copy Sheets("March").Cells(LCopyToRow, 1)

Also just a note: Should there be the "space" preceeding the Sheet name Costa Rica?

~~~~~~~~~~~~~~

OR "Selection.Paste" instead of "ActiveSheet.Paste"

 

[PVA0724]

Hi Warship

Thanks for the reply...I modified the coded as follow, as you can see I added the line in order to replace the part of the code as you mention, but into March sheet was copied but again it's overwriting the lines rather than paste it to to the last active line based on column A. So I added

.End(xlUp).Offset(1, 0)

After your suggestion, but it didn't work either...I know that is the trickie part of the code so any help will be more than welcome. Thanks

PS: Selection.Paste didn't work

Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim ArrayCo As Variant
Dim I As Integer


On Error GoTo Err_Execute
'Start search in row 4
LSearchRow = 3
'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2
While Len(Range("A" & CStr(LSearchRow)).Value) > 0

ArrayCo = Array("GL60", "GL62", "GL67", "GL69", "GL72", "GL75", "GL85", "GL86", "GL87", "GL88", "EBILLING60")
For I = 0 To UBound(ArrayCo)

'If value in column A = ArrayCo and Invoice Coding And PO Redistribution, copy entire row to Sheet2
If Range("A" & CStr(LSearchRow)).Value = ArrayCo(I) And _
Range("H" & CStr(LSearchRow)).Value = "Invoice Coding" Or _
Range("A" & CStr(LSearchRow)).Value = ArrayCo(I) And _
Range("H" & CStr(LSearchRow)).Value = "PO Redistribution" Then


Sheets("Costa Rica").Rows(LSearchRow).Copy Sheets("March").Cells(LCopyToRow, 1).End(xlUp).Offset(1, 0)

'Move counter to next row
LCopyToRow = LCopyToRow + 1
'Go back to Costa Rica to continue searching
Sheets("Costa Rica").Select
End If
LSearchRow = LSearchRow + 1

Next I
Wend
'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub

 

[PVA0724]

Does anyone to help me with this please...

 

[PVA0724]

Solved: Trouble when using Paste to last line

Thanks for the help I was able to resolve my problem with the following code

Sub SearchForString()
    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    Dim ArrayCo As Variant
    Dim I As Integer
 
         
    'On Error GoTo Err_Execute
    'Start search in row 4
    LSearchRow = 3
    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 2
    While Len(Range("A" & CStr(LSearchRow)).Value) > 0
    
    ArrayCo = Array("GL60", "GL62", "GL67", "GL69", "GL72", "GL75", "GL85", "GL86", "GL87", "GL88", "EBILLING60")
    For I = 0 To UBound(ArrayCo)
    
        'If value in column A = ArrayCo and Invoice Coding And PO Redistribution, copy entire row to Sheet2
        If Range("A" & CStr(LSearchRow)).Value = ArrayCo(I) And _
        Range("H" & CStr(LSearchRow)).Value = "Invoice Coding" Or _
        Range("A" & CStr(LSearchRow)).Value = ArrayCo(I) And _
        Range("H" & CStr(LSearchRow)).Value = "PO Redistribution" Then
            
        'Select Copy from Doxis Costa Rica
        
        Sheets("Costa Rica").Select
        Range(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Copy
        
        'Select Destination to Paste
        
        Sheets("March").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
        
            'Move counter to next row
            LCopyToRow = LCopyToRow + 1
            'Go back to  Costa Rica to continue searching
            Sheets("Costa Rica").Select
        End If
        LSearchRow = LSearchRow + 1
    
    Next I
    Wend
    'Position on cell A3
    Application.CutCopyMode = False
    Range("A3").Select
    MsgBox "All matching data has been copied."
    Exit Sub
'Err_Execute:
    'MsgBox "An error occurred."
End Sub

Hope this may help someone.