Pasting problems

matttan

New Member
Joined
Jan 13, 2005
Messages
39
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:

Code:
' 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! :rolleyes:

Thanks,

matttan
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

matttan

New Member
Joined
Jan 13, 2005
Messages
39
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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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.
 

Forum statistics

Threads
1,147,673
Messages
5,742,530
Members
423,736
Latest member
dracula cyrus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top