copying text using cells(R,C) function

[pallaver]

I am trying to copy text from one sheet to another using the Cells
function.
Originally I tried .Value, but I think that only works for numbers,
not letters/words.

That being said, here is what I have below.


Sheets("Sheet1").Cells(ItemRow, ItemColumn).Select
Selection.Copy
Sheets("Sheet2").Cells(SokuteiItemRow, SokuteiItemColumn).Select
ActiveSheet.Paste

Yet when I run the program, I get a debugging error and the third row
of the above code is highlighted.

Where am I going wrong? Is there an easy way to do the code below
which works with numbers, with letters?

Sheets("Sheet2").Cells(SokuteiItemRow, SokuteiItemColumn).Value =
Sheets("Sheet1").Cells(ItemRow, ItemColumn).Value

Thanks. Neil

 

[Joel]

Value will work with number and text. Your code should work as long as the
variables are set to valid Positive numbers and the sheet names are valid.
Also the code you posted is on two lines. either make sure it si one line in
your code or put a continuation character at the end of the line ( _ )

Sheets("Sheet2").Cells(SokuteiItemRow, SokuteiItemColumn).Value = _
Sheets("Sheet1").Cells(ItemRow, ItemColumn).Value

or

Sheets("Sheet1").Cells(ItemRow, ItemColumn).Copy _
destination:=Sheets("Sheet2").Cells(SokuteiItemRow, SokuteiItemColumn)

or

Sheets("Sheet1").Cells(ItemRow, ItemColumn).Copy
Sheets("Sheet2").Cells(SokuteiItemRow, SokuteiItemColumn).paste

or

Sheets("Sheet1").Cells(ItemRow, ItemColumn).Copy
Sheets("Sheet2").Cells(SokuteiItemRow, SokuteiItemColumn).pastespecial _
paste:=xlPasteFormats



The copy method will copy the cell formates along with the data as if you
did the copy on tghe worksheet. Using the equal sign will only copy the
value without formats. The equal sign doesn't always work depending on the
shape of the source and destination data ranges. You can also use
Pastespecial like on the worksheet.

There is no reason to use Select. The macro recorded generates code using
the select method but it is unecessary in most cases.

 

[pallaver]

Hi Joel,

Thanks, not sure what was up, but got it to work. The statement below
though will be very helpful in the future.
Now I'm off to figuring out how to get text from a worksheet in a text
box through searches.
Thanks for your help, slowly but surely plugging and chugging along.

N

--- JOEL WROTE ---
The copy method will copy the cell formates along with the data as if
you
did the copy on tghe worksheet.  Using the equal sign will only copy
the
value without formats.  The equal sign doesn't always work depending
on the
shape of the source and destination data ranges.  You can also use
Pastespecial like on the worksheet.

 

[pallaver]

I do still have a problem though .... I used values and it works
fine. I tried copy/paste method in order to keep formatting, and I
get an error.

I have the following code below which works if you put .Value but not
for .Copy or .Paste. Since it works with .Value, it makes me think
all the variables are properly dimensioned etc., but there's something
else that's goofy.... Any idea why/what?

WORKS (All on one line)
Sheets("Sheet2").Cells(SokuteiItemRow + (NumberingTeishutsuSheet - 1),
SokuteiItemColumn + 5).Value = Sheets("Sheet1").Cells(TemporaryRow,
ColumnSearch).Value

DOESN'T WORK (On two lines)
Sheets("Sheet1").Cells(TemporaryRow, ColumnSearch).Copy
Sheets("Sheet2").Cells(SokuteiItemRow + (NumberingTeishutsuSheet - 1),
SokuteiItemColumn + 5).Paste


Thankx. NP

 

[Joel]

If you aen't getting compiler errors then your variables aren't worksheet
numbers. Numbers have to be positive (not 0), columns < 256, rows <65536.

I usuallyt place break points and step through the code to find problems.

F8 - Step
F9 - set Break Point
F5 - run code

You can move the mouse over differrent variables to see there values as you
step through the code.

You can add a watch window on different variables by highlighting the
variable then right click mouse and select Watch. do this for as many
variables as necessary.

You can also use debug statements to see the variable in the immediate window

debug.print SokuteiItemRow '<=add statement to code for debugging