How to transfer and combine data from one sheet to another
Category: [Excel] Demo Available
How to transfer and combine data from one sheet to another
Requirement:
The user is trying to convert the data on Sheet2:
https://image.ibb.co/bZSQYQ/1.png
Into this format on Sheet 3:
https://image.ibb.co/di8onk/2.png
The user also attached a sample file.
Solution:
Click the button on Sheet 1 to update the JUN17 Table.
Code assigned to button is:
Code:
Sub UpdateConsolidated()
Dim x, y, i As Long, ii As Long
x = Sheets("Sheet1").Cells(1).CurrentRegion '// Change sheet name to suit
ReDim y(1 To UBound(x, 1) - 1, 1 To 6)
For i = 2 To UBound(x, 1)
y(i - 1, 1) = x(i, 1): y(i - 1, 2) = x(i, 3): y(i - 1, 4) = x(i, 2)
For ii = 4 To UBound(x, 2)
If x(i, ii) <> "" Then
If y(i - 1, 6) = "" Then
y(i - 1, 6) = x(i, ii) & "-" & x(1, ii)
Else
y(i - 1, 6) = y(i - 1, 6) & " / " & x(i, ii) & "-" & x(1, ii)
End If
End If
Next
Next
With Sheets("JUN17")
i = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(i, 1).Resize(UBound(y, 1), UBound(y, 2)) = y
.Rows.AutoFit
.Activate
ActiveWindow.ScrollRow = i + UBound(y, 1) - 5
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
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 use VBA code to extract rows of data meeting criteria |
| How to copy selected data and paste to the last row of another sheet |
| 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 |
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.