transpose unknown file

L

leeners82

I need to create a macro where the user is first prompted to select th
file path and then a range from the selected file is copied or inserte
in to another workbook. I am guessing that I need to us
"getopenfilename" and "transpose" in some combination but I have n
idea how to do that. Any suggestions?
 
D

Dave Peterson

This might get you started:

Option Explicit
Sub testme01()

Dim myFileName As Variant
Dim RngToCopy As Range
Dim RngToPaste As Range
Dim wkbk As Workbook

Set RngToPaste = Nothing
On Error Resume Next
Set RngToPaste = Application.InputBox(prompt:="Where to paste?", _
Type:=8).Cells(1)
On Error GoTo 0

If RngToPaste Is Nothing Then
Exit Sub
End If

myFileName = Application.GetOpenFilename("Excel File, *.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set wkbk = Workbooks.Open(Filename:=myFileName)

Do
Set RngToCopy = Nothing
On Error Resume Next
Set RngToCopy = Application.InputBox(prompt:="What to copy?", _
Type:=8).Areas(1)
On Error GoTo 0

If RngToCopy Is Nothing Then
'user hit cancel
wkbk.Close savechanges:=False
Exit Sub
End If

If LCase(RngToCopy.Parent.Parent.FullName) <> LCase(myFileName) Then
MsgBox "Please choose a range in: " & myFileName
Else
Exit Do
End If
Loop

RngToCopy.Copy
On Error Resume Next
RngToPaste.PasteSpecial Transpose:=True
If Err.Number <> 0 Then
MsgBox "Paste didn't work correctly!"
End If
On Error GoTo 0

wkbk.Close savechanges:=False

End Sub
 
Top