Excel VBA Macro to Copy and Transpose Data Across Worksheets
Question details
The user needs an Excel VBA macro that transposes data from multiple company worksheets into their matching reformatted worksheets.

- 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.
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.
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.
Open the workbook in desktop Excel, then select Developer > Visual Basic. Use Insert > Module to create a standard module.
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
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.
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.

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.

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.




