Incrementing a list on clicking a macro button

[Decatron]

Hi

I've got a list of names (from which I can select one) in a cell "b10" in sheet1 whose source is in sheet2. There is a macro called "SaveFile" in sheet1.

I want the next name in the list to appear in the cell b10 each time the macro "SaveFile" is clicked onto. Kindly post a code snippet suggesting what can be added to the vba code of the macro to make this happen.

Thanks

[Ian Crawford]

May I see your version of Savefile?

[Decatron]

May I see your version of Savefile?

\\Below is the version of my SaveFile macro


Sub savefile ()

Sheet1.Range("b1:139").ExportAsFixed Format x1Type PDF, Filename:="G:\New Report" & Sheet1.Range("d9").Text
Sheet1.Range("d9") = Sheet1.Range("d9")+1

End Sub

[Paul_Hossler]

@Decatron

Please use the code tags icon to bracket your macros -- instruction in my signature

Other than B10 instead of B9 is there any difference from your other post at 1;01am? If not I'll delete the second one

If the 1:01am post is what you were looking for, you can mark is solved - instructions also in my signature

[Ian Crawford]

Firstly the range you are selecting is poorly defined. "B1:139" is meaningless. perhaps you meant ("B1:B139") but who would know, most certainly Excel most definitely does not understand your request.

Next what does this mean "I want the next name in the list to appear in the cell b10 each time the macro "SaveFile" is clicked onto"? Your code talks about Sheet1.Range("B9")+1, so at this point if for example the value in cell B9 was "Black", then you want each subsequent save to be named as Black +1, which can only result in Black1, Black1, Black1 etc. You are not actually incrementing the value to save as.

Something along the lines of


Dim i as String
Set I = Sheet1.Range("B9")
With Sheet1.Range("B1:B139")
.ExportAsFixedFormat xlType:=PDF, Filename:="G:\New Report" & i+1
End With


I think this is closer than what you had, but am happy to wait for others to correct.

[georgiboy]

From what I understand the TS has a list of names in another tab. As well as range D9 incrementing the TS wants a name from the list in another tab to be brought into cell D10. The next time the macro is run D9 increments by 1 and the next name from the list in the other tab is brought into cell D10.

The tricky bit would be to get the next name when the end of the list of names in the second tab is reached, it would need to go back to the first name in the list I would assume?

[Decatron]

From what I understand the TS has a list of names in another tab. As well as range D9 incrementing the TS wants a name from the list in another tab to be brought into cell D10. The next time the macro is run D9 increments by 1 and the next name from the list in the other tab is brought into cell D10.

The tricky bit would be to get the next name when the end of the list of names in the second tab is reached, it would need to go back to the first name in the list I would assume?

Yes, exactly. You have understood it well unlike others.

[Decatron]

Yes, it is different. The previous post was regarding incrementing a number in a cell. Whereas in this post, the subsequent item in the list must appear in place of the previously selected item, on getting the macro activated.

Sorry for the other errors that have been caused due to being new on the platform. I'll try to rectify them in order to make it simple to understand.

[georgiboy]

I have been playing with the below:

Dim x As Long

Sub savefile()
Dim rng As Range, c As Long

Set rng = Sheet2.Range("A2:A8")
c = rng.Cells.Count

If x = c Then
x = 1
Else
x = x + 1
End If

Range("D10") = rng(x)
Sheet1.Range("D9") = Sheet1.Range("D9") + 1
Sheet1.Range("B1:B139").ExportAsFixedFormat xlTypePDF, Filename:="G:\New Report\" & Sheet1.Range("D9").Value & " - " & Sheet1.Range("D10").Value
End Sub

I have attached a file so you can see what it is doing.

[Decatron]

I have been playing with the below:

Dim x As Long

Sub savefile()
Dim rng As Range, c As Long

Set rng = Sheet2.Range("A2:A8")
c = rng.Cells.Count

If x = c Then
x = 1
Else
x = x + 1
End If

Range("D10") = rng(x)
Sheet1.Range("D9") = Sheet1.Range("D9") + 1
Sheet1.Range("B1:B139").ExportAsFixedFormat xlTypePDF, Filename:="G:\New Report\" & Sheet1.Range("D9").Value & " - " & Sheet1.Range("D10").Value
End Sub

I have attached a file so you can see what it is doing.

Got no words to thank you.
It's just awesome. You've made my day.
Am absolutely stunned to see this working for which I had spent days to find a solution for.
Thanks once again.

[georgiboy]

Glad we could help, you are welcome.