0

Hey guys need some help here . how can I transpose a range in excel with VBS? basically copy a range from one sheet then pastespecial transpose on the other sheet. thanks in advance

TriniCurry
  • 57
  • 1
  • 9

2 Answers2

1
 Set objXLApp = CreateObject("Excel.Application")
 Set objXLWb = objXLApp.Workbooks.Open("C:\Users\CuRrY\Desktop\test1.xls")
 objXLApp.Application.Visible = True
 objXLApp.DisplayAlerts=False
 Set objXLWs = objXLWb.Sheets(1)  

 objXLWs.Range("A1:O1").Copy
 objXLWs.Range("A2").PasteSpecial ,,,True

from what I have seen in other scripts I wrote,wscript goes through the entire string so just added the commas and just put the transpose part as true >>> 

.Range("A2").PasteSpecial ,,,True

Thanks again cronos2546 , I love this site :)

TriniCurry
  • 57
  • 1
  • 9
0
Public Sub transpose()
           Worksheets("Sheet1").Range("A1:A5").Copy
           Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True
End Sub

Is this what you want?

cronos2546
  • 1,088
  • 7
  • 16
  • thx for the fast answer ill let you know if it works, but is that for VBS or VBA ? – TriniCurry Jul 08 '14 at 22:33
  • I think that it should work with VBS, but let me know if you have any problems. You may want to directly reference the excel files as objects, which would require you to dim the excel application and reference the file location. I'm going to update my post to include this. – cronos2546 Jul 09 '14 at 03:19
  • Hey dude thanks,so it turns out it didn't work in vbs but it needed to be translated into what wscript would understand will post what I used to test Thanks again :D @cronos2546 – TriniCurry Jul 21 '14 at 20:06