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

Sheet1.Range("F14:F83").Copy
Sheet5.Cells(14, PasteToCol).PasteSpecial Paste:=xlPasteValues

 

[TBW_MK]

Thanks VoG, that works...

I was sure I'd tried that!

 

[TBW_MK]

Just one more piece of advice...

I'm trying to get the data to copy to another workbook so I've modified to the below...

Sub CopyColumnBandPasteToFirstFreeColumnSheet2()
Dim PasteToCol As Long

PasteToCol = Sheet5.Cells(2, Columns.Count).End(xlToLeft).Column + 1
Sheet1.Range("F3:F83").Copy
Workbooks.Open Filename:= _
"S:\Helpdesks\MMS Admin\HELPDESK\Call monitoring\Agents\Name\Performance-Overall.xlsx"
Sheet5.Cells(2, PasteToCol).PasteSpecial Paste:=xlPasteValues
End Sub

BUT the data writes to the existing sheet on the current workbook, rather than the workbook I have requested opening...

What am I doing wrong?

 

[VoG]

I'm not sure but opening a workbook can clear the clipboard (your code would error though if that happened).

Also, VBA can be a bit funny using sheet codenames between different workbooks. So I would use the sheet's index number instead

Sub CopyColumnBandPasteToFirstFreeColumnSheet2()
Dim PasteToCol As Long, wb As Workbook
Set wb = Workbooks.Open("S:\Helpdesks\MMS Admin\HELPDESK\Call monitoring\Agents\Name\Performance-Overall.xlsx")
PasteToCol = Sheets(5).Cells(2, Columns.Count).End(xlToLeft).Column + 1
ThisWorkbook.Sheets(1).Range("F3:F83").Copy
wb.Sheets(5).Cells(2, PasteToCol).PasteSpecial Paste:=xlPasteValues
End Sub

 

[TBW_MK]

Thanks a lot for your help VoG... I would be struggling for ages to get this to work on my own!

 

[TBW_MK]

Just one final question...

If I want to replace 'name' in the filepath with the text held in cell B4, how do I do that?

 

[VoG]

Try

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 = Sheets(5).Cells(2, Columns.Count).End(xlToLeft).Column + 1
ThisWorkbook.Sheets(1).Range("F3:F83").Copy
wb.Sheets(5).Cells(2, PasteToCol).PasteSpecial Paste:=xlPasteValues
End Sub

 

[TBW_MK]

That's great.

Thanks again VoG.

Matt

 

[TBW_MK]

In the function

PasteToCol = Sheets(5).Cells(2, Columns.Count).End(xlToLeft).Column + 1

My data goes to the next 'free' column in the destination spreadsheet. HOWEVER, if I want it to go to the column number specified in cell F5 & offset by 3 columns to the right (i.e. if F5 = 1 I want the result to go into Column D), how would I do that?