Pasting problems

[matttan]

I have two files, one of which feeds off the other (call the first 'summary' and the second 'detail'). The detail file is based on a template, but has been saved as from a preceding detail file, to avoid the need to large amounts of data entry to take place. This all works fine except the reference number of the detail file needs to be copied back into the detail file (as the 'save as' means that the reference is now the original detail file).

I've put the code below, but here is a rough explanation...The approach that I am taking is to locate the reference number in the summary file (which is easy to do, as it will always be the last reference entered, so will be at the end of a list of reference numbers), copying that, and paste-specialing it into the appropriate cell in the detail file. This al works fine, except it then goes and makes changes to the sumary file. Here's the code, and I'll explain further:

' Update quote number on new file
    Windows("NABDealSummary.xls").Activate
    Sheets("Data Upload").Select
    Range("A1").Select
    Sheets("Summary by Deal Number").Select
    Range("A1").Select
    ActiveSheet.Protect "roseville", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("Data Upload").Select
    Range("A1").Select
    ActiveSheet.Protect "roseville", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("Intro").Select
    Range("C5").Select
    Selection.Copy
    Range("A1").Select
    
    Windows(MyFile).Activate
    Sheets("Sales Info").Select
    Range("C6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWorkbook.Save
    Windows("NABDealSummary.xls").Activate
    Sheets("Summary by Deal Number").Select
    ActiveWorkbook.Save
    Workbooks("NABDealsummary.xls").Close

I've identified 'myfile' and 'originalfile' previously, and this identification is working fine.

Possibly some of the problem is coming from the use of the reference number in multiple places. That is, it is both a pure number in both files, but is also the file name (eg. 1234567.xls, with the reference number being 1234567). The place that it modifies the data in the summary is on a sheet called 'data upload', which links into all of the detail files. It replaces the original detail file path/reference with the new detail file path/reference. The net result is that the original file doesn't link into the summary anymore, while the new file effectively links in twice.

I've tried a number of different options - in particular, copying the reference number from the summary, then closing it, and then pasting into the detail file. However, the copy function aborts once the file closes. I've also toyed with identifying the selection (ie, "mySelection = Selection"), but I can't then work out how to paste that, as it was never copied, as such, so there's nothing to paste.

Sorry that this is so long winded! If there's any advice out there, I'd really appreciate it!

Thanks,

matttan

 

[matttan]

I've since tried a few other approaches, but to no avail, is there anyone out there that can help on this? It's a fairly important part of the whole process for me.

Thanks heaps!!!

 

[Tazguy37]

Perhaps you could post a small sample of your data, and what information needs to be copied to where. There's a link to the HTML Maker utility in my sig.