range.copy switches/syntax/uses

[Elemental]

this is a simple question im guessing, but i can't find more info on it.

=================================
rng.Copy Sheets("Results").[A1]
=================================

what would i add/change to the code to make it paste only the values of the selected cells into the results sheet ( these are linked cells being selected and they just paste the first few cells on the linked xls file instead of the data ).

im used to the below code, but the above method is much tidier.

selection.copy
Sheets("Results").Select
selection.pastespecial



i was thinking too that perhaps the forums could be sectioned off into more specific areas than a 12,000 somthing topic Excel Thread. Then i wouldnt need to pester ppl for information that's probably around here somehwere



thanks everybody.

 

[Juan Pablo González]

For the first, you can just use

Range("A1").Value = Sheets("Sheet2").Range("A2").Value

for the second question, two things:

- Look in the "Questions about this board". That has been covered, and answered

- The search function is quite nicely... and it's being improved.

 

[Elemental]

so if a had a range(selection) that had been produced by the user entering a number of rows(random, depending on what the users wants) how does that range idea you mentioned work ?

or can i add that somehow to my range.copy code ?

and for the search, i have tried for weeks to find information on "Method Of Range Class Failed" in the messageboard here

the search engine will produce results as long as all of those words are somewhere in a topic, but not in that exact phrase, so filtering to find it takes ages

 

[Juan Pablo González]

You can use the .Resize method of the Range to "adjust" the range, you can also use .Rows.Count or .Columns.Count to know how many rows or columns does the range has, or you can use just .Count to get the number of cells in the range.

That search criteria, between quotes, i.e. "Method Of Range Class Failed", produced only this post as a result. That's a VERY common error, so, you could try using "Paste Values" instead to see what you get.

 

[Elemental]

ok, so this code is what i am working on ATM :


Private Sub form_submit_Click()
Dim iRow As Integer, datesta As String, datefin As String
Dim Location, rng As Range
datesta = Sheets("Form").[C7]
datefin = Sheets("Form").[C11]
Location = Sheets("Form").[K15]
' Sheets("Results").Protect UserInterfaceOnly:=True 'Just in case
Sheets("Results").Select
Sheets("Results").Cells.Clear
Select Case Location
Case 1
With Sheets("Data")
For iRow = 1 To 100
If .Cells(iRow, "A").Value = datesta Then
.Cells(iRow, "AD").Value = "Select Me"
ElseIf .Cells(iRow, "A").Value = datefin Then
.Cells(iRow, "AD").Value = "Select Me"
End If
Next iRow
Set rng = .[AD5].End(xlDown)
Set rng = .Range(rng, rng.End(xlDown))
Set rng = .Range(rng, rng.End(xlToLeft))
Set rng = .Range(rng, rng.End(xlToLeft))
Set rng = .Range(rng, rng.End(xlToLeft))
rng.Copy Sheets("Results").[A1]
Set rng = .[AD5].End(xlDown)
rng.Value = ""
rng.End(xlDown).Value = ""
End With
Case 2 To 7
Sheets("Results").Select
Case Else
Sheets("Form").Select
End Select
Set rng = Nothing
End Sub


so somthing like

range(rng).value.copy Sheets("Results").[A1]

in place of

rng.Copy Sheets("Results").[A1]

would work ?

 

[Elemental]

is there a way to set the custom formatting of rows and columns to display cells as vales ?

not numbers, but the actual values of a cell