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.
Application.DisplayAlerts = False
On Error Resume Next
On Error GoTo 0
Application.DisplayAlerts = True
Dim ws As Worksheet
' copy headings
' 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
It does not take much:
change this line of code:
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.