Forum Discussion
Anan Srivastava
Apr 30, 2018Copper Contributor
Excel to copy specific rows and paste in a specific manner
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 valuesSet 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
No RepliesBe the first to reply