range.copy switches/syntax/uses

Elemental

Board Regular
Joined
Jul 13, 2002
Messages
110
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top