Link to home
Start Free TrialLog in
Avatar of Avinash Singh
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
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Hi,

As it stand, I don't see any "conditional copy".
Can you explain more ?
Avatar of Bill Prew
Bill Prew

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
Avatar of Avinash Singh

ASKER

Yes the order will be same
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

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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:
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

Open in new window

Note: The code only work for the 1st row, you should duplicate and adapt the line #13 to fit your needs.
Thnx to all members for giving ur precious time to this post & Thnx Neeraj sir
You're welcome Avinash!