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

TBW_MK

New Member
Joined
Sep 27, 2011
Messages
11
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

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

TBW_MK

New Member
Joined
Sep 27, 2011
Messages
11
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

Legend
Joined
Jun 19, 2002
Messages
63,651
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

Code:
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

New Member
Joined
Sep 27, 2011
Messages
11
Thanks a lot for your help VoG... I would be struggling for ages to get this to work on my own!
 

TBW_MK

New Member
Joined
Sep 27, 2011
Messages
11
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Code:
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

New Member
Joined
Sep 27, 2011
Messages
11
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?
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top