CODEWITHSUNDEEP
excel
BlackCoder
BlackCoder

Reputation: 115

How to Get All sheets in a workbook to one - Excel 2013

is it possible to automatically get all the sheets in workbook to a new sheet ?

i converted an PDF file(29page) to excel it is successful done, but i got 1 page as 1 sheet (so totally 29 sheets)

is there any option in excel to combine all those sheets to sheet.

Upvotes: 0

Views: 869

Answers (1)

Tushar Chhabhaiya
Tushar Chhabhaiya

Reputation: 700

You can do that by writing simple macro for that workbook

Introduction to write macro.

http://office.microsoft.com/en-in/excel-help/introduction-to-custom-macros-in-excel-HA001118958.aspx

Dim offsetRow As Integer

Sub Button1_Click()
Dim lastrow As Integer
Dim s As Integer
Dim ws As Worksheet
Dim tmpws As Worksheet
offsetRow = 3
lastrow = 1
 ThisWorkbook.Worksheets.Add
 s = ThisWorkbook.ActiveSheet.Index
For i = 2 To ThisWorkbook.Worksheets.Count
    ThisWorkbook.Worksheets(i).Select
    ThisWorkbook.Worksheets(i).UsedRange.Copy
    ThisWorkbook.Worksheets(s).Select
    ThisWorkbook.Worksheets(s).Cells(lastrow, 1).Select
    lastrow = lastrow + ThisWorkbook.Worksheets(i).UsedRange.Rows.Count + offsetRow
    ThisWorkbook.Worksheets(s).Paste
    ThisWorkbook.Worksheets(i).Select
    'ThisWorkbook.Application.SendKeys ("{ESC}")
Next i
ThisWorkbook.Worksheets(s).Select
End Sub

Upvotes: 1

Related Questions