Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy & paste-special-values data to selected worksheets | Excel Discussion (Misc queries) | |||
Need help with macro to copy, paste, and clear cell values | Excel Discussion (Misc queries) | |||
copy paste values macro | Excel Discussion (Misc queries) | |||
Macro for copy and paste values | Excel Discussion (Misc queries) | |||
Copy range in macro using paste special values | Excel Discussion (Misc queries) |