How to use VBA code to copy ActiveRow cells to another sheet
Category: [Excel] Demo Available
How to use VBA code to copy ActiveRow cells to another sheet
Requirement:
Code:
Sub MyCopy()
Dim myRow As Long
myRow = ActiveCell.Row
Sheets(1).Cells(myRow, 2).Copy Sheets(2).Range("X1")
Sheets(1).Cells(myRow, 3).Copy Sheets(2).Range("C8")
Sheets(1).Cells(myRow, 4).Copy Sheets(2).Range("H8")
Sheets(1).Cells(myRow, 5).Copy Sheets(2).Range("Q8")
Sheets(1).Cells(myRow, 11).Copy Sheets(2).Range("G9")
End Sub
The user has tried running this code above but it did work.
1. When the code was first run the code it prompted: CANNOT CHANGE PART OF A MERGED CELLS.
So the user unmerged the merged cells in the destination sheet.
2. When the user ran the code again, it did work but the problem this time is THE DESTINATION CELLS IN ANOTHER SHEET ADJUSTED ITS CELL HEIGHT THEREBY DISTORTING THE LAYOUT OF THE DESTINATION SHEET.
Could anyone help the user to fix this without destroying the layout of the destination sheet?
Is there a way to paste the source value to destination with MERGED cells?
Is there a way to paste the source value to destination with UNMERGED cells without affecting the row height and column width of the destination?
Solution:
Code:
Option Explicit
Sub MyCopy()
Dim lRow As Long, ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets(1): Set ws2 = Sheets(2)
lRow = ActiveCell.Row
With ws2
.[x1] = ws1.Cells(lRow, 2)
.[c8] = ws1.Cells(lRow, 3)
.[h8] = ws1.Cells(lRow, 4)
.[q8] = ws1.Cells(lRow, 5)
.[g9] = ws1.Cells(lRow, 11)
.Activate
End With
End Sub
You could also have the code update the form automatically. All you need to do is double click any cell in a students Row and the form will update with that students data.
The code in the Worksheet Object Module for the StudentsFrofile (I assume that should really be "StudentsProfile"!) sheet is
Code:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lRow As Long, ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets(1): Set ws2 = Sheets(2)
lRow = Target.Row
Application.Goto ws1.[a1]
With ws2
.[x1] = ws1.Cells(lRow, 2)
.[c8] = ws1.Cells(lRow, 3)
.[h8] = ws1.Cells(lRow, 4)
.[q8] = ws1.Cells(lRow, 5)
.[g9] = ws1.Cells(lRow, 11)
.Activate
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 paste a cell value to the active cell |
| How to search for a word inside a workbook and open that sheet as active sheet |
| How to find and replace based on list entered by user input |
| How to create VBA code to increment number each time a copy is printed |
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.