MS Excel 2007 - Separate Pages in one sheet to multiple sheets

[EDCSYS]

I converted a pdf file into excel format using software tool. The conversion worked and converted 11 pages of data into a single work sheet. I manually defined the page breaks to make it display exactly as the original of 11 pages on this worksheet.

My Question:

Is there a build-in function or short-cut to separate all the pages on this one worksheet into multiple sheets in the same workbook? For example, I have page 1, 2, and 3 one sheet1, but I want to make the page 1 stays on sheet1, page 2 move to sheet2, and page 3 move to sheet3 base on the page breaks I manually inserted.

 

[Gruuuu]

Not really, but you can switch to Page Break Preview to observe the page breaks, right click->cut, insert a new sheet, right click-paste

with 11 pages, shouldn't be too bad?

 

[EDCSYS]

First, thank you for your reply.

I understand it's only 11 pages in my case and I used the same method as you said. I was just wondering if there is any easier way to do this if I have, let's say, 100 pages?

I google around and all i can find is regarding to print multiple pages on one sheet, nothing relate to my question.

 

[SkipVought]

Why even bother to import your PDF into a workbook, when you chop up the data into separate sheets and make it as worthless to workbook functionality as a PDF?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EDCSYS]

Hi SkipVought,

Thank you for your input. In my case, it was a pdf file sent from our supplier contains spreadsheet data (table mostly)in each page, I needed to extract them into excel format so I can modify it later on.

 

[Gruuuu]

So if you're going to use this data as a primary data source, why do you wish to split it up into multiple sheets?

If there's some step that you've got to take after splitting the data up (which is pretty much always a bad idea), maybe we can help with that, instead.

After all, if you have to keep doing this, keeping all that data in one place will save you time, and quite a few headaches.

 

[JPaules]

The latest version of ASAP Utilities has a function that will do what you ask.

"Sheets » Split the selected range into multiple worksheets...
With this tool you can quickly split your selected data into multiple sheets.
You can do this either by value/group in a column or you can specify the number of rows to create per sheet."

I am a huge fan of theis Excel add-in. It has saved me a lot of time by doing in on e step what I was doing manually.


--
JP

 

[EDCSYS]

Hi JPaules,

Thank you for the info.

I checked out the ASAP Utilities website and went through the description of the utilities, I saw it has function to pull out cell(s) on the sheet1 to create new sheets with the cell(s) as the new sheet name.

This is good, but what I actually looking for was to take, for example, one page from the sheet1 (there are multiple pages on the sheet1 and I manually place the page breaks to separate each page)and create a new sheet with the page on that particular sheet (actually let's say, the content of that page).

In fact, whenever excel sees there is a page break, it will automatically create a new sheet with the content below that page break. So, at the end, all the pages will be separated into its own sheet. I hope this is not too much to ask...

All these questions and requests were just out of my curiosity to see if Excel has such function to do it, I know I might be able to create and apply a script (which I haven't learn how to do that yet).

 

[Gruuuu]

There is that possibility, you would have to observe the relationship between the page breaks and the cells that they confine.

 

[SkipVought]

All these questions and requests were just out of my curiosity to see if Excel has such function to do it...

Excel is a SPREADSHEET application.

The feature that you define, absolutely DESTROYS the SPREADSHEET's functionality, so why would Excel even entertain such a disastrous, devastating feature?

You describe the features of a WORD PROCESSING application; not a SPREADSHEET application.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JPaules]

ASAP has this optio:
Sheets » Split the selected range into multiple worksheets...



--
JP

 

[EDCSYS]

Gruuuu and SkipVought,

At first, I actually thought I could just highlight cell(s), right click the highlighted area, and there would be a selection called "move ( cell(s) )to new sheet" or something like that, but I guess there's no such thing then ~

Thank you all for clear that out.

 

[SkipVought]

At first, I actually thought I could just highlight cell(s), right click the highlighted area, and there would be a selection called "move"...

No, it's actually right-click select CUT and then you select the sheet and cell and right-click select PASTE or ctr+v.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[yooneek]

Not sure how familiar you are with VBA, but you can add this to a module and see if it works for you:

Public Sub CopyFromPageBreaks()
Dim vpba() As Variant, hpba() As Variant, pb() As Variant
Dim pa As String, paa As String
Dim vpbx As Integer, hpbx As Integer, x As Integer, y As Integer, xy As Integer, z As Integer

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

'clear sheet for testing puropses
'ActiveSheet.Cells.Clear

'find printarea
pa = ActiveSheet.PageSetup.PrintArea
paa = Right(pa, Len(pa) - InStr(1, pa, ":"))

'get vertical pagepreak points
For Each vpb In ActiveSheet.VPageBreaks
vpbx = vpbx + 1
ReDim Preserve vpba(1 To vpbx)
vpba(vpbx) = vpb.Location.Column - 1
Next
'add the last printarea column
ReDim Preserve vpba(1 To vpbx + 1)
vpba(vpbx + 1) = Range(paa).Column

'get horizontal pagebreak points
For Each hpb In ActiveSheet.HPageBreaks
hpbx = hpbx + 1
ReDim Preserve hpba(1 To hpbx)
hpba(hpbx) = hpb.Location.Row - 1
Next
'add the last printarea row
ReDim Preserve hpba(1 To hpbx + 1)
hpba(hpbx + 1) = Range(paa).Row

'create ranges
For x = 1 To UBound(hpba)
For y = 1 To UBound(vpba)
xy = xy + 1
ReDim Preserve pb(1 To xy)
If x = 1 And y = 1 Then
pb(xy) = Range(Cells(x, y), Cells(hpba(x), vpba)).Address
ElseIf x = 1 And y > 1 Then
pb(xy) = Range(Cells(hpba(x), vpba(y - 1) + 1), Cells(x, vpba)).Address
ElseIf x > 1 And y = 1 Then
pb(xy) = Range(Cells(hpba(x - 1) + 1, vpba), Cells(hpba(x), y)).Address
Else
pb(xy) = Range(Cells(hpba(x - 1) + 1, vpba(y - 1) + 1), Cells(hpba(x), vpba)).Address
End If
Next y
Next x

actnm = ActiveSheet.Name
For z = 1 To UBound(pb)
'add values to range for testing purposes
'Range(pb(z)).Value = z
Range(pb(z)).Copy
Worksheets.Add After:=Worksheets(Worksheets.Count)
With Worksheets(Worksheets.Count)
.Range("A1").PasteSpecial xlPasteAll
.Name = "Data Set " & z
End With
Worksheets(actnm).Select
Next z

With Application
.Calculate
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub


I don't have your data set, so use at your own risk and verify your data after it has run.


OCD, it’s not obsessive if you can control it…