How to create VBA to copy specific column from one sheet to another
Category: [Excel] Demo Available
How to create VBA to copy specific column from one sheet to another
Requirement:
The user has a workbook with sheets transaction and check.
The user need to copy column of H of transaction sheet to M of check sheet and column I transaction sheet to K check sheet based on header 5 of transaction sheet.
All data column are dynamic .
The format is always the same.
The issue is that are blank cells in both column to be copied.
Solution:
Try the attached, click the button on "TRANSACTION MERGER" sheet to run the macros
In Worksheet Object Module for "TRANSACTION MERGER" sheet
Code:
Sub btnCopy_Click()
CopyColumns
End Sub
and in a standard module
Code:
Sub CopyColumns()
Dim x, y, z, IDs, i As Long, ii As Long
x = Sheets("TRANSACTION MERGER").Cells(1).CurrentRegion
IDs = Sheets("CHECK").Cells(1).CurrentRegion.Columns(5)
ReDim y(1 To UBound(x, 1) - 1, 1 To 1): ReDim z(1 To UBound(x, 1) - 1, 1 To 1)
For i = 2 To UBound(x, 1)
ii = Application.Match(x(i, 5), IDs, 0)
y(i - 1, 1) = x(i, 8): z(i - 1, 1) = x(i, 9)
Next
With Sheets("CHECK")
.[m2].Resize(UBound(y, 1)) = y
.[i2].Resize(UBound(z, 1)) = z
End With
MsgBox "Columns successfully copied", , "Completed"
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 generate report based on criteria |
| How to use Excel VBA code to hide based on criteria |
| How to protect VBA source code from view |
| How to use VBA to turn columns into rows |
| How to turn one operation into a loop in VBA |
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.