OK so i'm pretty new to VBA. I think im nearly there with my macro except for a few things that i'm tearing my hair out! Firstly - I have got a 'standard quote sheet' that my engineers open. Fill out the details required and then save the file as the job reference number. This then goes upstairs to be approved. Once this is approved or has the correct adjustments made they then have to fill out a 'customer quote form'. Dont ask something that the supplier wants!. Now this becomes a nightmare and time consuming. So i started a macro to autofill the suppliers form. Which works great as long as the guys filling out the quote DONT save the file as something else. My 2nd issue is saving the suppliers form. I can get this to work within reason BUT instead of it being just whats in cell G4 i want to be G4 and K4 to be the filename. I hope that all makes sense. I have copied and pasted the macro so far - dont laugh at it please ;-) Thanks in advance Ant Sub ContourToolQuote() ' ' ContourToolQuote Macro ' ' Workbooks.Open Filename:= _ "Z:\CONTOUR\2 - Tooling Costing\6 - Templates\Contour Tooling Quotation Form Template AVT.xls" Windows("DPS Master Costing Form.xlsm").Activate Range("B3:D3").Select Selection.Copy Windows("Contour Tooling Quotation Form Template AVT.xls").Activate ActiveSheet.Paste Range("G4:J5").Select Range("J4").Activate Application.CutCopyMode = False With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Selection.UnMerge With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Range("G4:K5").Select Range("K4").Activate Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyl e = xlNone Windows("DPS Master Costing Form.xlsm").Activate Windows("Contour Tooling Quotation Form Template AVT.xls").Activate Windows("DPS Master Costing Form.xlsm").Activate Range("D6").Select Selection.Copy Windows("Contour Tooling Quotation Form Template AVT.xls").Activate Range("D11").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Windows("DPS Master Costing Form.xlsm").Activate Range("B6").Select Application.CutCopyMode = False Selection.Copy Windows("Contour Tooling Quotation Form Template AVT.xls").Activate Range("D13").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Windows("DPS Master Costing Form.xlsm").Activate Range("B5").Select Application.CutCopyMode = False Selection.Copy Windows("Contour Tooling Quotation Form Template AVT.xls").Activate Range("D20").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=12 Windows("DPS Master Costing Form.xlsm").Activate Range("D10").Select Application.CutCopyMode = False Selection.Copy Windows("Contour Tooling Quotation Form Template AVT.xls").Activate Range("D32").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("D34").Select Windows("DPS Master Costing Form.xlsm").Activate Range("D11").Select Application.CutCopyMode = False Selection.Copy Windows("Contour Tooling Quotation Form Template AVT.xls").Activate Range("D34").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWorkbook.SaveAs "Z:\CONTOUR\2 - Tooling Costing\2 - To Send\" & ActiveSheet.Range("g4").Value & ".xls" End Sub

Before I look at the code, let me make one suggestion. When posting code to this forum, please use the Pre tags so that your code retains the formatting from the VBA editor. Which is easier to follow: This...? Sub YadaYada() For Each sht In ActiveWorkbook.Sheets If sht.Name = "Input" Then With Sheets(sht.Name) If .Range("A1:B5").Interior.ColorIndex = 5 Then .Range("A1:B5").Interior.ColorIndex = 4 End If End With End If Next End Sub or this...? Sub YadaYada() For Each sht In ActiveWorkbook.Sheets If sht.Name = "Input" Then With Sheets(sht.Name) If .Range("A1:B5").Interior.ColorIndex = 5 Then .Range("A1:B5").Interior.ColorIndex = 4 End If End With End If Next End Sub

