Copying Horizontallyand Pasting Vertically

[babar1988]

Hi,

I am using the below code copying horizontally from one workbook and pasting vertically to another. but once pasting it's making a mess.

Sub Alldrrdata()
Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook

Set currentworkbook = ThisWorkbook

Set sourceworkbook = Workbooks.Open("C:\Users\babar\OneDrive\Desktop\New Folder\DRR.xlsx")
sourceworkbook.Worksheets("Month Actual").Range("E58:AI58").Copy


currentworkbook.Activate
currentworkbook.Worksheets("Daily Pickup").Activate
currentworkbook.Worksheets("Daily Pickup").Range("V5:V34" & ActiveCell.Row).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

 

[alansidman]

Try using the Unpivot function within Power Query. It can be found on the Data Tab of the Ribbon and is called Get and Transform Data
Load your data to the PQ Editor. On the Transform Tab of the Editor, select Unpivot. Close and Load your file to an excel spreadsheet.

 

[babar1988]

there is no VBA solution?

 

[alansidman]

might be, but you will need to wait for someone who believes that VBA is the way to go. Patience. I prefer the methodology I proposed.