VBA - Open workbook, Copy all sheets, paste as values, save as new workbook

[Nineshine]

Hello

Long time user, first time poster.

I am using Excel 2010.

I am currently trying to create a Macro that will do the following.

Open a workbook based on text written in cell

Copy all tabs from the opened workbook

Paste as values

Then save as a new workbook, also if possible from a value use a cell to determine the the file name and file path where it will be saved.

I currently have the following for opening the MMA

Sub OpenWorkbook()


Dim varCellvalue As String


varCellvalue = Range("B1").Value


Workbooks.Open "\\P:\Group Accounts\2015 MMA\" & varCellvalue & ".xls"

End Sub

However when I add any further code for copying it uses the original workbook rather than the one that has just been opened.

Any help on this would be greatly appreciated, please let me know if I can provide any further information.

 

[AlphaFrog]

Hi and welcome to the forum.

Copy all tabs from the opened workbook

Paste as values

That can be interpreted several ways. Can you be more specific?

 

[JLGWhiz]

If you are doing nothing more than making all cells with formulas convert to their calculated values, then you do not need to copy the sheets to a new workbook.

Sub OpenWorkbook()
Dim varCellvalue As String
varCellvalue = Range("B1").Value
Workbooks.Open "\\P:\Group Accounts\2015 MMA\" & varCellvalue & ".xls"
For Each sh In ActiveWorkbook.Sheets
    If sh.UsedRange.Rows.Count > 0 Then
        With sh.UsedRange
            .Value = .Value
        End With
    End If
Next
ActIveWorbook.SaveAs('Enter new file data here')
ActiveWorkbook.Close
End Sub

Fill in the correct file path and name for the SaveAs. Your original file will still be intact so long as you use SaveAs instead of Save. The original only changes if you use Save after making changes to the content.

 

[Nineshine]

Hi and welcome to the forum.



That can be interpreted several ways. Can you be more specific?

Thank you for your welcome AlphaFrog.

I would like to select everything in the workbook, i.e. all tabs/sheets hidden or not. And then everything within those tabs/sheets.

I would then like everything to pasted as values. Like you would when you right click and paste as values.

I think this is clear, but if it is not please let me know.

 

[Nineshine]

JLGWhiz

Thank you for your quick reply.

This code does everything that I have requested. I can't thank you enough.

Apologies if this appears to be cheeky, but is there anyway of doing this for multiple files?

 

[JLGWhiz]

but is there anyway of doing this for multiple files?

The question is open ended. Yes it can be done for multiple files, but you need to define how you will identify the files. Are they all in one directory and are they the only files in that directory? Are they in multiple directories or sub directories? Do you have a list of them with their directory path? A different method of handling the files is needed for each category in these questions.

 

[Nineshine]

The question is open ended. Yes it can be done for multiple files, but you need to define how you will identify the files. Are they all in one directory and are they the only files in that directory? Are they in multiple directories or sub directories? Do you have a list of them with their directory path? A different method of handling the files is needed for each category in these questions.

Hi JLGWHIZ

I had no idea I had so many options. Thank you for detailing a few of them. Ideally if you could open all the files in a folder, run the previous macro and save all into a separate folder. Also if they could be saved with the same filename but with pasted values at the end that would be the ultimate goal.

I understand that this is a lot to ask. I appreciate the time you have spent on this and would be grateful for any further time.

 

[JLGWhiz]

I have no idea what directories you use, whether they are on your PC or a server nor what their paths are. So it is impossible for me to write any code that you could use without that information unless I use fictitious paths or assume the path is the same as your host workbook. I think you need to put some more thought into this and start a new thread.

 

[Nineshine]

I have no idea what directories you use, whether they are on your PC or a server nor what their paths are. So it is impossible for me to write any code that you could use without that information unless I use fictitious paths or assume the path is the same as your host workbook. I think you need to put some more thought into this and start a new thread.

Thank you once again for your help. I have been able to learn a lot from the code and information you have already provided.

I'm sorry my inexperience has left this thread on a negative note.

 

[JLGWhiz]

Do not take it negatively that I suggest a new thread. If you read the guidelines for posting, you will see in those that when the original post has been resolved, if there is significant change to the original, then a new thread should be initiated. That provides opportunity for others to join in the process.