PDA

View Full Version : Solved: Copy the data



jignesh142
11-17-2012, 10:34 AM
I have some data in one excel file and i want to open the another excel file, copy the data in that excel file and then close it again. In short i want to export that data in another excel file.
Also i want to transpose the data in following manner, for example
Old excel range A1-D1 to new excel Range A1-A4
Old excel range A2-D2 to new excel range A5-A8
Old excel range A3-D3 to new excel range A9-A10
And so on.....
Can any body help me ?

patel
11-17-2012, 11:08 AM
Public Sub copyrange2()
Dim wk1 As Workbook, wk2 As Workbook, sh As Worksheet
Dim sh1 As Worksheet, lng As Long, lRow As Long
Set wk1 = ThisWorkbook
Set wk2 = Workbooks.Open(ThisWorkbook.Path & "\yourfile.xlsx")
Set sh1 = wk1.Worksheets(1)
Set sh2 = wk2.Worksheets(1)
sh1.Range("A1:D1").Copy
sh2.Range("A1").PasteSpecial Transpose:=True
sh1.Range("A2:D2").Copy
sh2.Range("A5").PasteSpecial Transpose:=True
sh1.Range("A3:D3").Copy
sh2.Range("A9").PasteSpecial Transpose:=True
Application.CutCopyMode = False
wk2.Close
End Sub

jignesh142
11-17-2012, 09:56 PM
Many Many Thanks

It worked .......!!!!