Many thanks for getting back to me on this. I've unticked Enable Background Data, and used the ActiveWorkbook.RefreshAll command but this seems to just sit in limbo with an "updating data..." message that doesn't seem to end. Perhaps if I post the code below you may have a better idea, I've added in a comment in CAPS to show where I need the command.
Apologies for the lack of knowledge I'm fairly new to macros and VBA!
Thanks again for your help.
Sub LoopListMancBills()
'
' ListMancBills Macro
'
Do
Workbooks.Open Filename:= _
"G:\Administration\Finance\Database\ACCOUNTS\LSC\Manchester Work\Mark's\Manchester LSC Billing.xlsm"
Sheets("Bill Template").Select
Range("L4").Select
Windows("Accounts To Bill.xlsm").Activate
ActiveCell.Select
Selection.Copy
Windows("Manchester LSC Billing.xlsm").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("G16").Select
'NEED REFRESH CODE HERE, .REFRESHALL DOESN'T SEEM TO WORK. THE DATA IS FROM SQL SO PERHAPS THERE IS A DIFFERENT COMMAND?
Workbooks.Open "G:\Administration\Finance\Database\ACCOUNTS\LSC\LB Bill Numbers To Use.xls"
Range("B1").Select
Do Until ActiveCell <> "Y"
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell = "Y"
ActiveCell.Offset(0, -1).Select
ActiveWorkbook.Save
ActiveCell.Copy
ActiveWorkbook.Close
ActiveSheet.Paste
ActiveCell.Offset(-2, 0).Select
Selection.Copy
Workbooks.Open "G:\Administration\Finance\Database\ACCOUNTS\LSC\LB Bill Numbers To Use.xls"
ActiveCell.Offset(0, 2).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveCell.Offset(-1, 0).Select
Selection.Copy
Workbooks.Open "G:\Administration\Finance\Database\ACCOUNTS\LSC\LB Bill Numbers To Use.xls"
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, -2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Select
ActiveCell.Offset(-2, 1).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Select
ActiveCell.Offset(-1, 1).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Range("G16").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Dim strYear, strMonth, strDay, strNumber, strPath, strDefpath, strAcc, strClient, strToday As String
On Error Resume Next
strYear = Range("H1").Text
strMonth = Range("I1").Text
strDay = Range("J1").Text
strNumber = Range("G16").Text
strAcc = Range("L4").Text
strClient = Range("L9").Text
strToday = Range("H2").Text
strDefpath = "G:\Administration\Finance\Database\Processed Bills\"
If IsEmpty(strYear) Then Exit Sub
If IsEmpty(strMonth) Then Exit Sub
If IsEmpty(strDay) Then Exit Sub
If IsEmpty(strNumber) Then Exit Sub
MkDir strDefpath & strYear
MkDir strDefpath & strYear & "\" & strMonth
MkDir strDefpath & strYear & "\" & strMonth & "\" & strDay
MkDir strDefpath & strYear & "\" & strMonth & "\" & strDay & "\" & strNumber
strPath = strDefpath & strYear & "\" & strMonth & "\" & strDay & "\" & strNumber & "\" & strClient & " - " & strAcc & " - " & strToday
ActiveWorkbook.SaveAs Filename:=strPath, _
FileFormat:=52, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Windows("Accounts To Bill.xlsm").Activate
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
End Sub