Copy Specific Cells and produce new output sheet

Hi Everyone

I want to create a macro that will select a certain range of cells (for example column E to Column G) in one workbook then copy and paste them into a new workbook which I can then save separately and use an an upload file for an external system,

Reason being is that the first workbook has several columns with formula in that I don't want uploading into the external system so a fresh workbook is required

Thanks in advance

Dan

Hi Marreco

Thanks for the response

I've tried the Macro but I think there is a piece missing for pasting the values from column G into the new file

I'll try and add it myself but would appreciate your help again if you could add that part in

Thanks

Hi Marreco

The first one nearly works fine.. It just needs to paste the copied range from G1:G1000 to the new saved file

Is it possibly missing a ActiveSheet.Paste function somewhere to paste the data in?

Thanks for your help

Dan

Sorry Marreco

I meant this one.. It opens the prompt, let's you name the file, saves the file but does not copy any data

Could you tell me what's missing from this one as it suits what I need much better

Thanks again

Dan

Sub marreco_1048069()

Dim strFileName As String

strFileName = InputBox("Type a name for the new workbok", "File Name")
If Trim(strFileName) = vbNullString Then Exit Sub

ActiveSheet.Range("G1:G1000").Copy Sheets.Add.Range("A1")
ActiveSheet.UsedRange.EntireColumn.AutoFit
ActiveSheet.Move
ActiveWorkbook.SaveAs "C:\MyFile\" & strFileName & ".xls", xlNormal
ActiveWorkbook.Close False

Apologies for the confusion as I am not that proficient with VBA yet

What I mean is that in the new saved file there is no data copied from column G

For example if I have in G1 "APPLE" then G2 "PEAR" then use the macro to save the new workbook none of those values appear in the new workbook

I have already changed the save file path to my desktop so I can see the file but it is empty

Thanks again

Dan

Hi Marreco

This copies the entire worksheet to a new workbook and isn't quite what I'm looking for but I will work with the examples you have given me to try and come up with a solution

Thank you for your help on this

Dan