I am working on an excel sheet that runs a macro every time you select a name from the drop-down box. The macro pulls the data and fills out the charts and tables correctly. This is where the problem is. I need to run a macro to select a different name from the drop box, then save as PDF. I can get it to save as a PDF, but it doesn't change the name in the drop-down, it just uses the same name, 40 times. I can not figure out how to get the action to select a different name each time, and then save as a pdf. Any ideas??
How do I use this macro correctly?
- Thread starter rgould09
- Start date
Can you post the macro? Wild guess, I'm assuming there's some sort of loop in the macro. Somewhere in the loop, you need to increment the drop down box. Note that VB can't literally "open drop down box, choose next 1", so you might need to define the range of cells that contain the choices, and then have the code step through those cells.
Pseudo code:
[pre]
[/pre]
Pseudo code:
[pre]
Code:
MyChoices = Range("A1:A40")
DropDownBox = Range("B2")
'Loop 40 times
For i = 1 to 40
DropDownBox = MyChoices.Cells(i,1).Value
'Make chart
'Make pdf
NextThis is the Macro code, shortened in length. I need a code that separates the save as PDF codes, and would select another name from a drop down.
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UserssmithDocumentscopy.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UserssmithDocumentscopy2.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UserssmithDocumentscopy.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UserssmithDocumentscopy2.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub
Like I stated in previous post, the macro needs to know which cells you want to change, as well as which cells contain the list of choices. Elaborating on code I supplied previously with what you have:
[pre]
[/pre]
[pre]
Code:
Sub Macro1()
MyChoices = Range("A1:A40")
DropDownBox = Range("B2")
'Loop 40 times
For i = 1 to 40
DropDownBox = MyChoices.Cells(i,1).Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UserssmithDocumentscopy" & i & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
NextUsing the same code of Luke:
the below should help you
MyChoices = Range("A1:A40")
DropDownBox = Range("B2")
'Loop 40 times
For i = 1 to 40
DropDownBox = MyChoices.Cells(i,1).Value
'Make chart
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UserssmithDocumentscopy" & i & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next
End sub
the below should help you
MyChoices = Range("A1:A40")
DropDownBox = Range("B2")
'Loop 40 times
For i = 1 to 40
DropDownBox = MyChoices.Cells(i,1).Value
'Make chart
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:UserssmithDocumentscopy" & i & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next
End sub
Hi, I tried the above out and it gave me an 'object not found' error so I added 'set' before 'MyChoices' and 'DropDownBox' and it did work saving the files but it did not go through the list. The files were all the same. If anyone has advice that would be great. I'm using the code below:
Sub Print_Exec_Summary()
Set MyChoices = Range("T1:T10")
Set DropDownBox = Range("M1")
'Loop 40 times
For i = 1 To 3
DropDownBox = MyChoices.Cells(i, 1).Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:test" & i & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next
End Sub
Sub Print_Exec_Summary()
Set MyChoices = Range("T1:T10")
Set DropDownBox = Range("M1")
'Loop 40 times
For i = 1 To 3
DropDownBox = MyChoices.Cells(i, 1).Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:test" & i & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next
End Sub
NARAYANK991
Excel Ninja
Hi ,
I am not sure what you want to do ; the export procedure is exporting the Activesheet ; this however does not change within the For ... Next loop ; what will happen is that the same worksheet will be saved to differently named files.
If you wish to save different worksheets from one workbook to different files , then you need to keep changing the Activesheet within the loop ; else you need to replace the Activesheet keyword with the name of the worksheet which will change with every pass through the loop ; for this , you can use a For Each construct as in :
[pre]
[/pre]
Narayan
I am not sure what you want to do ; the export procedure is exporting the Activesheet ; this however does not change within the For ... Next loop ; what will happen is that the same worksheet will be saved to differently named files.
If you wish to save different worksheets from one workbook to different files , then you need to keep changing the Activesheet within the loop ; else you need to replace the Activesheet keyword with the name of the worksheet which will change with every pass through the loop ; for this , you can use a For Each construct as in :
[pre]
Code:
For Each wk In ThisWorkbook.Worksheets
If wk.Visible = xlSheetVisible Then
wk.ExportAsFixedFormat xlTypePDF, i & ".pdf", xlQualityMinimum, True, False, , , True
i = i + 1
End If
NextNarayan