copy filtered data / paste to another sheet but modified

excel01noob

Board Regular
Joined
Aug 5, 2019
Messages
93
Office Version
  1. 365
  2. 2016
Using data from source Worksheet to filter as per defined criteria I want after to copy the column of accounts IDs (column A) and paste them into the destination worksheet but with a slight different format

In the account IDs I check the length: if equal to 4 digits such as 1234, copy the account and paste in destination column D as F1234, if more than 4 digits as 4592482, copy and paste as X4592482
How to achieve this?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
You can try this. You'll need to replace the sheet names with the sheet you're copying from and the destination sheet, but it should do what you want.

VBA Code:
Option Explicit

Sub CopyRangeOnCriteria()
    Dim i As Long
    Dim lastRow As Long
    
    lastRow = Cells.Find(What:="*", _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).row
    
    For i = 1 To lastRow
        If Len(Cells(i, 1).Value) = 4 Then
            Worksheets("Sheet2").Range("D" & i).Value = "F" & Worksheets("Sheet1").Cells(i, 1).Value
        ElseIf Len(Cells(i, 1).Value) > 4 Then
            Worksheets("Sheet2").Range("D" & i).Value = "X" & Worksheets("Sheet1").Cells(i, 1).Value
        End If
    Next i
End Sub
 
Upvote 0

Your solution works but I forgot to mention the other important criteria.
I have column B in the source WS which defines the sales type into Local, Regional, International

I filter this column first with "International" and only after I would use the code to copy/paste with the account format stated above.
So I am copy/pasting only the visible accounts based on the filtered criteria

How would be the modified code?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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