VBA code that saves multiple excel files derived from table

[coen078]

I want to write a macro that changes the value in A10 worksheet "blad1" with the values in the table P&ID in worksheet "blad 2" every time the value is changed it needs to save that excel file with that specific value and the file name needs to be the same as that value. So let's say i have 21 different blueprint numbers ranging from P&ID 100 until 120.
I want to create 21 different excel files with the file names "P&ID 100", "P&ID 101", "P&ID 102" etc...

I hope to save a lot of time with this macro instead of manually changing the number tens or hundreds of times and saving as a different file.

Sub SaveAsCell()
Dim strName As String


strName = Sheet1.Range("A10")
ActiveWorkbook.SaveAs strName
Exit Sub

End Sub

I have this as a starting point but i want to speed it up and have it do the whole table from a different worksheet.

 

[vw412]

How do you know which of the values in Blad 2 should be put into Blad 1 cell A10?

 

[coen078]

How do you know which of the values in Blad 2 should be put into Blad 1 cell A10?

The first sheet is a cover sheet for the blueprints. So all the blueprints need a coversheet that's why all values need to be added and made a seperate file. So i actually succeeded by myself but i know have a new question. I want to print all those files from a directory map without actually opening the individual Excel files. This works fine but the problem is that it will print "Blad2" 21 times when that worksheet is redudant. Is there an easy way to online print the first sheet?

 

[vw412]

Post the code that you have so we can evaluate it to help you.

 

[coen078]

2 typo's. I meant now instead of know. And only instead of online.

 

[coen078]

Sub VoorbladenMaken()

Dim strName As String
Dim i As Integer
i = 1

Worksheets("Sheet2").Activate

Do While Cells(1 + i, 1).Value <> ""

Cells(i + 1, 1).Copy Range("M1")
i = i + 1

strName = Sheet1.Range("A10")
ActiveWorkbook.SaveAs strName

Loop

Worksheets("Sheet1").Activate

End Sub

 

[vw412]

The first sheet is a cover sheet for the blueprints. So all the blueprints need a coversheet that's why all values need to be added and made a seperate file. So i actually succeeded by myself but i know have a new question. I want to print all those files from a directory map without actually opening the individual Excel files. This works fine but the problem is that it will print "Blad2" 21 times when that worksheet is redudant. Is there an easy way to online print the first sheet?

I meant, what code are you using to print the files? Or did misunderstand and you are not using VBA to print but just printing from a Windows Directory?

 

[coen078]

Oh yes. I'm printing directly from a Windows Directory which means it just prints the whole workbook. I either want to look for a fast solution that only prints the first sheet or my vba code needs additionale code that deletes sheet2. The problem with the latter is that it basically breaks the code because the table with the data doesn't exist anymore so it can't continue.

 

[vw412]

Try to Hide Sheet2 before saving then try to print as you want and see if that works. Let me know.

 

[coen078]

For some reason it stops after the first blueprint number. So it only makes 1 file and that stops.




Sub VoorbladenMaken()

Dim strName As String
Dim i As Integer
i = 1

Worksheets("Sheet2").Activate

Do While Cells(1 + i, 1).Value <> ""

Cells(i + 1, 1).Copy Range("M1")
i = i + 1

Worksheets("Sheet2").Visible = False

strName = Sheet1.Range("A10")
ActiveWorkbook.SaveAs strName

Worksheets("Sheet2").Visible = True

Loop


End Sub