Using VBA program to copy and transpose on to new sheet

[ksmallfoot]

Hello! I am trying to write a program that lets me select a file, after selection it would then open the file and copy the contents in the first sheet. From here I want the code to paste (transposed) the data on to the second sheet of that selected file. Right now, this code is pasting the contents of sheet 1 on to the active workbook. I am not sure how to make it paste on to sheet 2 of the selected workbook. I hope someone can help!

Here is the code I have so far:

_____________________________________________________________
Sub Start()


Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range


Set wb1 = ActiveWorkbook
Set PasteStart = [Sheet1!A1]


Sheet1.Select
Cells.Select
Selection.ClearContents


FileToOpen = Application.GetOpenFilename(Title:="Please choose a File to Open", _
FileFilter:="Report Files *.csv (*.csv),")


If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen)


For Each Sheet In wb2.Sheets
With Sheet.UsedRange
.Copy PasteStart
Set PasteStart = PasteStart.Offset(.Rows.Count)
End With
Next Sheet


End If
wb2.Close



End Sub
_________________________________________________________________


Thank you!

-K

 

[John_w]

You should have continued in your existing thread https://www.mrexcel.com/forum/excel...-open-file-using-inputbox-type-file-name.html, rather than starting a new one for the same request.

 

[Fluff]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).