VBA PasteSpecial Format & Values

[pbt]

I am trying to copy a range from one sheet and paste in another sheet via VBA.

This is the code I have:

Sheets("RECAP CURRENT YEAR").Select
     Range("E:E").Copy
     Sheets("FORECAST").Activate
     ActiveSheet.Paste Destination:=Range("IV1").End(xlToLeft).Offset(0, 1)
     Range("A1").Select

Column E has formulas (=SumB3:D3) nothing more then that. I get a #REF after the paste into the FORCAST sheet.

I would like to Paste Values and Formats.

I tried changing the code to this, with different variations:

Sheets("RECAP CURRENT YEAR").Select
     Range("E:E").Copy
     Sheets("FORECAST").Activate
     ActiveSheet.PasteSpecial Paste:=xlPasteFormats, xlPasteValues _
     Destination:=Range("IV1").End(xlToLeft).Offset(0, 1)
     Range("A1").Select

But keep comming up with errors.

Any ideas, Thanks
h.h.

 

[venkat1926]

Sheets("RECAP CURRENT YEAR").Select
Range("E:E").Copy
Sheets("FORECAST").Activate
ActiveSheet.Paste
Destination:=Range("IV1").End(xlToLeft).Offset(0, 1)
Range("A1").Selec

try this modification

Sheets("RECAP CURRENT YEAR").Select
     Range("E:E").Copy
     Sheets("FORECAST").Activate
set Destination:=Range("IV1").End(xlToLeft).Offset(0, 1)
Destination.PasteSpecial xlValues
range("a1").select
application.cutcopymode=false

 

[pbt]

Thanks for the reply venkat

I get a syntax errorwith this line highlighted:

set Destination:=Range("IV1").End(xlToLeft).Offset(0, 1)

Also I need to paste Values and Formats
h.h.

 

[jindon]

Hi hh
don't "Select/Activate"

Sheets("RECAP CURRENT YEAR").Range("E:E").Copy
With Sheets("FORECAST").Range("iv1").End(xlToLeft).Offset(,1)
     .PasteSpecial xlPasteFormats
     .PasteSpecial xlPasteValues
End With

 

[pbt]

Thanks jindon,

That did it just fine.

Harry

 

[pbt]

One more question, jindon

don't "Select/Activate"

Does this line actually "Activate" that sheet?

Sheets("RECAP CURRENT YEAR").Range("E:E").Copy

Or do you have to put in:

Sheets("RECAP CURRENT YEAR").Activate

Which I did not do. Just a learning question

Harry

 

[jindon]

OK

Select/Activate creates errors unless properly written.

In most cases, you don't need to Select/Activate the object, if you specify the sheet

such like:

Regardless of the activesheet, you can do

Sheets("Sheet1").Columns("e").Delete
Sheets("Sheet2").Columns("a:f").Delete

Sheets("Sheet1").Range("a1").Value = Sheets("Sheet2").Range("c1").Value

Sheets("Sheet1").Range("a1").Copy Destination:= Sheets("Sheet2").Range("d1")

You don't need to select sheets/range and these action makes the code slower.

 

[pbt]

Thanks for the explanation.

I will try to remember this in the future. Learn something every day :wink:

Harry

 

[pbt]

The code was working Saturday night, but now it does't.

This is what I have:

Sub Forecast()
Dim ws As Worksheet
   Application.ScreenUpdating = False
   Sheets("RECAP CURRENT YEAR").Range("A:A").Copy
   With Sheets("FORECAST").Range("A:A")
     .PasteSpecial xlPasteFormats
     .PasteSpecial xlPasteValues
     .Columns("A:A").EntireColumn.AutoFit
End With
    Application.CutCopyMode = False
    
Sheets("RECAP CURRENT YEAR").Range("E:E").Copy
With Sheets("FORECAST").Range("iv1").End(xlToLeft).Offset(, 1)
     .PasteSpecial xlPasteFormats
     .PasteSpecial xlPasteValues
End With
     Range("A1").Select
     Application.CutCopyMode = False
     Application.ScreenUpdating = True
End Sub

Stepping through the code, it does the first half then copies Range ("E:E")
But from there nothing happens. I have no errors, and continueing with (F8) key, it will step through to the End.

Anybody have an idea.

h.h.

 

[pbt]

Disregard the above problem.

I solved it

Somehow I had something in column IU & IV

Cleared it out ~ works fine now.

h.h.
I need somewhere to hide for awhile