Dear Experts,
I have trying to build some VBA code. The code is working upto copy, but its not pasting the values in transpose.
Somone please help me to correct this code .
Sub OEM()
Dim Crit1 As String, myfolder As String
Dim LastRow As Long, LastRow2 As Long, i As Long
Dim myRange
Application.DisplayAlerts = False
Application.ScreenUpdating = False
myfolder = Workbooks("update values.xlsm").Sheets("List").Range("I2")
Application.Workbooks.Open (myfolder & "\" & "OEM Sales Reporting 2013.xlsx")
With Workbooks("update values.xlsm")
LastRow = Workbooks("update values.xlsm").Sheets("List").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Crit1 = Workbooks("update values.xlsm").Sheets("List").Cells(i, 1).Value
Cells(i, 1).Offset(0, 2).Select
Range(Selection, Selection.End(xlToRight)).Copy
'Set myRange = Workbooks("OEM Sales Reporting 2013.xlsx").Sheets(Crit1).Range("A22:N22" & Workbooks("OEM Sales Reporting 2013.xlsx").Sheets("Crit1").Range("A22:N22" & Rows.Count).End(xlLeft).Row).SpecialCells(xlCellTypeVisible)
'myRange.Copy Destination:=Workbooks("OEM Sales Reporting 2013.xlsx").Sheets(Crit1).Range("A22:N22")
Workbooks("OEM Sales Reporting 2013.xlsx").Sheets(Crit1).Range("a22").End(xlToRight).Select
Selection.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Next i
Workbooks("OEM Sales Reporting 2013.xlsx").Close SaveChanges:=True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End With
End Sub
Bookmarks