Panasonic Home Business > Industrial Devices > Automation Controls Top > Service & Support > Frequently Asked Questions

When displaying file data, how can I switch rows and columns?

ID:
846
Issued:
Dec 25th, 2009
Updated:
May 17th, 2013

When displaying file data, how can I switch rows and columns?

ANSWER
At the moment, the only way to do this is to first display the file data in a sheet, and then execute a macro on this data that switches rows and columns. Use "Register Auto Macro Launch" to set the macro to launch on, for example, a notification event set using "Register File Process",

1. Copy the macro of the blue part below into the module of the book that you are using. There is no need to edit it.

2. Operation methods of the macro below.

Example 1:
Switch the rows and columns in the range B2:D5 in Sheet 1, and paste them starting from B2 in Sheet2.
Call Application.Run("RowColumn_Change", "Sheet1", "B2:D5", "Sheet2", "B2")

Example 2:
Switch the rows and columns in the range B2:D5 in the active sheet, and paste them starting from cell B7 in the active sheet.
Call Application.Run("RowColumn_Change", "", "B2:D5", "", "B7")
Note: this will not work if any of the destination cell positions are inside the range of the source cells.

Example:
Switch the rows and columns of the file data is displayed in the range B2:D5 in Sheet1, and display from B7 in Sheet2.

1) Copy the macro in the blue part below into the module of the book that you are using.

2) Create a macro.
Sub test()
'When copying between different sheets, the following three lines are required because the data in Sheet1 is not updated when Sheet2 becomes active. 'This macro "PCWAYsubSheetRefreshNoMessage" can only be used with Ver1.06 and later.

If ActiveSheet.Name <> "Sheet1" Then
Call Application.Run("PCWAYsubSheetRefreshNoMessage", "Sheet1")
End If

Call Application.Run("PCWAYsubRowColumnChange", "Sheet1", "B2:D5", "Sheet2", "B7")
End Sub

3) Select the process checkbox of the "Register File Process" notification event, and register V0 as the event.

4) Select the "Register Auto Macro Launch" process checkbox, and register the trigger as V0, and the macro name as test.

End. Row to column switch process


--------------------------------------------------------

Parameter1 strFromSheet : Name of the source sheet. If "", it is the active sheet. Ex) "Sheet1"
Parameter2 strFromRange : Source switch range Ex) "B2:D10"
Parameter3 strToSheet : Name of the destination sheet. If "", it is the active sheet. Ex) "Sheet2"
Parameter4 strToCell : Start cell position in the destination sheet Ex) "B2"

Operation example 1:

Switch the rows and columns of the range B2:D5 in Sheet1, and paste from cell B2 in Sheet2
Call Application.Run("RowColumn_Change", "Sheet1", "B2:D5", "Sheet2", "B2")

Operation example 2:
Switch the rows and columns of the range B2:D5 in the active sheet, and paste from cell B7 in the active sheet

Call Application.Run("RowColumn_Change", "", "B2:D5", "", "B7")

Note: this will not work if any of the destination cell positions are inside the range of the source cells.


-----------------------------------------------------------

Sub PCWAYsubRowColumnChange(strFromSheet As String, strFromRange As
String, strToSheet As String, strToCell As String)
Dim strSheetname As String
If strFromSheet = "" Then
strSheetname = ActiveSheet.Name
Else
strSheetname = strFromSheet
End If

Worksheets(strSheetname).Range(strFromRange).Copy
If strToSheet = "" Then
strSheetname = ActiveSheet.Name
Else
strSheetname = strToSheet
End If

Worksheets(strSheetname).Range(strToCell).PasteSpecial
Paste:=xlFormats, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Worksheets(strSheetname).Range(strToCell).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True
Application.CutCopyMode = False
End Sub
RELATED CATEGORIES

Did you solve your problems?

YES

NO

Search Keywords


If you still have problems with our products,

FEEL FREE TO CONTACT US

Return to top