Using the PasteSpecial & xlPasteValues funct [Solved/Closed]

tee2tee - Jul 2, 2010 at 04:00 PM - Latest reply:  tee2tee
- Jul 6, 2010 at 09:27 AM
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
See more 

2 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 3, 2010 at 02:12 AM
1
Thank you
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

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1786 users this month

That worked perfect! Thank you so much, I really appreciate it!