Export to Excel - Different Sheets in the Same Workbook

[#1]

Hi,

Suppose I have 10 markets in data and 1 pivot table.

I want to export 10 tables to 10 excel sheets with different market selected.

ie. select Market A export to Sheet 1, select group B export to Sheet 2 etc.

I need a macro that can automatically select each market one by one, and export the pivot to each excel sheet.

I've seen the same question on forum but there was no answer. Does anyone know where I can find examples? THANK YOU!!

[#2]

This is what I've got right now : I can export tables to 5 excel files into 5 sheets in the same file.

I feel I'm almost there....can anyone help me with the macro?

THANKS!!

[#3 · Accepted solution]

For you

sub CopyToXLbyDMA
' Set Excel App
set XLApp = CreateObject("Excel.Application") ' Define Object
set XLDoc = XLApp.Workbooks.Add 'Open new workbook
XLApp.Visible = True 'Visible set as true
z = 0
Set LBy = ActiveDocument.GetSheetObject("LB01") 'Per Year
valy = LBy.GetPossibleValues
for y= lbound(valy) to ubound(valy)
Time_BeforeSelection = ActiveDocument.GetApplication.GetElapsedMilliseconds
vSelectedItemy = valy(y)
Set Fieldy = LBy.GetField
Fieldy.Select vSelectedItemy

Time_AfterSelection = ActiveDocument.GetApplication.GetElapsedMilliseconds
ActiveDocument.GetApplication.WaitForIdle

XLDoc.Worksheets.Add
set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted

' Get TB1
set table = ActiveDocument.GetSheetObject("CH01")
' Copy table and paste into Excel
table.CopyTableToClipboard true 'Copy data to Clipboard
XLSheet.Paste XLSheet.Range("A1") 'Paste data into cell
XLsheet.Cells.EntireColumn.AutoFit
XLsheet.Cells.EntireRow.AutoFit
XLsheet.name = "Tabella_"&y
next 'Year
Fieldy.Clear
end sub

Daniela

[#4]

Thank you Daniela!!

I've changed your script a little bit and worked very well!!

Please see the attached file - I HAVE ANOTHER QUESTION

I have 5 markets, now I can let the macro run from A to E and rename each tab by A, B,C,D,E.

What if I want them to rum from E to A and rename the tab names as well?

I tried to use for y= ubound(valy) to lbound(valy) but it didn't work, so I created another index column assigning A as 5, ..., E as 1.

So the variable becomes the index number.

How do I rename the tabs as Market E, D, C, B,A?

How to make the code recognize what is being selected in field "DMA" and name tabs as selected DMA?

THANKS!!

[GabrielAraya]

Hi, the follwoing example create a XLS file and the name of each tab is changed by SalesMen Name + Year ...

[#6]

To make you count from E to A minus!

for y=Ubound(valy) to Lbound(valy) STEP -1

Ciao!

Daniela

[#7]

Daniela,

Thank you very much!!

But can you illustrate more clearly about this part?

Do I type "step -1" right after the " for y=Ubound(valy) to Lbound(valy) "?

That didn't work for me, the last part "Fieldy.Clear" was highlighted and the comment was "Object required : 'Fieldy''

Why is that? THANK YOU IN ADVANCE!!

[#8]

Thanks gaa,

this is very helpful!

[#9]

Grazie, il nome al foglio sono riuscito a darglielo MI ERO INCASINATO PERCHE' E' OBBLIGATORIO DARGLI UN NUMERO NON NE CAPISCO IL MOTIVO MA VUOLE OBBLIGATORIAMENTE UN FOGLIO NUMERICO

XLsheet.name = vSelectedItemy &

"_("&y &")"

Non riesco però ad attribuire il nome al documento xls

Grazie Ancora

Claudio

[#10]

Ciao Daniela,

bravissima

bella storia... le mie conoscenze si limitavano all'exportbiff.

E" possibile attribuire un nome al documento XLS ???

Grazie

Claudio