Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copy worsksheets and paste values only into new worksheet

  Asked By: Conrad    Date: Aug 29    Category: MS Office    Views: 822
  

The following code takes data from several worksheets and merges
them all into one, however I now need to be able to just paste the
values. I am now stuck, anyone any idea of how I alter the code to
do this?

Sub Combine()
Dim J As Integer

On Error Resume Next
Sheets("Merge").Select
Worksheets("Merge").Range("A2:AB65536").Clear

' copy headings
Sheets("Calanova Golf & Sea All").Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets("Merge").Range("A1")

' work through sheets
For J = 2 To 28 ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this
sheets

' select all lines except title
Selection.Offset(2, 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

Worksheets("Datesheet").Select

End Sub

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Kellie Bishop     Answered On: Aug 29

Have you tried recording a macro to do this? The paste  you want is "paste
Special" from the edit menu instead of hitting the paste icon.

 
Answer #2    Answered By: Mona Wagner     Answered On: Aug 29

>No Sorry, not an option, I still have to get the original data  from
elsewhere. There are approx. 25 worksheets  which is why I use merge
VBA as below. I now need to take info from one  workbook to this
workbook and then do the merge. Only thing is I do not have time to
give each formula absolute reference.

So now when I use the merge vba it takes reference from the other
workbook but alters all the cell references.

 
Answer #3    Answered By: Eloise Lawrence     Answered On: Aug 29

That doesn't sound right somehow... Do you mean you can edit VBA code
but you can't record a macro????

 
Answer #4    Answered By: Doyle Gonzalez     Answered On: Aug 29

You are not listening to what Lisa says. Your code  is correct, but
misses the last part, where you actually paste  the information into the
Merge sheet.

So record a macro as Lisa suggested, and the answer will appear before
you.

 
Didn't find what you were looking for? Find more on Copy worsksheets and paste values only into new worksheet Or get search suggestion and latest updates.




Tagged:          

 
 
 
 

Related Post