Excel to copy specific rows and paste in a specific manner

Copper Contributor

PP.PNG

 

My requirement is

  • If Internal Asset ID (Column B) is unique, Copy row regardless selected or not (Column F).

  • If Internal Asset ID not unique i.e. same Internal Asset ID is present column B more than once, see against which Internal Asset ID is Column F marked as selected & then copy only that row.

  • Copied rows are: 3rd row,5th row, 7th, 8th, 9th row"

This data is in Workbook1:Sheet1 and I have to copy it to Workbook2:Sheet2 The mapping of copy & paste has to be like mentioned below - 

 

WB1:Sheet1 A to WB2:Sheet2 A
WB1:Sheet1 B to WB2:Sheet2 B
WB1:Sheet1 N to WB2:Sheet2 C
WB1:Sheet1 X to WB2:Sheet2 D
WB1:Sheet1 Y to WB2:Sheet2 E
WB1:Sheet1 AY to WB2:Sheet2 G
WB1:Sheet1 C to WB2:Sheet2 H
WB1:Sheet1 D to WB2:Sheet2 I
WB1:Sheet1 E to WB2:Sheet2 J
WB1:Sheet1 F to WB2:Sheet2 K
WB1:Sheet1 BI to WB2:Sheet2 R
WB1:Sheet1 AT to WB2:Sheet2 S
WB1:Sheet1 AU to WB2:Sheet2 T
WB1:Sheet1 AV to WB2:Sheet2 U
WB1:Sheet1 AW to WB2:Sheet2 V

 The pasting in Workbook2:Sheet2 has to start from "A12"

 

My attempt -

 

Sub cpyCol()
Dim wc As Worksheet, wa As Worksheet
Dim lr As Long, I As Long, J As Long
Dim uR As Range
Dim eNumStorage() As String ' initial storage array to take values

Set ws = Sheets("Test")
'Set wa = Sheets("Test")
lRow = ws.Range("A" & Rows.Count).End(xlUp).Row

Const fRow As Long = 3
Application.ScreenUpdating = False
For I = 3 To lRow 'sheets all have headers that are 2 rows
If Not (Application.WorksheetFunction.CountIf(ws.Range("B" & fRow, "B" & lRow), ws.Range("B" & I)) > 1 And _
Application.WorksheetFunction.CountIfs(ws.Range("B" & fRow, "B" & lRow), ws.Range("B" & I), ws.Range("F" & fRow, "F" & lRow), "Selected") = 1 _
And ws.Range("F" & I) <> "Selected") Then
If (uR Is Nothing) Then
Set uR = Range(I & ":" & I)
Else
Set uR = Union(uR, Range(I & ":" & I))
End If
End If
Next I
uR.copy Destination:=ws.Range("A13")
Application.ScreenUpdating = True
End Sub

 

This code generates partial result that  I need. What I can't figure out is how to copy it from another workbook to current workbook based copy paste mapping mentioned above. Please help me improve my attempt

 

0 Replies