VBA For Excel - Copy Paste Values only.
 

Hello,
I am trying to consolidate a number of worksheets into one "combined"
worksheet. I've been able to get the following code to work but I want to
modify it to paste values only. Can I have your thoughts on how this might
be done?

Thanks:

Sub Combine()
Dim J As Integer
Dim k As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' work through sheets
For J = 10 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1:A1").Select
For k = 1 To 201
Selection.Copy Destination:=Sheets(1).Rows(k + 201 * (J -
10)).Columns(1)
Next k
Sheets(J).Activate
Range("a10:d210").Select
Selection.Copy Destination:=Sheets(1).Rows((J - 10) * 201 +
1).Columns(2)
Next J
End Sub

VBA For Excel - Copy Paste Values only.
 

I think you mean this:


Sub Combine()

Dim J As Integer
Dim k As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' work through sheets
For J = 10 To Sheets.Count ' from sheet 2 to last sheet

Sheets(J).Range("A1").Copy
Sheets(1).Range("A" & 201 * (J - 10)).Resize(201).PasteSpecial
xlValues

Sheets(J).Range("A10:A210").Copy
Sheets(1).Range("B" & 201 * (J - 10)).PasteSpecial xlValues
Next J
Application.CutCopyMode = False
End Sub



But I am far from sure about that what you really want to do.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Graeme" wrote:

Hello,
I am trying to consolidate a number of worksheets into one "combined"
worksheet. I've been able to get the following code to work but I want to
modify it to paste values only. Can I have your thoughts on how this might
be done?

Thanks:

Sub Combine()
Dim J As Integer
Dim k As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' work through sheets
For J = 10 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1:A1").Select
For k = 1 To 201
Selection.Copy Destination:=Sheets(1).Rows(k + 201 * (J -
10)).Columns(1)
Next k
Sheets(J).Activate
Range("a10:d210").Select
Selection.Copy Destination:=Sheets(1).Rows((J - 10) * 201 +
1).Columns(2)
Next J
End Sub

VBA For Excel - Copy Paste Values only.
 

Wigi,

Thankyou very much for the code. It is exactly what I was after and runs
much more quickly than my original posting.

Thanks,

Graeme.

"Wigi" wrote in message
...
I think you mean this:


Sub Combine()

Dim J As Integer
Dim k As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' work through sheets
For J = 10 To Sheets.Count ' from sheet 2 to last sheet

Sheets(J).Range("A1").Copy
Sheets(1).Range("A" & 201 * (J - 10)).Resize(201).PasteSpecial
xlValues

Sheets(J).Range("A10:A210").Copy
Sheets(1).Range("B" & 201 * (J - 10)).PasteSpecial xlValues
Next J
Application.CutCopyMode = False
End Sub



But I am far from sure about that what you really want to do.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Graeme" wrote:

Hello,
I am trying to consolidate a number of worksheets into one "combined"
worksheet. I've been able to get the following code to work but I want to
modify it to paste values only. Can I have your thoughts on how this
might
be done?

Thanks:

Sub Combine()
Dim J As Integer
Dim k As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' work through sheets
For J = 10 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1:A1").Select
For k = 1 To 201
Selection.Copy Destination:=Sheets(1).Rows(k + 201 * (J -
10)).Columns(1)
Next k
Sheets(J).Activate
Range("a10:d210").Select
Selection.Copy Destination:=Sheets(1).Rows((J - 10) * 201 +
1).Columns(2)
Next J
End Sub

VBA For Excel - Copy Paste Values only.
 

Making it run a lot quicker was indeed the second aim I had when posting the
code.

See you,


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Graeme" wrote:

Wigi,

Thankyou very much for the code. It is exactly what I was after and runs
much more quickly than my original posting.

Thanks,

Graeme.

"Wigi" wrote in message
...
I think you mean this:


Sub Combine()

Dim J As Integer
Dim k As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' work through sheets
For J = 10 To Sheets.Count ' from sheet 2 to last sheet

Sheets(J).Range("A1").Copy
Sheets(1).Range("A" & 201 * (J - 10)).Resize(201).PasteSpecial
xlValues

Sheets(J).Range("A10:A210").Copy
Sheets(1).Range("B" & 201 * (J - 10)).PasteSpecial xlValues
Next J
Application.CutCopyMode = False
End Sub



But I am far from sure about that what you really want to do.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Graeme" wrote:

Hello,
I am trying to consolidate a number of worksheets into one "combined"
worksheet. I've been able to get the following code to work but I want to
modify it to paste values only. Can I have your thoughts on how this
might
be done?

Thanks:

Sub Combine()
Dim J As Integer
Dim k As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' work through sheets
For J = 10 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1:A1").Select
For k = 1 To 201
Selection.Copy Destination:=Sheets(1).Rows(k + 201 * (J -
10)).Columns(1)
Next k
Sheets(J).Activate
Range("a10:d210").Select
Selection.Copy Destination:=Sheets(1).Rows((J - 10) * 201 +
1).Columns(2)
Next J
End Sub