VBA & Macro Problems

How to Copy Column Data to a Row Based on a Matching Date with Excel VBA

Chanuka Geekiyanage Oct 20, 2026 869 views

Question details

The user needs to find a specific date in a storage worksheet and automatically copy a vertical range of data from a selector sheet horizontally into the row matching that date.

How to Copy Column Data to a Row Based on a Matching Date with Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Automating data entry by copying vertical cell values (e.g., D4:D8) from an input sheet to a horizontal record in a master storage sheet based on a date criteria.
Observed behavior: The user is looking for a VBA script to validate an input date, locate the date in a specific column, and automatically transpose the target data to the correct destination cells.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) to allow VBA code execution, and verify that the sheet names "Selector" and "Storage" exist in your file.

Solution 1Recommended

Use a VBA Macro with the Application.Transpose Function

Create and assign a macro that searches for a valid date and automatically transposes vertical column data horizontally into the matched row.

This VBA script first checks if the input cell contains a recognized date. It then searches the master sheet for this exact date.

If the date is found, the macro uses the Application.Transpose method to flip the vertical data from your source column into a horizontal row directly adjacent to the matched date cell.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.

Insert a New Module

From the top menu, click Insert > Module to create a blank workspace for your code.

Paste the Transpose VBA Code

In the new module, paste the following code: Sub FileAway() Dim ws As Worksheet Dim wt As Worksheet Dim d As Date Dim c As Range Set ws = Worksheets("Selector") If Not IsDate(ws.Range("D3").Value) Then MsgBox "Please enter a valid date in D3", vbExclamation Exit Sub End If d = ws.Range("D3").Value Set wt = Worksheets("Storage") Set c = wt.Range("B:B").Find(What:=d, LookAt:=xlWhole) If c Is Nothing Then MsgBox "Date not found on Storage", vbExclamation Exit Sub End If c.Offset(0, 1).Resize(1, 5).Value = Application.Transpose(ws.Range("D4:D8").Value) End Sub

Assign Macro to a Button

Go back to your Excel worksheet. Go to Insert > Shapes and draw a shape (like a rectangle) to act as a button. Right-click the shape, select 'Assign Macro', choose 'FileAway' from the list, and click OK.

Use a VBA Macro with the Application.Transpose Function

Customizing the Range: If you have more or fewer than 5 rows of data to copy, ensure you adjust both the source range (e.g., 'D4:D10') and the Resize parameters (e.g., Resize(1, 7)) in the code to match the new item count.

Seamless VBA Execution

Automate Workflows with WPS Spreadsheet Macros

WPS Office Spreadsheet fully supports VBA macros. You can run complex automation scripts like transposing column data to rows based on date matching without rewriting your existing Excel code.

1. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the data.
2. Access the Developer Tools: Navigate to the Tools tab on the top ribbon and click on Macro to access your existing scripts.
3. Use the Visual Basic Editor: Click on 'Visual Basic Editor' (or press ALT + F11) to paste or review the VBA code provided in the solution.
4. Run your Macro: Assign the macro to a form control button in your WPS sheet to execute the data transpose seamlessly.

100% compatible with Microsoft Excel .xlsm and .xlsb macro formatsBuilt-in Visual Basic Editor for writing, testing, and debugging macrosLightweight application with fast execution speeds for heavy data manipulationFree to download and use for all your daily office needs

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a 'Date not found on Storage' error even though the date exists?

This error usually occurs if the date formatting in the 'Storage' sheet does not perfectly match the 'Selector' sheet, or if the cell is stored as a text string instead of a true date value. Ensure both cells are formatted as Dates.

How do I modify the VBA code to transpose more than 5 rows of data?

To transpose a larger range, change the range reference in the script (e.g., from 'D4:D8' to 'D4:D10') and update the '.Resize(1, 5)' section to match the new count of items (e.g., '.Resize(1, 7)').

Can I trigger this macro automatically when a date is entered?

Yes. Instead of using a button, you can use the Worksheet_Change event within the VBA editor for the 'Selector' sheet. This allows the script to monitor cell D3 and execute the transpose automatically when the cell value is updated.

What does Application.Transpose do in Excel VBA?

Application.Transpose is a built-in method in VBA used to convert a vertical array or range of cells (a column) into a horizontal array (a row), or vice versa, without needing to loop through each cell individually.

Excel & Data Problems