How to copy the data from sheet 1 and paste to sheet 2
Category: [Excel] Demo Available
How to copy the data from sheet 1 and paste to sheet 2
Requirement:
The user has a data in sheet1 and has to copy paste the data from sheet1 to sheet2 as shown in the attachment.
Solution:
Code:
Sub ertert()
Dim x, y(), i&, j&, k&, n&
Dim nBg&, nEd&
With Sheets("Sheet1")
x = .Range("C2:G" & .Cells(Rows.Count, 3).End(xlUp).Row + 1).Value
End With
nBg = 1
With Sheets("Sheet2")
.Range("G:J").ClearContents
For i = 2 To UBound(x)
If x(i, 5) = "A" Then
nEd = i: k = 0
ReDim y(1 To nEd - nBg + 1, 1 To UBound(x, 2))
For n = nBg To nEd
k = k + 1
For j = 1 To UBound(x, 2)
y(k, j) = x(n, j)
Next j
Next n
.Cells(Rows.Count, 7).End(xlUp)(3, 1).Resize(k, UBound(x, 2)).Value = y()
If x(i + 1, 5) = Empty Then
For n = i To 1 Step -1
If x(n, 5) = Empty Then nBg = n + 1: Exit For
Next n
End If
End If
Next i
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by nilem.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy cell data to another sheet and save it automatically |
| How do I hide data in column until data is entered in columns to the left |
| How to do conditional formatting based on another cell having data |
| How to pull data from a worksheet where variables are met |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.