Thread
:
Copy & Paste values macro...almost there?
View Single Post
#
5
Posted to microsoft.public.excel.programming
ste mac
external usenet poster
Posts: 117
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
Reply With Quote
ste mac
View Public Profile
Find all posts by ste mac