macro to copy range

[Frank Situmorang]

Hello,

We appreciate if anybody can help me to wrote a macro to copy a formula to a
ong range:

This is my worksheet layout more or less:

A B c
1
2
3
4
5.
..
..
..
..
I want to copy a formula in cell C4 tp range C7 upto C65000

maybe it is simple, but since my background is accountancy so I really do
not know how to make it.

Since the range will be dynamic, but it is difficult to make it, if possible
it prompts us to fill in from C7 to C.......( dots means it prompts us to
fill in celll of column C.

Thanks in advance for any idea.

 

[Rick Rothstein \(MVP - VB\)]

How did you want the formula in C4 copied to C7 before it is filled down...
exactly as written or adjusted for the 3 row offset? The following macro
assumes you wanted it copied exactly as written...

Sub CopyC4ToC7ThenFillDown()
Dim LastCell As Long
LastCell = InputBox("Fill dow to which cell?")
If IsNumeric(LastCell) Then
With Worksheets("Sheet3")
If LastCell > 7 And LastCell <= .Rows.Count Then
.Range("C7").Formula = .Range("C4").Formula
.Range("C7:C" & LastCell).FillDown
Exit Sub
End If
End With
End If
MsgBox "You didn't enter a valid row number"
End Sub

Rick

 

[Frank Situmorang]

Thanks Rick for your help, but could you please help me again, that what I
want to copy is just the formula in cell C4, I do not need copy the rows.
Actually I can do it manulally, but since the row could go down to more than
60,000 lines, so it takes very long eventhough I have pressed pagedown.

When I tried to use this Macro, it also overwrite celll A and B.

Thanks in advance for your help.

 

[Rick Rothstein \(MVP - VB\)]

Just so you know, you still haven't stated exactly what you want to do
clearly enough... you still didn't say if you wanted an exactly copy of the
formula or if you want row references adjusted for the new location (and if
that is what you want, you will have to show us the formula so we can see
its construction), so I am still going to have to guess at what you want.
Are you looking to simply copy the exact formula in C4 into the single cell
in Column C corresponding to the row number you type into the Input Box? If
so, give this macro a try...

Sub CopyC4ToNewRow()
Dim CopyToThisRow As Long
CopyToThisRow = InputBox("Fill dow to which cell?")
If IsNumeric(CopyToThisRow) Then
With Worksheets("Sheet3")
If CopyToThisRow > 7 And CopyToThisRow <= .Rows.Count Then
.Cells(CopyToThisRow, "C").Formula = .Range("C4").Formula
Exit Sub
End If
End With
End If
MsgBox "You didn't enter a valid row number"
End Sub

To press you further on this... are you ultimately trying to copy the
formula in C4 into the first blank cell after the last piece of data in
Column C? If so, give this macro a try instead (no question will be asked,
the macro will automatically find the open cell and perform the copy
operation)...

Sub CopyC4ToRowAfterLastDataCell()
Dim LastRow As Long
With Worksheets("Sheet3")
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Cells(LastRow + 1, "C").Formula = .Range("C4").Formula
End With
End Sub

Rick

 

[Frank Situmorang]

Thanks Rick for your help, it works now prefectly. sorry I think I have also
langguange problem to express my difficulty, because in Indonesia we seldom
speak English.

What I want also is if I do it manually the range to which I copied formula
will be highligted ( active), so what I do next is copy value, then I sorted
it because the use of the formula is if it is the same it will be deleted, so
I sorted it by the result of the formula that says " this", all the "this" I
deleted.

My question is how can we make it active, for example if I fill in the last
cell by 100, then c7 to c100 will be active (Highlighted), so that I can copy
that range to the same range, because I want it to be the value not formula
any more, coz I want to sort it manually. I do not need the macro upto here.
I just want to make the range C7 to C100 active ( for example)

Thanks in advance

 

[Rick Rothstein \(MVP - VB\)]

There is nothing wrong with your language skills... it is just you were
leaving out details of what you wanted and that made it difficult to know
how to answer your question. For example, in your last message, you didn't
say which of the two subroutines is the one you decided to use. For the
first one (CopyC4ToNewRow), use this statement...

..Range("C7:C" & CopyToThisRow).Select

If you used the second subroutine (CopyC4ToRowAfterLastDataCell) instead,
then use this statement...

..Range("C7:C" & (LastRow + 1)).Select

Note there is a "dot" in front of each of those statements.

Rick

Thanks Rick for your help, it works now prefectly. sorry I think I have
also
langguange problem to express my difficulty, because in Indonesia we
seldom
speak English.

What I want also is if I do it manually the range to which I copied
formula
will be highligted ( active), so what I do next is copy value, then I
sorted
it because the use of the formula is if it is the same it will be deleted,
so
I sorted it by the result of the formula that says " this", all the "this"
I
deleted.

My question is how can we make it active, for example if I fill in the
last
cell by 100, then c7 to c100 will be active (Highlighted), so that I can
copy
that range to the same range, because I want it to be the value not
formula
any more, coz I want to sort it manually. I do not need the macro upto
here.
I just want to make the range C7 to C100 active ( for example)

Thanks in advance

 

[Frank Situmorang]

Thanks very much Rick, it works great I use 1st subroutine. You are awesome.

Greetings from Jakarta.

 

[Frank Situmorang]

Rick:

This is my macro, but the result is not as what I expected:
Sub hfscopy()
'
' hfscopy Macro
'

Dim CopyToThisRow As Long
CopyToThisRow = InputBox("Isi disini.... sampai cell dari kolunm AI no
berapa?")
If IsNumeric(CopyToThisRow) Then
With Worksheets("PO_Line_text")
If CopyToThisRow > 8 And CopyToThisRow <= .Rows.Count Then
.Range("AI8:AI" & CopyToThisRow).Formula = .Range("AI4").Formula
.Range("AI8:AI" & CopyToThisRow).Select
Exit Sub
End If
End With
End If
MsgBox "Kamu tidak mengisi no cell yang benar"
End Sub

May be there is still something wrong with this statement:
..Range("AI8:AI" & CopyToThisRow).Formula = .Range("AI4").Formula

Actually we need to copy formula in Cell AI4 ( sorry the actual cell is AI
not C, just for the sake of simplification) to Ai8 until AI" &
CopyToThisRow). Not to make the formula the same, other wise all cellsAI" &
CopyToThisRow) will be the same.

Could you please help me again?