Copy & Paste values macro...almost there?
 

Hi there, l am struggling with the final tweaks of the macro below, l can
to find and select the correct data(thanks to this ng) after the data
is selected it is copied, then l want to paste the values (values only) into
the correct ws...l have got it to go to the correct ws but I just don't
know how to get it to paste the values there...have l structured the
macro incorrectly.....

any help would be very welcome....TIA


Sub aaarrrggghhhh()

Dim SheetName As String
Dim sheetnumber As Long
Dim rightsheet As Long
Dim idnumber As String
Dim activeid As String
Dim xlrow As Long
Dim scennumber As Variant

'this bit finally selects and copies the data (took me ages!)
Sheets("Live History").Select
Sheets("Live History").Range("A1").End(xlDown).Select
Range(ActiveCell, ActiveCell.Offset(0, 20)).Select
Selection.Copy

Sheets("Live History").Select
Sheets("Live History").Range("N1").End(xlDown).Select
ActiveCell.Offset(0, 5).Select
idnumber = ActiveCell.Value
Sheets("Selection").Select

For sheetnumber = 1 To 5

xlrow = 3

SheetName = "S" & Format(sheetnumber, "##0")
Sheets(SheetName).Select

rightsheet = Sheets("Live History").Range("H1").End(xlDown).Value
scennumber = ActiveSheet.Cells(3, 2).Value

'This bit finds the right place to paste (suprised myself)
If rightsheet = scennumber Then
ActiveSheet.Range("BW3").End(xlDown).Select
ActiveCell.Offset(1, 0).Select

End If

Next

End Sub

Copy & Paste values macro...almost there?
 

the short answer is
Selection.PasteSpecial Paste:=xlPasteValues

but you also need to get rid of the selects. Example
Sheets("Live History").Select
Sheets("Live History").Range("A1").End(xlDown).Select
Range(ActiveCell, ActiveCell.Offset(0, 20)).Select
Selection.Copy
can be
Sheets("Live History").Range("A1").End(xlDown).Offset(0, 20)).Copy
and then
Sheets("othersheet").Range("A1").End(xlDown).Offse t(0, 20)). _
PasteSpecial Paste:=xlPasteValues

A bit of work with these ideas should help you learn

"ste mac" wrote in message
om...
Hi there, l am struggling with the final tweaks of the macro below, l can
to find and select the correct data(thanks to this ng) after the data
is selected it is copied, then l want to paste the values (values only)
into
the correct ws...l have got it to go to the correct ws but I just don't
know how to get it to paste the values there...have l structured the
macro incorrectly.....

any help would be very welcome....TIA


Sub aaarrrggghhhh()

Dim SheetName As String
Dim sheetnumber As Long
Dim rightsheet As Long
Dim idnumber As String
Dim activeid As String
Dim xlrow As Long
Dim scennumber As Variant

'this bit finally selects and copies the data (took me ages!)
Sheets("Live History").Select
Sheets("Live History").Range("A1").End(xlDown).Select
Range(ActiveCell, ActiveCell.Offset(0, 20)).Select
Selection.Copy

Sheets("Live History").Select
Sheets("Live History").Range("N1").End(xlDown).Select
ActiveCell.Offset(0, 5).Select
idnumber = ActiveCell.Value
Sheets("Selection").Select

For sheetnumber = 1 To 5

xlrow = 3

SheetName = "S" & Format(sheetnumber, "##0")
Sheets(SheetName).Select

rightsheet = Sheets("Live History").Range("H1").End(xlDown).Value
scennumber = ActiveSheet.Cells(3, 2).Value

'This bit finds the right place to paste (suprised myself)
If rightsheet = scennumber Then
ActiveSheet.Range("BW3").End(xlDown).Select
ActiveCell.Offset(1, 0).Select

End If

Next

End Sub

Copy & Paste values macro...almost there?
 

I think this will do what you're looking for:


Public Sub aaarrrggghhhh()
Dim copyRange As Range
Dim sheetNumber As Long
Dim rightSheet As Long
Dim idNumber As String

With Sheets("Live History")
Set copyRange = .Range("A1").End(xlDown).Resize(1, 20)
idNumber = .Range("N1").End(xlDown).Offset(0, 5).Value
rightSheet = .Range("H1").End(xlDown).Value
End With
For sheetNumber = 1 To 5
With Sheets("S" & sheetNumber)
If .Cells(3, 2).Value = rightSheet Then _
copyRange.Copy Destination:= _
.Range("BW3").End(xlDown).Offset(1, 0)
End With
Next sheetNumber
End Sub


In article ,
(ste mac) wrote:

Hi there, l am struggling with the final tweaks of the macro below, l can
to find and select the correct data(thanks to this ng) after the data
is selected it is copied, then l want to paste the values (values only) into
the correct ws...l have got it to go to the correct ws but I just don't
know how to get it to paste the values there...have l structured the
macro incorrectly.....

any help would be very welcome....TIA


