Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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
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!!
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
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!!
Hi, the follwoing example create a XLS file and the name of each tab is changed by SalesMen Name + Year ...
To make you count from E to A minus! |
for y=Ubound(valy) to Lbound(valy) STEP -1
Ciao!
Daniela
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!!
Thanks gaa,
this is very helpful!
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
Ciao Daniela,
bravissima
bella storia... le mie conoscenze si limitavano all'exportbiff.
E" possibile attribuire un nome al documento XLS ???
Grazie
Claudio