VBA & Macro Problems

How to Create an Excel Table with Hyperlinks Using VBA

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to create a VBA macro to generate an Excel table from existing worksheet data, preserving hyperlinks, arranging specific columns (Name, Country, Party/Faction), and automatically highlighting members of a specific group (Renew Europe).

Product: Excel
Device & OS: not provided
Scenario: Reorganizing raw worksheet data into a clean, formatted table where hyperlinks are kept intact and specific rows are highlighted based on their column value.
Observed behavior: The required VBA procedure must successfully clear previous output rows while preserving headers, copy data and hyperlinks into a specified column order, and conditionally apply a red format to rows where the Party/Faction value is 'Renew Europe'.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) to retain VBA code. It is highly recommended to prepare a sanitized worksheet with dummy data to test the macro's output before applying it to your actual dataset.

Solution 1Recommended

Use a Custom VBA Macro to Rebuild the Table and Preserve Hyperlinks

Write a specific VBA macro that loops through the source data, extracts values and hyperlinks, places them in the desired column order, and applies row highlighting.

When rearranging data with VBA, standard value transfers (like `Target.Value = Source.Value`) will drop hyperlinks. To preserve them, the macro must explicitly copy the hyperlink object along with the cell value. Additionally, applying row highlights requires evaluating each row's data during the generation loop.

Open the VBA Editor

Press 'Alt + F11' to open the Visual Basic for Applications (VBA) Editor in your spreadsheet program.

Insert a New Module

Click 'Insert' > 'Module' to create a blank script window where you can paste your macro.

Clear Old Result Rows

Start your procedure by clearing previous table outputs while preserving headers. Use a command like `Range("A2:D1000").Clear` (adjust the range to match your destination area).

Copy Data and Hyperlinks

Write a loop to iterate over your source data. Use the `Range.Copy` method to transfer data to the destination cells for the Name, Country, and Party/Faction columns. This native copy method ensures hyperlinks are carried over automatically.

Apply Conditional Formatting to Rows

Add an `If` statement inside your loop that checks if the Party/Faction column equals "Renew Europe". If true, apply formatting to the target row using `TargetRow.Interior.Color = vbRed`.

Run the Macro

Press 'F5' to run the macro, or return to your worksheet and assign the macro to a shape or button to run it easily.

Test with Dummy Data: When developing complex VBA scripts for restructuring data, starting with a sanitized sample workbook containing dummy data ensures you do not accidentally overwrite your live data if the code loops incorrectly.

Solution 2

Restructure Data Using Dynamic Formulas and Simple VBA

Use modern spreadsheet formulas to quickly arrange columns, then use a simpler macro just to re-attach hyperlinks.

Advanced Spreadsheets with WPS Office

Easily Manage Data and Macros in WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA macros (in the professional version) and offers powerful built-in tools to manage, reshape, and conditionally format your data intuitively.

1. Open Data in WPS Spreadsheet: Download and install WPS Office, then open your raw dataset in WPS Spreadsheet.
2. Access the Developer Tools: Navigate to the 'Developer' tab and click 'Macros' or 'Visual Basic' to insert and run your table-generation script.
3. Leverage Built-In Tools: Use the built-in Conditional Formatting feature under the 'Home' tab to visually highlight 'Renew Europe' members effortlessly if you prefer not to hardcode formatting into your macro.

Full compatibility with Microsoft Excel .xlsm and .xlsx formatsNative support for VBA macros to automate complex data tasksIntuitive conditional formatting tools for quick highlighting without codeLightweight application that runs smoothly on Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

Why do my hyperlinks disappear when I copy data with VBA?

If your VBA code assigns values using `Destination.Value = Source.Value`, only the plain text is transferred. To preserve hyperlinks, you must either use the `Source.Copy Destination` method or explicitly recreate the link using `Hyperlinks.Add` on the destination cell.

How do I highlight an entire row based on a cell value in VBA?

You can reference the entire row range of your target destination and change its interior color property. For example, `Range(Cells(r, 1), Cells(r, 3)).Interior.Color = vbRed` will highlight the first three columns of row `r`.

Can I arrange columns dynamically without using VBA?

Yes, you can use dynamic array formulas like `CHOOSECOLS` and `WRAPROWS` in modern spreadsheet applications to reshape your data. However, formulas alone cannot dynamically carry over clickable hyperlinks based on source cell objects; VBA or manual copying is required for that specific task.

Excel & Data Problems