Sub aaarrrggghhhh()

Dim SheetName As String
Dim sheetnumber As Long
Dim rightsheet As Long
Dim idnumber As String
Dim activeid As String
Dim xlrow As Long
Dim scennumber As Variant

'this bit finally selects and copies the data (took me ages!)
Sheets("Live History").Select
Sheets("Live History").Range("A1").End(xlDown).Select
Range(ActiveCell, ActiveCell.Offset(0, 20)).Select
Selection.Copy

Sheets("Live History").Select
Sheets("Live History").Range("N1").End(xlDown).Select
ActiveCell.Offset(0, 5).Select
idnumber = ActiveCell.Value
Sheets("Selection").Select

For sheetnumber = 1 To 5

xlrow = 3

SheetName = "S" & Format(sheetnumber, "##0")
Sheets(SheetName).Select

rightsheet = Sheets("Live History").Range("H1").End(xlDown).Value
scennumber = ActiveSheet.Cells(3, 2).Value

'This bit finds the right place to paste (suprised myself)
If rightsheet = scennumber Then
ActiveSheet.Range("BW3").End(xlDown).Select
ActiveCell.Offset(1, 0).Select

End If

Next

End Sub

Copy & Paste values macro...almost there?
 

Missed the "Values only".

Replace

copyRange.Copy Destination:= _
.Range("BW3").End(xlDown).Offset(1, 0)


with

.Range("BW3").End(xlDown).Offset( _
1, 0).Resize(1, 20).Value =
copyRange.Value

In article
,
"J.E. McGimpsey" wrote:

I think this will do what you're looking for:


Public Sub aaarrrggghhhh()
Dim copyRange As Range
Dim sheetNumber As Long
Dim rightSheet As Long
Dim idNumber As String

With Sheets("Live History")
Set copyRange = .Range("A1").End(xlDown).Resize(1, 20)
idNumber = .Range("N1").End(xlDown).Offset(0, 5).Value
rightSheet = .Range("H1").End(xlDown).Value
End With
For sheetNumber = 1 To 5
With Sheets("S" & sheetNumber)
If .Cells(3, 2).Value = rightSheet Then _
copyRange.Copy Destination:= _
.Range("BW3").End(xlDown).Offset(1, 0)
End With
Next sheetNumber
End Sub

Copy & Paste values macro...almost there?
 

A big thanks to both J.E Gimpsey and Don for a couple of reasons,
being very quick to help and giving some sound programming
advice...cheers...

seeya ste


"J.E. McGimpsey" wrote in message ...
I think this will do what you're looking for:


Public Sub aaarrrggghhhh()
Dim copyRange As Range
Dim sheetNumber As Long
Dim rightSheet As Long
Dim idNumber As String

With Sheets("Live History")
Set copyRange = .Range("A1").End(xlDown).Resize(1, 20)
idNumber = .Range("N1").End(xlDown).Offset(0, 5).Value
rightSheet = .Range("H1").End(xlDown).Value
End With
For sheetNumber = 1 To 5
With Sheets("S" & sheetNumber)
If .Cells(3, 2).Value = rightSheet Then _
copyRange.Copy Destination:= _
.Range("BW3").End(xlDown).Offset(1, 0)
End With
Next sheetNumber
End Sub


In article ,
(ste mac) wrote:

Hi there, l am struggling with the final tweaks of the macro below, l can
to find and select the correct data(thanks to this ng) after the data
is selected it is copied, then l want to paste the values (values only) into
the correct ws...l have got it to go to the correct ws but I just don't
know how to get it to paste the values there...have l structured the
macro incorrectly.....

any help would be very welcome....TIA


Sub aaarrrggghhhh()

Dim SheetName As String
Dim sheetnumber As Long
Dim rightsheet As Long
Dim idnumber As String
Dim activeid As String
Dim xlrow As Long
Dim scennumber As Variant

'this bit finally selects and copies the data (took me ages!)
Sheets("Live History").Select
Sheets("Live History").Range("A1").End(xlDown).Select
Range(ActiveCell, ActiveCell.Offset(0, 20)).Select
Selection.Copy

Sheets("Live History").Select
Sheets("Live History").Range("N1").End(xlDown).Select
ActiveCell.Offset(0, 5).Select
idnumber = ActiveCell.Value
Sheets("Selection").Select

For sheetnumber = 1 To 5

xlrow = 3

SheetName = "S" & Format(sheetnumber, "##0")
Sheets(SheetName).Select

rightsheet = Sheets("Live History").Range("H1").End(xlDown).Value
scennumber = ActiveSheet.Cells(3, 2).Value

'This bit finds the right place to paste (suprised myself)
If rightsheet = scennumber Then
ActiveSheet.Range("BW3").End(xlDown).Select
ActiveCell.Offset(1, 0).Select

End If

Next

End Sub

Copy & Paste values macro...almost there?
 

Thanks for the info Tom... you saw it before i did...

Thanks for modification to the code J.E...

seeya stge