Copy :=Destination

[vestax22]

Hi, is it possible to do a special paste with the destination clause of the copy command?

thx for the help

 

[VBAjedi]

Vestax22,

Not as far as I know. I've done some poking around and not found it. . .

However, it's only one extra line of code to do a copy, followed by a seperate pastespecial command. Is there a reason that approach won't work for you?

VBAjedi

 

[vestax22]

hmm

whats the extra line of code?

What I do is I open a workbook from my existing workbook. I copy a range from the workbook I opened close it and paste it inside the main workbook heres the code.

Worksheets(dateRange).Range("A1:AI45&quot.Copy _
Destination:=Workbooks(Mybook).Worksheets("Daily_Statistics&quot.Range("A1:AI45&quot

Mybook contains the name of the main workbook whereas the second workbook is already opened.

Can you help out?

thx for the reply

 

[Stretchwickster]

What Jedi means is that you have a copy line and you have a PasteSpecial line. So you need to remove the Destination argument so that you're left with this:

  Worksheets(dateRange).Range("A1:AI45").Copy

Then you on the next line have your select sheet line then the PasteSpecial line:

  Workbooks(Mybook).Worksheets("Daily_Statistics").Activate
  Range("A1:AI45").PasteSpecial ...'add the required parameters

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[VBAjedi]

Something like:

Worksheets(dateRange).Range("A1:AI45&quot.Copy
Workbooks(Mybook).Worksheets("Daily_Statistics&quot.Activate
Range("A1&quot.PasteSpecial (xlPasteValues)

Let me know if that works for you!

VBAjedi

 

[vestax22]

Thx a lot it worked great. One more question though. Now when the function is completed the data has been copied but the area where the data has been copied to is selected and blue. This might be a stupid question but how do I unselect this?

I tried selecting range("A1&quot but it didn't work
I also tried application.cutcopymode = false

any ideas?

 

[Stretchwickster]

I normally use:

Range("A1").Select 
or 
Range("A1").Activate

Is it producing an error for you?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[VBAjedi]

You probably have the other workbook selected. Try:

Worksheets(dateRange).Activate
Range("A1").Select
Workbooks(Mybook).Worksheets("Daily_Statistics").Activate
Range("A1").Select

This should select cell A1 in both sheets. You really should have a Workbook qualifier in front of your Worksheets(dateRange) references when you are working with multiple workbooks, but if the rest of your code is working without it (for now, at least!), then this should too.

HTH!


VBAjedi

 

[vestax22]

hey VBA jedi,

I close the second workbook after copying. So how could it be confused??

 

[VBAjedi]

Oh - wasn't aware of that. No, it's probably not confused then. I assume you are referencing the sheet explicitly? Try selecting a cell outside the current blue selection, then selecting A1 (a bit clunky, but if it works. . .):

Workbooks(Mybook).Worksheets("Daily_Statistics&quot.Activate
Range("Z1&quot.Select
Range("A1&quot.Select

HTH!

VBAjedi

 

[vestax22]

hey VBA Jedi

Thx a lot, I realised that I activated cell "a1" in worksheet Daily_Statistics instead of activating Daily_Statistics and then selecting the cell "a1".

It now works great, thx for the advice