Macro to copy range of cells to the next free column in another worksheet

[TBW_MK]

I have the following macro which is taking data from one range of cells & copying it to the next free column in another worksheet (starting from row 14).

Sub CopyColumnBandPasteToFirstFreeColumnSheet2()
Dim PasteToCol As Long

PasteToCol = Sheet5.Cells(14, Columns.Count).End(xlToLeft).Column + 1
Sheet1.Range("F14:F83").Copy Sheet5.Cells(14, PasteToCol)

End Sub

It works well.

However I need it to Paste the Value rather than the formula - I've tried to add the .PasteSpecial Paste:=xlPasteValues part to both line, but have no luck.

How can I get it to work / where am I going wrong?

 

[VoG]

Try

pastetocol = Range("F5").Value + 3

 

[TBW_MK]

Hmmm... that creates a Ruin time error '13' - Type mismatch error message

So I now have...

Sub CopyColumnBandPasteToFirstFreeColumnSheet2()
Dim PasteToCol As Long, wb As Workbook
Set wb = Workbooks.Open("S:\Helpdesks\MMS Admin\HELPDESK\Call monitoring\Agents\" & Range("B4").Value & "\Performance-Overall.xlsx")
PasteToCol = Range("F5").Value + 3
ThisWorkbook.Sheets(1).Range("F3:F83").Copy
wb.Sheets(1).Cells(2, PasteToCol).PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

I've tried various combinations of the amendment with the previous code for the line starting PasteToCol, but all seem to have the same error...

Do I need this line of code or can it go in the line starting wb.Sheets(1)?

Thanks.

 

[VoG]

Perhaps it should be

PasteToCol = ThisWorkbook.Sheets(1).Range("F5").Value + 3

or at least you need to qualify the workbook and sheet that you want to grab the value from.

 

[TBW_MK]

Thanks VoG for your ongoing help here...

I'm now getting a runtime error 9: subscript out of range message & a highlight on the final line of code...
wb.Sheets(5).Cells(2, PasteToCol).PasteSpecial Paste=:xlPasteValues

I also can't revert to the previous code without an error... why would that be?

 

[VoG]

That error basically means that something doesn't exist. Do you still have a sheet 5?

 

[TBW_MK]

I think so...

The 'destination' file opens - it only has the one sheet - Sheet5.

In the VBAProject pane it is listed as: Sheet12 (Sheet5). I've tried listing both, but get the same result...

 

[VoG]

If there is only one sheet, its index number is 1. Try

wb.Sheets(1).Cells(2, PasteToCol).PasteSpecial Paste:=xlPasteValues

 

[TBW_MK]

Excellent... that one worked.

Thanks.