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

Nineshine

New Member
Joined
Jul 2, 2015
Messages
5
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Jul 2, 2015
Messages
5
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

New Member
Joined
Jul 2, 2015
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jul 2, 2015
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jul 2, 2015
Messages
5
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,916
Messages
5,638,976
Members
417,063
Latest member
thematulaak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top