macro deleting workbook despite saved as different name

[tweacle]

I have a workbook which is linked to a summary via a paste special link. I have also a macro set up to delete all the data in the input screens which in turn delete the data in the summary.

Every month I have to save the data in the summaries. I at present Save it as a new file name. When I delete the original workbook using the macro it also deletes the data in the renamed file. Is there anyway I can save the data in a new renamed file and then delete the data in the original summary without it being deleted in the renamed file.

 

[malik641]

Could you post the code???

 

[tweacle]

sorry??? post thecode im lost here. Still new to excel and aint got clue what you talking about

 

[malik641]

Sorry, could you post the Macro that you are talking about (the one that deletes the original information)???

And be sure to use [ code ] [ /code ] tags when you do

 

[tweacle]

I dont know I thought that if I saved a file under a different name but had the link from the workbook previously used it wouldnt delete it cos its a different workbook name.

 

[tweacle]

This is the macro I use to delete the input screens that and also the input on the linked workbook

Workbooks.Open Filename:= _
"r:\shrared\passacc\excel\passacc\mkeynes\MKWEEK1.XLS", _
UpdateLinks:=0
Dim ws As Worksheet
' Unprotect
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect "shed"
Next ws
ActiveWorkbook.Unprotect "shd"

ActiveWindow.LargeScroll ToRight:=-26
Sheets(Array("Wednesday", "Thursday", "Friday")).Select
Sheets("WEDNESDAY").Activate
ActiveWindow.SmallScroll Down:=-54
Range("B9:BC35").Select
ActiveWindow.SmallScroll Down:=3
Range("B9:BC35,B38:I40").Select
Range("B38").Activate
ActiveWindow.SmallScroll ToRight:=3
Range("B9:BC35,B38:I40,J38:BC40").Select
Range("J38").Activate
ActiveWindow.SmallScroll Down:=9
Range("B9:BC35,B38:I40,J38:BC40,B43:BC45").Select
Range("BC45").Activate
Selection.ClearContents
ActiveWindow.SmallScroll Down:=3
Range("B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65").Select
Range("B48").Activate
Selection.ClearContents
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 57
ActiveWindow.SmallScroll Down:=-69
Range("B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35").Select
Range("BH9").Activate
ActiveWindow.SmallScroll Down:=6
Range("B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35,BH38:CI40").Select
Range("CI40").Activate
Selection.ClearContents
ActiveWindow.SmallScroll Down:=3
Range("B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35,BH38:CI40,BH43:CI45" _
).Select
Range("BH43").Activate
ActiveWindow.SmallScroll Down:=3
Range( _
"B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35,BH38:CI40,BH43:CI45,BH48:CI65" _
).Select
Range("CI48").Activate
Selection.ClearContents
ActiveWindow.LargeScroll ToRight:=8
ActiveWindow.SmallScroll Down:=-18
ActiveWindow.SmallScroll ToRight:=-2
ActiveWindow.SmallScroll Down:=-48
Range( _
"B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35,BH38:CI40,BH43:CI45,BH48:CI65,CK9:DB35" _
).Select
Range("CK9").Activate
ActiveWindow.SmallScroll Down:=6
Range( _
"B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35,BH38:CI40,BH43:CI45,BH48:CI65,CK9:DB35,CK38:DB40" _
).Select
Range("DB40").Activate
ActiveWindow.SmallScroll Down:=6
Range( _
"B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35,BH38:CI40,BH43:CI45,BH48:CI65,CK9:DB35,CK38:DB40,CK43:DB45" _
).Select
Range("CK43").Activate
ActiveWindow.SmallScroll Down:=9
Range( _
"B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35,BH38:CI40,BH43:CI45,BH48:CI65,CK9:DB35,CK38:DB40,CK43:DB45,CK48:DB65" _
).Select
Range("DB48").Activate
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-18
ActiveWindow.SmallScroll ToRight:=22
ActiveWindow.SmallScroll Down:=-42
Range( _
"B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35,BH38:CI40,BH43:CI45,BH48:CI65,CK9:DB35,CK38:DB40,CK43:DB45,CK48:DB65,DG9:DJ35" _
).Select
Range("DG9").Activate
ActiveWindow.SmallScroll Down:=3
Range( _
"B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35,BH38:CI40,BH43:CI45,BH48:CI65,CK9:DB35,CK38:DB40,CK43:DB45,CK48:DB65,DG9:DJ35,DH38:DJ40" _
).Select
Range("DJ40").Activate
ActiveWindow.SmallScroll ToRight:=-2
Range( _
"B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35,BH38:CI40,BH43:CI45,BH48:CI65,CK9:DB35,CK38:DB40,CK43:DB45,CK48:DB65,DG9:DJ35,DH38:DJ40,DG38:DG40" _
).Select
Range("DG38").Activate
ActiveWindow.SmallScroll Down:=9
Range( _
"B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35,BH38:CI40,BH43:CI45,BH48:CI65,CK9:DB35,CK38:DB40,CK43:DB45,CK48:DB65,DG9:DJ35,DH38:DJ40,DG38:DG40,DG43:DJ45" _
).Select
Range("DG43").Activate
ActiveWindow.SmallScroll Down:=6
Range( _
"B9:BC35,B38:I40,J38:BC40,B43:BC45,B48:BC65,BH9:CI35,BH38:CI40,BH43:CI45,BH48:CI65,CK9:DB35,CK38:DB40,CK43:DB45,CK48:DB65,DG9:DJ35,DH38:DJ40,DG38:DG40,DG43:DJ45,DG48:DJ65" _
).Select
Range("DG48").Activate
Selection.ClearContents
ActiveWorkbook.Save
Sheets("Thursday").Select
ActiveWorkbook.Save
' Protect
For Each ws In ActiveWorkbook.Worksheets
ws.Protect "ed"
Next ws
ActiveWorkbook.Protect "sed"

ActiveWorkbook.Save
ActiveWorkbook.Close

Thing is this deletes the data in the connected workbook and also the same data in the original workbook renamed.