Assistance With Code Which Is Causing Excel To Close

[Si1209]

Hello

I created a tracker for our area so we could get a detailed break down of whether we are over forecast, what our lost call rate is, the asa and other various factors throughout the day. To check how we are performing against the original forecast an import is having to be run. The import goes to a link and then opens and copies the data in to the spreadsheet. However, on certain computers when it starts to open the link and then the data it just causes Excel to crash and close down.

Not sure whats causing it or why it is happening . Below is the code if anyone would be able to assist that would be great.

Application.ScreenUpdating = False
    Range("AE1").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Workbooks.Open Filename:= _
    "\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Redesign Copley.ttx"
    Range("B9:B28").Select
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("C83").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Redesign Copley.ttx").Activate
    Range("C9:C28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("G83").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Redesign Copley.ttx").Activate
    Range("D9:D28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("J83").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Redesign Copley.ttx").Activate
    Range("E9:E28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("L83").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Workbooks.Open Filename:= _
        "\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Redesign NP.ttx"
    Range("B9:B28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("C112").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Redesign Copley.ttx").Activate
    Windows("Redesign NP.ttx").Activate
    Range("C9:C28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("G112").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Redesign NP.ttx").Activate
    Range("D9:D28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("J112").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Redesign NP.ttx").Activate
    Range("E9:E28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("L112").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Workbooks.Open Filename:= _
        "\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\CPT idp tool.ttx"
    Range("B9:B28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("C141").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("CPT idp tool.ttx").Activate
    Range("C9:C28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("G141").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("CPT idp tool.ttx").Activate
    Range("D9:D28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("J141").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("CPT idp tool.ttx").Activate
    Range("E9:E28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("L141").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Workbooks.Open Filename:= _
        "\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Claims BAU Run Off idp tool.ttx"
    Range("B9:B28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("C170").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Claims BAU Run Off idp tool.ttx").Activate
    Range("C9:C28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("G170").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Claims BAU Run Off idp tool.ttx").Activate
    Range("D9:D28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("J170").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Claims BAU Run Off idp tool.ttx").Activate
    Range("E9:E28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("L170").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Workbooks.Open Filename:= _
        "\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\SF&F idp tool.ttx"
    Range("B9:B28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("C228").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("SF&F idp tool.ttx").Activate
    Range("C9:C28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("G228").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("SF&F idp tool.ttx").Activate
    Range("D9:D28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("J228").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("SF&F idp tool.ttx").Activate
    Range("E9:E28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("L228").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Workbooks.Open Filename:= _
        "\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\FRT idp tool.ttx"
    Range("B9:B28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("C257").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("FRT idp tool.ttx").Activate
    Range("C9:C28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("G257").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("FRT idp tool.ttx").Activate
    Range("D9:D28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("J257").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("FRT idp tool.ttx").Activate
    Range("E9:E28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("L257").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Workbooks.Open Filename:= _
        "\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\polisy idp tool.ttx"
    Range("B9:B28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("C286").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("polisy idp tool.ttx").Activate
    Range("C9:C28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("G286").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("polisy idp tool.ttx").Activate
    Range("D9:D28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("J286").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("polisy idp tool.ttx").Activate
    Range("E9:E28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("L286").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Workbooks.Open Filename:= _
        "\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Storm idp tool.ttx"
    Range("B9:B28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("C315").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Storm idp tool.ttx").Activate
    Range("C9:C28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("G315").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Storm idp tool.ttx").Activate
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Workbooks.Open Filename:= _
        "\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Flood Surge Team idp tool.ttx"
    Range("B9:B28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("C344").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Flood Surge Team idp tool.ttx").Activate
    Range("C9:C28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("G344").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Flood Surge Team idp tool.ttx").Activate
    Range("D9:D28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("J344").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Flood Surge Team idp tool.ttx").Activate
    Range("E9:E28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Claims Service Tracker 01.07.2016.xlsm").Activate
    Range("L344").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.ScreenUpdating = False

Apologises if this is something simple for you guys, I'm pretty much self taught with Excel and VBA and this is causing me some serious struggles trying to correct it and get it working properly.

[SamT]

Please don't expect us to do the very simple drudge work for you.

First, edit your code to delete all "Select" + carriage return + "Selection." nb: Delete only one dot, but, dot + dot is not allowed (Range("A1")..PasteSpecial).

Use this only the first time, unless you change the paste requirements:

PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False

Instead, delete all default parameters

PasteSpecial Paste:=xlPasteValues

Return your edited code here for the next step in turning your recorded macro into a real VBA Procedure.

However, on certain computers when it starts to open the link and then the data it just causes Excel to crash and close down.

So what is the software difference between the computers?

[Si1209]

Please don't expect us to do the very simple drudge work for you.

First, edit your code to delete all "Select" + carriage return + "Selection." nb: Delete only one dot, but, dot + dot is not allowed (Range("A1")..PasteSpecial).

Use this only the first time, unless you change the paste requirements:

PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False

Instead, delete all default parameters

PasteSpecial Paste:=xlPasteValues

Return your edited code here for the next step in turning your recorded macro into a real VBA Procedure.




So what is the software difference between the computers?

Firstly, i apaologise this may be simple drudge work to yourselves, however, as I have pretty much self taught myself a lot of this its not that simple to myself.

Secondly the items that are being pasted are always different so from what I understand the PasteSpecial Paste:x1PasteValues needs to be there as its pasting 10 different spreadsheets of information. So again sorry if this is simple but could you explain if I am pasting more and this can be removed how this will work and what I need to do?

Thirdly there is no difference with any software, all are running the same operating system and the same version of Excel. To my knowledge none of them have any different software on them that would create any issue.

[Paul_Hossler]

I had already started to plow though the macro recorder's generated code

No idea if this will work, but

1. When dealing with multiple open WBs I like to be explicit with all references
2. It's easier to follow without all the recorded NVA steps (faster also)
3. A lot of the copy/paste could be captured in a separate sub

Option Explicit

Sub CleanerCode()

Dim wb1 As Workbook, wb2 As Workbook

Application.ScreenUpdating = False

'assuming that the macro containing WB is open and named Claims Service Tracker 01.07.2016.xlsm
Set wb1 = ThisWorkbook

Workbooks.Open Filename:="\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Redesign Copley.ttx"

Set wb2 = ActiveWorkbook
wb1.Activate

wb2.Range("B9:B28").Copy
wb1.Range("C83").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wb2.Range("C9:C28").Copy
wb1.Range("G83").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wb2.Range("D9:D28").Copy
wb1.Range("J83").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wb2.Range("E9:E28").Copy
wb1.Range("L83").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wb2.Close False

'etc
'Workbooks.Open Filename:= "\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Redesign NP.ttx"


Application.ScreenUpdating = True
End Sub

Firstly, i apologize this may be simple drudge work to yourselves, however, as I have pretty much self taught myself a lot of this its not that simple to myself.

No worries - everyone (well 99% of us) started out with the macro recorder, and learned to clean up the code and to generalize it

1. One important thing is that it's almost never necessary to select an Excel 'thing' to work with it
2. Another is learning to use some of the VBA statements like .CurrentRegion, .Resize, .Offset, etc.

[SamT]

an import is having to be run. The import goes to a link and then opens and copies the data in to the spreadsheet. However, on certain computers when it starts to open the link

The issue seems to be in some of the software there. Note that *.ttx is not really an Excel file.

Firstly, i apologise this may be simple drudge work to yourselves, however, as I have pretty much self taught myself a lot of this its not that simple to myself.

By the time you have deleted "Select+CR+Selection." three times, it will be simple drudge work for you too.

However, if you won't even make that basically mindless, although repetitive, effort, I am certainly not going to do it for you.

[Si1209]

Paul, thank you for your help much appreciated.

Workbooks.Open Filename:="file://\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Redesign"]\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Redesign Copley.ttx"

Some reason when I go to run the macro it doesnt like this line. I have had to remove the URL part for this post as it wouldnt let me post links due to post count

By the time you have deleted "Select+CR+Selection." three times, it will be simple drudge work for you too.

However, if you won't even make that basically mindless, although repetitive, effort, I am certainly not going to do it for you.

SamT, sorry i'm wasting your time with drudge work, but again, without knowing what you were on about and without having a full understanding of VBA i'm not aware of all this drudge work you keep going on about. I'm completely self taught in this so my knowledge is very minimal. Dont like that fact? Well I'm sorry for that but there is nothing I can do. I've come looking for help and assistance not to be told i'm pretty much useless and have a lot of drudge work for people to sort through in a rude manner. Making that basic, mindless task might be easy for you but when my knowledge is very minimal please dont expect me to be at the same level as knowledge as yourself. If I was I wouldnt be on here asking for help!!

[SamT]

You are trying to learn VBA. I was giving you a very simple, but very important, first lesson regarding simply recording Excel Macros vs VBA Coding.

I will try one more time to help you learn VBA.

In your Macro, every time you see the word structure in Red, Bold, Underline below, delete it

Range("B9:B28"). Select
Selection. Copy

The results from that example should be:
Range("B9:B28").Copy


If you want to learn VBA, then you are going to have to learn to convert recorded Excel Macros into VBA code, just like all VBA programmers before you have done.

I just did a brief analysis of your 290 line recorded Excel Macro. It can be shortened to about less than a 50 line VBA Procedure. The first step is to perform all the Drudge work. There 40 instances of Select+Selection and many more cases where there is an unnecessary Application.CutCopyMode line in between. In fact, you should delete all the CutCopyMode lines while you remove the Select+Selections.

Range("B9:B28").Select
Application.CutCopyMode = False
Selection.Copy

Just that shrinks your Macro by 100 lines without any affect on the way it works. Well, it will make it a little faster, a bit more robust, and make it use less memory.

If you are more interested in recieving a well written VBA Procedure than learning how to do it yourself, we can do that. Just say the words.

[SamT]

This is what your Macro will resemble after all the basic cleanup is done. Note that this is not complete and youshouldnot use it as is

Dim Dest As Worksheet

Application.ScreenUpdating = False 

Range("AE1").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True 
Set Dest = Workbooks("Claims Service Tracker 01.07.2016").Sheets(1)

Workbooks.Open Filename:="\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Redesign Copley.ttx" 
Range("B9:B28").Copy 
Dest.Range("C83").PasteSpecial Paste:=xlPasteValues
Windows("Redesign Copley.ttx").Range("C9:C28").Copy 
Dest.Range("G83").PasteSpecial Paste:=xlPasteValues
Windows("Redesign Copley.ttx").Range("D9:D28").Copy 
Dest.Range("J83").PasteSpecial Paste:=xlPasteValues
Windows("Redesign Copley.ttx").Range("E9:E28").Copy 
Dest.Range("L83").PasteSpecial Paste:=xlPasteValues

Workbooks.Open Filename:="\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Redesign NP.ttx" 
Range("B9:B28").Copy 
Dest.Range("C112").PasteSpecial Paste:=xlPasteValues
Windows("Redesign Copley.ttx").Windows("Redesign NP.ttx").Range("C9:C28").Copy 
Dest.Range("G112").PasteSpecial Paste:=xlPasteValues
Windows("Redesign NP.ttx").Range("D9:D28").Copy 
Dest.Range("J112").PasteSpecial Paste:=xlPasteValues
Windows("Redesign NP.ttx").Range("E9:E28").Copy 
Dest.Range("L112").PasteSpecial Paste:=xlPasteValues

Workbooks.Open Filename:="\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\CPT idp tool.ttx" 
Range("B9:B28").Copy 
Dest.Range("C141").PasteSpecial Paste:=xlPasteValues
Windows("CPT idp tool.ttx").Range("C9:C28").Copy 
Dest.Range("G141").PasteSpecial Paste:=xlPasteValues
Windows("CPT idp tool.ttx").Range("D9:D28").Copy 
Dest.Range("J141").PasteSpecial Paste:=xlPasteValues
Windows("CPT idp tool.ttx").Range("E9:E28").Copy 
Dest.Range("L141").PasteSpecial Paste:=xlPasteValues

Workbooks.Open Filename:="\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Claims BAU Run Off idp tool.ttx" 
Range("B9:B28").Copy 
Dest.Range("C170").PasteSpecial Paste:=xlPasteValues
Windows("Claims BAU Run Off idp tool.ttx").Range("C9:C28").Copy 
Dest.Range("G170").PasteSpecial Paste:=xlPasteValues
Windows("Claims BAU Run Off idp tool.ttx").Range("D9:D28").Copy 
Dest.Range("J170").PasteSpecial Paste:=xlPasteValues
Windows("Claims BAU Run Off idp tool.ttx").Range("E9:E28").Copy 
Dest.Range("L170").PasteSpecial Paste:=xlPasteValues

Workbooks.Open Filename:="\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\SF&F idp tool.ttx" 
Range("B9:B28").Copy 
Dest.Range("C228").PasteSpecial Paste:=xlPasteValues
Windows("SF&F idp tool.ttx").Range("C9:C28").Copy 
Dest.Range("G228").PasteSpecial Paste:=xlPasteValues
Windows("SF&F idp tool.ttx").Range("D9:D28").Copy 
Dest.Range("J228").PasteSpecial Paste:=xlPasteValues
Windows("SF&F idp tool.ttx").Range("E9:E28").Copy 
Dest.Range("L228").PasteSpecial Paste:=xlPasteValues

Workbooks.Open Filename:="\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\FRT idp tool.ttx" 
Range("B9:B28").Copy 
Dest.Range("C257").PasteSpecial Paste:=xlPasteValues
Windows("FRT idp tool.ttx").Range("C9:C28").Copy 
Dest.Range("G257").PasteSpecial Paste:=xlPasteValues
Windows("FRT idp tool.ttx").Range("D9:D28").Copy 
Dest.Range("J257").PasteSpecial Paste:=xlPasteValues
Windows("FRT idp tool.ttx").Range("E9:E28").Copy 
Dest.Range("L257").PasteSpecial Paste:=xlPasteValues

Workbooks.Open Filename:="\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\polisy idp tool.ttx" 
Range("B9:B28").Copy 
Dest.Range("C286").PasteSpecial Paste:=xlPasteValues
Windows("polisy idp tool.ttx").Range("C9:C28").Copy 
Dest.Range("G286").PasteSpecial Paste:=xlPasteValues
Windows("polisy idp tool.ttx").Range("D9:D28").Copy 
Dest.Range("J286").PasteSpecial Paste:=xlPasteValues
Windows("polisy idp tool.ttx").Range("E9:E28").Copy 
Dest.Range("L286").PasteSpecial Paste:=xlPasteValues

Workbooks.Open Filename:="\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Storm idp tool.ttx" 
Range("B9:B28").Copy 
Dest.Range("C315").PasteSpecial Paste:=xlPasteValues
Windows("Storm idp tool.ttx").Range("C9:C28").Copy 
Dest.Range("G315").PasteSpecial Paste:=xlPasteValues
Windows("Storm idp tool.ttx").Dest.

Workbooks.Open Filename:="\\Global.lloydstsb.com\file\D_GIEWFM$\SHARED\Data\Flood Surge Team idp tool.ttx" 
Range("B9:B28").Copy 
Dest.Range("C344").PasteSpecial Paste:=xlPasteValues
Windows("Flood Surge Team idp tool.ttx").Range("C9:C28").Copy 
Dest.Range("G344").PasteSpecial Paste:=xlPasteValues
Windows("Flood Surge Team idp tool.ttx").Range("D9:D28").Copy 
Dest.Range("J344").PasteSpecial Paste:=xlPasteValues
Windows("Flood Surge Team idp tool.ttx").Range("E9:E28").Copy 
Dest.Range("L344").PasteSpecial Paste:=xlPasteValues

Application.ScreenUpdating = True

During this process, I realized that you amy not be able to use loops to go thru all the ttx files, since each file is sent to a different set of ranges in the destination worksheet.

[SamT]

Without more details about your workbooks I am only guessing, but you can convert code Structures like

Windows("Redesign Copley.ttx").Range("C9:C28").Copy 
Dest.Range("G83")

To structures like

Dest.Range("G83") = Windows("Redesign Copley.ttx").Range("C9:C28")

Future improvements include converting Windows("Redesign Copley.ttx") et al, to a simple variable "Src."

Dest.Range("G83") = Src.Range("C9:C28")

[Si1209]

SamT thanks very much for the response and the update. Appreciate it. Now I know what you mean and I understand where I can make this tidier and more stream lined I will look in to it getting it done and hope that this may have some impact on the computers that are crashing when its run. Thank you.

[SamT]

Just take note that while C&Ping the above examples, I didn't look closely at them. You can't really set one cell equal to a range of cells.

No

Dest.Range("G83") = Windows("Redesign Copley.ttx").Range("C9:C28")

Yes

Dest.Range("G83").Resize(20, 1) = Windows("Redesign Copley.ttx").Range("C9:C28")