save as help 1

[Pandab2002]

Hi. I have an excel spreadsheet the has 132 rows of information. I am trying to come up with a way to use the data in column "I" as "save as" file names for another spreadsheet that is open. In other words, I want to select the contents of "I2", do a CTRL-C (to copy), the select the other workbook, do a ALT-F-A (to save as), do a CTRL-V (to paste the new save name). And finally, send a "S" to save. Then I want to repeat the whole process 131 more times, stepping down to "I3" then "I4" and so on. Any suggestions?

Thank you
Paul...

 

[PHV]

I'd use automation instead of SendKeys ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rofeu]

something along the lines of:

Sub SaveOtherBooksAs()

Dim x As Integer

For x = 2 To 132
    With Workbooks(x - 1)
        .SaveAs ThisWorkbook.Sheets(1).Cells(x, 9)
        .Close
    End With
Next

End Sub

The code needs to be in the workbook that has the list in column I.

How will you know the name of the workbook that needs to be saved as the value in Ix though? Are the workbooks in the same order as the values in column I? (my code assumes that the wb with the list to use is wb 1 and the rest is to be saved in the same order as the values in column I)

This hasn 't been tested by the way.

Cheers,

Roel

 

[Pandab2002]

Thanks Roel,

The contents of column "I" are a 3 digit code and an airport code. "010_MCI" for example. I have two spreadsheets open, the one with the 132 rows containing the information above, and another spreadsheet, which I want to do the save as command 132 times. In the end, I want 132 spreadsheets with unique names. I can do this manually but it is time consuming and I have to do it weekly.

Paul

 

[PHV]

And what have you tried so far ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pandab2002]

PHV,

I clicked record and did everything manually for 10 saves. It works, but there must be an easier way.

Paul

 

[PHV]

And what is the recorded code for one save ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Fr33dan]

try this

    Dim folder$
    Dim FileName
    folder$ = "C:\"
    
    row = 1
    
    Do While (Cells(row, 9) <> Null)
        FileName = Cells(row, 9).Value
        Workbooks("WorkbookName").Activate
        ActiveWorkbook.SaveAs FileName:= _
            folder & FileName, FileFormat:= _
            xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
            , CreateBackup:=False
        Workbooks("WorkbookName2").Activate
        row = row + 1
    Loop

and replace WorkbookName with the name of the workbook your are saving ,WorkbookName2 with the name of the workbook you are getting the data from and C:\ with the folder you want to save the files in

 

[Pandab2002]

Thanks Fr33dan. I mad a couple modifications and it works like a charm. Here is the finished product.

Dim folder$
Dim FileName
Dim WorkbookName
WorkbookName = "xyz.xls"
folder$ = "C:\Documents and Settings\Administrator\"
Workbooks.Open folder & WorkbookName
Workbooks("abc.xlsx").Activate
Row = 2

Do While (Cells(Row, 1) <> "end")
FileName = Cells(Row, 1).Value
Workbooks(WorkbookName).Activate
ActiveWorkbook.SaveAs FileName:= _
folder & FileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Workbooks("abc.xlsx").Activate
Row = Row + 1
WorkbookName = FileName + ".xls"
Loop



I moved all the data to column A. For some reason the "Do While (Cells(Row, 1) <> Null)" did not work so I added "end" to the last cell and used "Do While (Cells(Row, 1) <> "end"). It works perfectly. Thanks again.

Paul

 

[PHV]

To avoid all those Activate:
...
Workbooks("abc.xls").Activate
Row = 2
Do While (Cells(Row, 1) <> "end")
FileName = Cells(Row, 1).Value
Workbooks(WorkbookName).SaveAs FileName:= _
folder & FileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Row = Row + 1
WorkbookName = FileName & ".xls"
Loop

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886