Vba to copy & paste sheets data colmn by colmn in a sheet

[FaizanRoshan88]

I have code to copy all sheet from source wb to RawData Sheet. Here i face problem that sheets data paste by rows. i want to paste next sheet data to next column in RawData ws, and so on.

For Each Sheet In wb2.Sheets
With Sheet.UsedRange
.Copy PasteSpecialFormatsStart
Set PasteSpecialFormatsStart = PasteSpecialFormatsStart.Offset(.rows.count)
End With
Next Sheet

Here is code:

Private Sub browse_file_Click()

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

Set wb1 = ActiveWorkbook
Worksheets.add(After:=Worksheets(Worksheets.count)).Name = "RawData"
Set PasteSpecialFormatsStart = [RawData!A1]

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xlsx (*.xlsx), *.xlsm (*.xlsm),*.xls (*.xls),")

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 PasteSpecialFormatsStart
            Set PasteSpecialFormatsStart = PasteSpecialFormatsStart.Offset(.rows.count)
        End With
    Next Sheet

End If

    wb2.Close
End Sub

 

[PCosta87]

I have code to copy all sheet from source wb to RawData Sheet. Here i face problem that sheets data paste by rows. i want to paste next sheet data to next column in RawData ws, and so on.

For Each Sheet In wb2.Sheets
With Sheet.UsedRange
.Copy PasteSpecialFormatsStart
Set PasteSpecialFormatsStart = PasteSpecialFormatsStart.Offset(.rows.count)
End With
Next Sheet

Here is code:

Private Sub browse_file_Click()

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

Set wb1 = ActiveWorkbook
Worksheets.add(After:=Worksheets(Worksheets.count)).Name = "RawData"
Set PasteSpecialFormatsStart = [RawData!A1]

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xlsx (*.xlsx), *.xlsm (*.xlsm),*.xls (*.xls),")

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 PasteSpecialFormatsStart
            Set PasteSpecialFormatsStart = PasteSpecialFormatsStart.Offset(.rows.count)
        End With
    Next Sheet

End If

    wb2.Close
End Sub

Hi,

Maybe replacing

.Offset(.rows.count)

with

.Offset(, .Columns.Count)

Hope this helps