VBA & Macro Problems

Excel VBA Macro to Copy and Transpose Data Across Worksheets

Camila MilosovichCamila Milosovich Nov 18, 2026 868 views

Question details

The user needs an Excel VBA macro that transposes data from multiple company worksheets into their matching reformatted worksheets.

Copy and Transpose Data Across Excel Worksheets with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook contains company source sheets such as CO A and CO B, with corresponding destination sheets such as CO A REFORMAT.
Observed behavior
A recorded macro processes one company sheet but does not continue through all company sheets.
Before you start

Check that each destination sheet is named exactly like its source sheet followed by a space and REFORMAT. Identify the source range and destination starting cell for the transpose; the example below uses A1:C10 and A1 and will overwrite the corresponding destination cells.

Solution 1Recommended

Loop Through Company Worksheets and Transpose Each Range

Use a controller macro to find each source sheet and transpose its data into the matching REFORMAT sheet.

The example transposes A1:C10 into A1:J3 on each destination sheet. Change the source range and destination starting cell to match your workbook. If your recorded macro transposes several separate ranges, add a copy and PasteSpecial pair for each range.

Sheets whose names contain REFORMAT are skipped. If a matching destination is missing, the macro reports it in the VBA Immediate window and continues.

1
Open the VBA editor

Open the workbook in desktop Excel, then select Developer > Visual Basic. Use Insert > Module to create a standard module.

2
Add the controller and processing procedures

Paste this code into the module: Sub TransposeAllCompanies() Dim sourceSheet As Worksheet Dim destinationSheet As Worksheet Dim destinationName As String For Each sourceSheet In ThisWorkbook.Worksheets If InStr(1, sourceSheet.Name, "REFORMAT", vbTextCompare) = 0 Then destinationName = sourceSheet.Name & " REFORMAT" Set destinationSheet = Nothing On Error Resume Next Set destinationSheet = ThisWorkbook.Worksheets(destinationName) On Error GoTo 0 If destinationSheet Is Nothing Then Debug.Print "Destination not found: " & destinationName Else TransposeCompany sourceSheet, destinationSheet End If End If Next sourceSheet Application.CutCopyMode = False End Sub Sub TransposeCompany(ByVal sourceSheet As Worksheet, ByVal destinationSheet As Worksheet) sourceSheet.Range("A1:C10").Copy destinationSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Transpose:=True ' Optional example: apply formatting only to CO A REFORMAT. If StrComp(sourceSheet.Name, "CO A", vbTextCompare) = 0 Then destinationSheet.Range("A1").Font.Bold = True End If End Sub

3
Adjust the ranges and formatting

In the module, replace sourceSheet.Range("A1:C10") and destinationSheet.Range("A1") with the required range and destination cell. Edit or remove the optional CO A formatting example, and add any other sheet-specific changes inside the processing procedure.

4
Run the macro

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). In Excel, select Developer > Macros, choose TransposeAllCompanies, and select Run. In the VBA editor, select View > Immediate Window to check for missing destination sheets.

Loop Through Company Worksheets and Transpose Each Range
Check the destination layout: The transposed range swaps rows and columns and can overwrite existing cells. Merged cells or incompatible destination layouts can also prevent PasteSpecial from working.
Free Microsoft Office alternative

Work with Company Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easier to move existing workbooks. It does not fix Excel VBA errors; macro support and compatibility depend on your WPS edition and setup.

Open and edit company worksheets in a familiar spreadsheet interfaceCompatible with common Microsoft Excel formats, including XLSX and XLSMMove existing spreadsheets into a lightweight office suiteUse free spreadsheet tools for everyday workbook editing
microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro skip a company worksheet?

The macro skips any sheet whose name contains REFORMAT. For other sheets, check that a destination exists with the source name followed by a space and REFORMAT. In the VBA editor, select View > Immediate Window to see missing destination names.

How do I transpose more than one range per company?

In the TransposeCompany procedure, add another sourceSheet.Range(...).Copy line and a corresponding destinationSheet.Range(...).PasteSpecial line for each range. Choose destination cells that leave enough room for the transposed results.

Can I apply extra formatting only to CO A REFORMAT?

Yes. In TransposeCompany, place the formatting statements inside the If StrComp(sourceSheet.Name, "CO A", vbTextCompare) = 0 Then block. The example bolds destination cell A1; replace that line with the changes you need.

What should I check if PasteSpecial gives a syntax error?

In the VBA editor, check that the statement is on one line and uses PasteSpecial Paste:=xlPasteAll, Transpose:=True. Use Debug > Compile VBAProject to locate syntax errors, and check for missing punctuation or incorrectly typed quotes.

Why do I need to save the workbook as XLSM?

The XLSM format preserves VBA code in the workbook. Use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm), then enable macros only if you trust the workbook.