There are multiple ways to accomplish your goal, this is just one of them. With Value1 in G4 and Value2 in K4, this will save the file as: "Value1 Value2.xls" myFileName = ActiveSheet.Range("G4") & " " & ActiveSheet.Range("K4") ActiveWorkbook.SaveAs myFileName & ".xls" Allow me to add a couple of coding tips. Based on how much extraneous code is present, it appears that you recorded a lot of this code. While recording and editing is perfectly fine (I do it all the time) you should edit the code and get rid of all the bloating to make it more efficient and easier to follow. It will also help you learn more of the "syntax" of VBA, making it easier to write code directly. You can also make your code a lot easier to maintain by setting variables equal to a common text string instead of repeating that string multiple times throughout the code. That way you can change the common text in one spot and it will carry through the code. For example, at the beginning of your code you could use this: QuoteTool = "Contour Tooling Quotation Form Template AVT.xls" Then throughout your code you can use: Windows(QuoteTool).Activate If you ever need to change the name of the file, you can just change it once, instead of 8 times.

Thanks for the replys and sorry for not using pre tag. i have re attached the code below. As i have said before very new to this but learning everyday! Derbydad03 - That code works a treat. One small issue is that its saving to My documents and not into Z:\Contour\To Send....etc etc Cheers again guys Ant Sub ContourToolQuote() ' ' ContourToolQuote Macro ' ' Workbooks.Open Filename:= _ "Z:\CONTOUR\2 - Tooling Costing\6 - Templates\Contour Tooling Quotation Form Template AVT.xls" Windows("DPS Master Costing Form.xlsm").Activate Range("B3:D3").Select Selection.Copy Windows("Contour Tooling Quotation Form Template AVT.xls").Activate ActiveSheet.Paste Range("G4:J5").Select Range("J4").Activate Application.CutCopyMode = False With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Selection.UnMerge With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Range("G4:K5").Select Range("K4").Activate Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Windows("DPS Master Costing Form.xlsm").Activate Windows("Contour Tooling Quotation Form Template AVT.xls").Activate Windows("DPS Master Costing Form.xlsm").Activate Range("D6").Select Selection.Copy Windows("Contour Tooling Quotation Form Template AVT.xls").Activate Range("D11").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Windows("DPS Master Costing Form.xlsm").Activate Range("B6").Select Application.CutCopyMode = False Selection.Copy Windows("Contour Tooling Quotation Form Template AVT.xls").Activate Range("D13").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Windows("DPS Master Costing Form.xlsm").Activate Range("B5").Select Application.CutCopyMode = False Selection.Copy Windows("Contour Tooling Quotation Form Template AVT.xls").Activate Range("D20").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=12 Windows("DPS Master Costing Form.xlsm").Activate Range("D10").Select Application.CutCopyMode = False Selection.Copy Windows("Contour Tooling Quotation Form Template AVT.xls").Activate Range("D32").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("D34").Select Windows("DPS Master Costing Form.xlsm").Activate Range("D11").Select Application.CutCopyMode = False Selection.Copy Windows("Contour Tooling Quotation Form Template AVT.xls").Activate Range("D34").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWorkbook.SaveAs "Z:\CONTOUR\2 - Tooling Costing\2 - To Send\" & ActiveSheet.Range("g4").Value & ".xls" End Sub

I was just showing you a technique for building the FileName from 2 cells. You still have to add the path. Modify the last line of your original code so that it picks up the values in G4 and K4 as I suggested. Yes, I'm making you figure it out on your own so you'll learn.

Got it thanks! So for my last issue - Once the file is saved as something different. (eg instead of DPS master quote its S1911833) the macro will not run. Am i correct in thinking that at the beginning I could have something like quote tool = active work sheet. Then as you said I only need to call up quote tool which when the macro starts. Am i on the right train of thought?

To be honest, I haven't dug down into your code to see what it actually does, I was only answering the specific question that you asked. The answer to your last question depends on what your goal is. Are you trying to open a template, make changes and save it as new "customized" file, or are you saving the files and then reopening them and saving them again either under the same name an/or under a different name? It sounds like your use of ActiveWorksheet might work, assuming you are reopening the file whose name you just changed.