Avinash Singh
asked on
conditionally copy and paste the data by vba
conditionally copy and paste the data by vba
see the sample file
highlighted colour is only for understanding purpose
Book1--3-.xlsm
see the sample file
highlighted colour is only for understanding purpose
Book1--3-.xlsm
In column A of the source sheet, will the same set of rows always be present for every "group" of data? Will they always be in the same order? Or could some be in one group, but not another?
abc
def
ghi
jkl
mno
»bp
abc
def
ghi
jkl
mno
»bp
ASKER
Yes the order will be same
ASKER
Kindly see the sample file fabrice sir and see the result u will understand what i mean to say i am new to vba so i don't know the exact word that's y i mentioned conditional copy paste
Hi Expert,
This code will run once, please add or modify with some coding to do while untill "abc" founded
This code will run once, please add or modify with some coding to do while untill "abc" founded
Sub Test()
Dim Tempvalue As String
Worksheets("Sheet1").Select
ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Temp"
For Each Cell In Worksheets("Sheet1").Range("A2:A6")
Tempvalue = Cell.Value
Worksheets("Temp").Select
Cells.Select
Selection.Find(What:=Tempvalue).Offset(0, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Worksheets("Result").Select
Cells.Select
Selection.Find(What:=Tempvalue).Select
If ActiveCell.Offset(0, 1) <> "" Then
ActiveCell.End(xlToRight).Offset(0, 1).Select
ActiveCell.PasteSpecial
Else
ActiveCell.Offset(0, 1).PasteSpecial
End If
Worksheets("Temp").Select
ActiveCell.EntireRow.Delete
Next
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I looked at your file thanks.
What I see is more or less copy and happen cells to each others than conditional copy (else, I missed the condition).
Well, a possible solution can be Something like the following:
What I see is more or less copy and happen cells to each others than conditional copy (else, I missed the condition).
Well, a possible solution can be Something like the following:
Option Explicit
Public Sub test()
Dim wb As Excel.Workbook
Set wb = ThisWorkbook
Dim src As Excel.Worksheet
Set src = wb.Worksheets(1)
Dim trg As Excel.Worksheet
Set trg = wb.Worksheets(2)
happenRange trg.Range("A1"), src.Range("A2:G2"), src.Range("A9:F9"), src.Range("A16:H16"), src.Range("A23:H23")
End Sub
Private Sub happenRange(ByRef trg As Excel.Range, ParamArray srcs() As Variant)
Dim src As Excel.Range
Set src = srcs(LBound(srcs))
'// Copy the 1st cell
trg.Value = src.Cells(1).Value
Set trg = trg.Offset(columnoffset:=1)
Dim i As Integer
For i = LBound(srcs) To UBound(srcs)
Set src = srcs(i)
'// copy all cells, skipping the 1st of each range
Dim skip As Boolean
skip = True
For Each cell In src.Cells
If Not skip Then
trg.Value = cell.Value
Set trg = trg.Offset(columnoffset:=1)
End If
skip = False
Next
Next
End Sub
Note: The code only work for the 1st row, you should duplicate and adapt the line #13 to fit your needs.
ASKER
Thnx to all members for giving ur precious time to this post & Thnx Neeraj sir
You're welcome Avinash!
As it stand, I don't see any "conditional copy".
Can you explain more ?