In this blog, I will demonstrate how to copy data from a complicated Excel spreedsheet and paste it into another worksheet with a VBA macro.
Very often, you have a complicated worksheet and you want to flat the its data into another worksheet. For example, the “Sheet1” below contains the data for a person read from a database. The ID dropdown is the identification matching the primary key in the database table. When a ID is selected, the excel can load the related data into the sheet. Sometimes, you want to edit the data locally (i.e. add Notes or change the address) for whatever reason. And you also want to save the modified data into another worksheet.
Step#1: Create a button called “Save” on Sheet1 (see the how to instruction )
Step#2: Add the following macro to the button
Sub Save() Dim SRC_SHEET As String Dim DST_SHEET As String SRC_SHEET = "Sheet1" DST_SHEET = "Sheet2" Dim entryID As Integer Dim dstRowNum As Integer Dim dstDataRange As String 'read the entryID from the src sheet Sheets(SRC_SHEET).Activate entryID = Sheets(SRC_SHEET).Range("A2").Value 'find the row matching the entryID from the dst sheet Sheets(DST_SHEET).Activate Set found = Sheets(DST_SHEET).Columns("A").Find(what:=entryID, LookIn:=xlValues, lookat:=xlWhole) dstRowNum = found.Row 'copy and paste the values dstDataRange = "B" & dstRowNum & ":" & "D" & dstRowNum Sheets(SRC_SHEET).Activate Sheets(SRC_SHEET).Range("B3:B5").Select Selection.Copy Sheets(DST_SHEET).Activate Sheets(DST_SHEET).Range(dstDataRange).Select Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True End Sub
Step#3: Test it: edit the data on sheet1 and click “Save”. You will notice the data appear on the Sheet2.
If you ever need to debug the VBA, you can simply press F8 in the Microsoft Visual Basic for Applications and it will step through the macro line by line. If the debugger is resized to make the spreadsheet visible, you should be able to see the actions on the worksheet and the data while stepping through.