copying cells 1

[kenguru]

Hi,

How can i copy one cell formula to another cell, of course continuing the numerotatian.
For ex. sheet(2).cells(100,3) formula is "=F100*10"

and i want that
sheet(2).cells(101.3) formula to be "F101*10"

Thanks you,
Kenguru

 

[PHV]

Have you tried this ?
Sheets(2).Cells(100, 3).Copy Sheets(2).Cells(101, 3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kenguru]

Thanks, it works !

Kenguru

P.S. I didn't tryed this before

 

[kenguru]

How could i preserved the color of the cell?
Because i do in the following way:
a=cell(i,1)
ActiveSheet.Rows(ii).EntireRow.Insert
ThisWorkbook.Sheets(1).Cells(ii, 1) =a

The point is: i have to save the data from i, because after inserting i <> old_i.

 

[PHV]

Perhaps something like this ?
Cells(i, 1).Copy ' Some sheet qualification needed here ?
ActiveSheet.Rows(ii).EntireRow.Insert
With ThisWorkbook.Sheets(1)
.Paste .Cells(ii, 1)
End With
Application.CutCopyMode = False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kenguru]

I got the error:
"Paste method of workbook failes"

 

[PHV]

"Paste method of workbook failes"
I'm confused as I didn't knew that the Workbook object had a Paste method ...

 

[kenguru]

Paste method of worksheet class failed"

Sorry for not writing corectly. I got this at this line:
With ThisWorkbook.Sheets(1)
.Paste .Cells(ii, 1)
End With

Kenguru

 

[PHV]

OOps, sorry, the Insert method seems to clear the CutCopyMode.
As I know nothing about ActiveSheet, ThisWorkbook.Sheets(1), i, ii and old_i I'm afraid I can't help without more info.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kenguru]

Yes you were right, it cleares it

My code is:
'a = ThisWorkbook.Sheets(sheet).Cells(ii, 1)
ThisWorkbook.Sheets(sheet).Cells(ii, 1).Copy
b = ThisWorkbook.Sheets(sheet).Cells(ii, 2)
ActiveSheet.Rows(ii + 1).EntireRow.Insert
ActiveSheet.Rows(ii + 1).NumberFormat = "@"

'ThisWorkbook.Sheets(sheet).Cells(ii + 1, 1) = a
With ThisWorkbook.Sheets(sheet).Paste(ThisWorkbook.Sheets(sheet).Cells(ii + 1, 1))
End With
Application.CutCopyMode = False
ThisWorkbook.Sheets(sheet).Cells(ii + 1, 2) = b

I have to 2*2 columns. And some rows i have to insert it from the second column in the first colum. I work on the same sheet. The second column is "i" and the first, where i insert the data is "ii".

Did i succed making it a little more clearer?
Thank's

 

[PHV]

Something like this ?
With ActiveSheet.Range(Cells(ii, 1), Cells(ii, 2))
.Copy
.Insert Shift:=xlShiftDown
End With
Application.CutCopyMode = False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kenguru]

I can't do it in that way, because i have more variable, only i didn't write it all them, for making it clearer. I have 9*9 columns, and when i make the copy some are made from the "ii" line, and others from "i" lines (the old i):
a = ThisWorkbook.Sheets(sheet).Cells(ii, 1)
b = ThisWorkbook.Sheets(sheet).Cells(ii, 2)
c = ThisWorkbook.Sheets(sheet).Cells(i, 16)
d = ThisWorkbook.Sheets(sheet).Cells(i, 17)
e = ThisWorkbook.Sheets(sheet).Cells(i, 18)
f = ThisWorkbook.Sheets(sheet).Cells(i, 19)
g = ThisWorkbook.Sheets(sheet).Cells(i, 20)
h = ThisWorkbook.Sheets(sheet).Cells(i, 21)
v = ThisWorkbook.Sheets(sheet).Cells(i, 22)
...
ThisWorkbook.Sheets(sheet).Cells(ii, 1) = a
ThisWorkbook.Sheets(sheet).Cells(ii, 2) = b
ThisWorkbook.Sheets(sheet).Cells(ii, 3) = c
ThisWorkbook.Sheets(sheet).Cells(ii, 4) = d
ThisWorkbook.Sheets(sheet).Cells(ii, 5) = e
ThisWorkbook.Sheets(sheet).Cells(ii, 6) = f
ThisWorkbook.Sheets(sheet).Cells(ii, 7) = g
ThisWorkbook.Sheets(sheet).Cells(ii, 8) = h
ThisWorkbook.Sheets(sheet).Cells(ii, 9) = v