You are here:

Excel/Excel VBA: merged data and copy, paste, values


I’m good with Excel 2010 but I am not a VBA expert. Based on some searches I have created some VBA code which is working but needs one tweak which I can’t figure out how to do.  Right now the code does the following:
1. In “Combined” (this is first worksheet) it clears everything below Row 1
2. It copies the titles from the first row of the 6th worksheet to the first row of the 1st worksheet ( I may remove this step and just keep my titles fixed on 1st row of 1st worksheet)
3.It then goes thru whole workbook and any worksheet that has “Tasks” in its name, it copies that data from each (below 1st row) into the “Combined” worksheet. So after it runs, the “Combined” worksheet has all the data found in the “Tasks” worksheets.
4.It then deletes any content in the “Combined” worksheet which may be in columns P thru DT.

This is all working fine but I need the copy>paste process to be copy>paste>special>values since I don’t want formulas to be carried forward into the “Combined” worksheet.  Any assistance to provide this functionality is greatly appreciated. Also any tweaks to make the code better is welcome. I have copied the current VBA code below.

Sub Combine()
Application.DisplayAlerts = False
On Error Resume Next
On Error GoTo 0
Application.DisplayAlerts = True
  Dim ws As Worksheet

   ' copy headings
   Selection.Copy Destination:=Sheets(1).Range("A1")

   ' work through sheets
  For Each ws In Worksheets
       If ws.Name Like "*Tasks*" Then
       Selection.CurrentRegion.Select ' select all cells in this sheets

       ' select all lines except title
       Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

       ' copy cells selected in the new sheet on last line
       Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
  End If

  Range("P:DT").Delete Shift:=xlToLeft

End Sub

Hi Mark,

It does not take much:

change this line of code:

Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)


Sheets(1).Range("A" & Sheets(1).Rwos.Count).End(xlUp).Offset(2).Resize(Selection.rows.count,Selection.columns.count).Value = Selection.Value

(this is one single line of code!)

Note that instead of copy/paste I use a more direct method of transferring the values.
Also not I made this Excel 2007-2013 proof.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jan Karel Pieterse


Excel and Excel/VBA questions


Excel MVP

Self employed Excel developer

Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 All rights reserved.