Using the PasteSpecial & xlPasteValues functSolved/Closed

Question

Hello, I'm working in Excel 2007, I'm trying to amend this vba script. I'm taking multiple worksheets, and combining them into one worksheet. It works perfectly except for one thing, in my worksheets I have a formula that takes M1*N2=O3. When I run this script, that column brings over the formula.  I've tried using the xlpaste but I'm not good with VBA and I don't get any records when I change it. 

Any suggestions? 
Thank you,  
Tina 

Sub Combine() 
    Dim J As Integer 

    On Error Resume Next 
    Sheets(1).Select 
    Worksheets.Add ' add a sheet in first place 
    Sheets(1).Name = "Combined" 

    ' copy headings 
    Sheets(2).Activate 
    Range("A1").EntireRow.Select 
    Selection.Copy Destination:=Sheets(1).Range("A1") 

    ' work through sheets 
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet 
        Sheets(J).Activate ' make the sheet active 
        Range("A1").Select 
        Selection.CurrentRegion.Select ' select all active 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) 

          
         Next 
End Sub

rizvisa1 · Answer

Try this

Sub Combine()
Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"
    
    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")
    
    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("A1").Select
        Selection.CurrentRegion.Select ' select all active cells in this sheets
        
        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
        
        'copy the selection
        Selection.Copy
        
        ' select the sheets(1). NOTE SHEET(1) needs to be visible
        Sheets(1).Select
        
        ' In column A, find the last non-blank cell and select one cell down from that cell
        Range("A" & Rows.Count).End(xlUp)(2).Select
        
        ' this is in case you want to retain the format too from the src. Remove the ' to activate the code
        'Selection.PasteSpecial xlPasteAll
        
        'paste the copied data as values
        Selection.PasteSpecial xlPasteValues
        
    Next
End Sub

Using the PasteSpecial & xlPasteValues funct

1 response

Subscribe To Our Newsletter!