Excel Macro: Use VBA to Copy Column Data Into Rows

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.

Problem Scenario

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.

worksheet

Solution

Step#1: Create a button called “Save” on Sheet1  (see the how to instruction )

button

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.

Debug

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.

Advertisements
This entry was posted in Excel, Programming, Tool and Debug, VB and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s