VBA to Copy & paste values in a new sheet in a new workbook.

[lionelnz]

Hi all.

I Have a weekly timesheet that I have automated using vba.

What happens is that from a master timesheet I copy the current worksheet into a new workbook for emailing.

When I have the new sheet in the new workbook I want to copy & paste values that in the master timesheet rely on a vlookup in a sheet that as allocation codes of hours. I have done this to take out unwanted detail in the emailed timesheet.

Here is the code that I am using to try copy & paste values that is not working. Everything else does what it is supposed to do.

'Copy and paste values as vlookup relies on master timesheet.
[COLOR=#0000ff][B]With Range("J19:J69").Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False[/B][/COLOR]
End With
'Deletes unwanted detail
Range("L19:L69").Value = ""

The code to delete values of unwanted detail works fine.

 

[JLGWhiz]

What did you select? It is much better if you can avoid using Select and Selection, to use a range reference or a an Object Variable that represents a range. Also when using PasteSpecial, You need to paste to either the upper left cell in the destination range, or specify an entire range that will accomodate the length and width of the copied range. For example, if you copy sheets(1).Range("A2:C22") then you can PasteSpecial to Sheets(2).Range("B2") and it will fill Sheet2!B2:D22. But if you have ten cells selected and try to post to Selection it will error because the copied field is larger than ten cells. Don't ask me why it will post to one cell but not 10, it is in the underlying software of the Excel application. You probably already knew this, but thought I would throw it out there just in case.

 

[lionelnz]

Thanks. I think I can see my error. I have not told the macro where I want the values pasted.

Thanks I will work on this.

 

[lionelnz]

This what I have now & it works

Range("J19:J69").Copy
Range("J19").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Thanks

 

[JLGWhiz]

Happy to helop.
Regards, JLG