VBA help is probably the best place to look (look for the Copy method and
SpecialCells).
The basic format is
Source.Copy Destination
where Source and Destination are range objects. For Source, the range is
first restricted to Column A (by specifying Columns(1)). Within this column
you only want particular cells (I guessed numeric constants -see help for
other options for SpecialCells). So SpecialCells returns a range of numeric
constants in Column A. This is copied to the upper left corner of the
destination, cell A1 of Sheet2.
One note I should add. Worksheets actually have two names. The name you
see on the tab and the code name. I habitually use the Code Name for a
worksheet because the tab name can change often (requiring you to edit your
VBA code -the code name can also be changed, but it requires more intent).
The code name and tab name can easily be different. The difference in syntax
(say sheet 2's tab name was changed to test) is:
Sheets("Test").Range("A1")
Sheet2.Range("A1")
If you were to hold down the control key and use the mouse to select the
numeric constants in Column A, then hit copy, go to another sheet (cell A1)
and hit paste -you'll get the same results.
And, if it is possible the source worksheet may not be the activesheet when
the copying is performed, you can qualify the source range further by
preceding it with the worksheet (again, I'm using code names)
Sheet1.Columns(1).SpecialCells(xlCellTypeConstants, _
xlNumbers).Copy Sheet2.Range("A1")
Thanks for the feedback. I'm glad the suggestion is in the right direction.
C Brandt said:
I would like to thank the group for their responses, expecially JMB who gave
me the solution that was needed to solve that particular problem and Gord
for showing me the error in my application.
Where can I find a write-up on the code that was used?
Thanks again,
Craig
Gord Dibben said:
Craig
Should read like this
Columns(1).SpecialCells(xlCellTypeConstants, _
xlNumbers).Copy Sheet2.Range("A1")
The _ is a line continuation mark and is used to place one long line on two
lines.
Note there is a space before the _
Gord Dibben MS Excel MVP
I am clearly way over my head with this, but that is my usual mode of
operation. Ask the experts, pick up a few key ideas, then spend some time
with documentation to figure out what it is they said.
I tried your code as presented and it will not run. It gives me a compile
error [Invalid Character] and points to the underline.
Columns(1).SpecialCells(xlCellTypeConstants,_xlNumbers).Copy
Sheet2.Range("A1")
I looked in the documentation that I have on-hand and it is of no help. Is
there a site on-line that may be of help.
Thanks Again,
Craig
When you say data -do you mean numeric constants or both numeric and text?
Maybe you could use the SpecialCells method. As an example this line of
code
would copy numeric constants from Column A of the activesheet to Sheet2.
Sub test()
Columns(1).SpecialCells(xlCellTypeConstants, _
xlNumbers).Copy Sheet2.Range("A1")
End Sub
Remove the second argument (xlnumbers) if you want both numeric and text
constants.
:
I create a column of formulas, 9600 cells long, in which some result in
a
text entry. I then copy this column and Paste Special / Value, then
sort,
descending, moving the cells with data to the top. I then manually
select
only the cells with data and copy them elsewhere.
I have been able to create a macro that does all the above, except
select
only the cells with data. How does one select the part of a column that
has
data?
Once I learn this trick, I can do a lot of automating of the operation
of
the spreadsheet.
I am using Excel 2000 in Windows 2000
Any assistance would be appreciated,
Thanks, in advance,
Craig
