How to create a macro for text copy and paste in different worksheets based on a variable in Excel
Category: [Excel] Demo Available
How to create a macro for text copy and paste in different worksheets based on a variable in Excel
Requirement:
Explanation:
In worksheet 1('reporting sheet') there is a archive code value that's the same as in worksheet 2('database sheet').
The text in E7 section ('text", 'reporting sheet') needs to be copied and pasted in workbook 2('database sheet') to B section, based on the archivecode in 'worksheet reporting sheet' section B7. The archivecodes are variables, so I just want to click 'Run macro', macro checks the archivecodes and copy and paste's it in the next workingsheet.
Solution:
Code:
Sub CopyText()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Sheets("Reporting sheet").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim code As Range
Dim foundCode As Range
For Each code In Sheets("Reporting sheet").Range("B7:B" & LastRow)
Set foundCode = Sheets("Database sheet").Range("A:A").Find(code, LookIn:=xlValues, lookat:=xlWhole)
If Not foundCode Is Nothing Then
foundCode.Offset(0, 1) = code.Offset(0, 3)
End If
Next code
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
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 go to the next sheet using a macro |
| How to create a macro to round total based on a cell value |
| How to input a row variable pertaining to all macros |
| How to create a macro to copy and paste in the next empty row |
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.