copy active cell within a page

[superik84]

Hi,

I am new to macros and trying to copy my active cell to C3 and so on..

I am trying for hours, unsuccesfull!

Can anyone help me?

All I want is to Copy-Paste my active cell, and return to my point of orrigin, so I can copy-paste the next one..

Thanks!

 

[ClulessChris]

Range("C3") = ActiveCell

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[superik84]

thanks!

looks simple.. Couldn't figure it out though..

 

[superik84]

PROBLEM!

when the information in the previously active cell changes,

the Range("C3") doesn't change with it!

Can I change this?

Thanks!

Erik

 

[ck1999]

when using this in vba
this changes cell "c3" to the value of the activecell. This is one instance in time. SO if activecell = 300 then c3=300 until you run the code again it stays at 300.

can you explain what you are trying to do? Do you want a formula in "C3"

ck1990

 

[xlbo]

You need to add the code to the wqorksheet CHANGE event

Search the forum for many many posts in this area

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[superik84]

I have a 250 sheet database and want a summary of each in the C-row and D-row (totals)of every sheet. When I make a change in my sheet, I would like to be abled to instantly see this in the C,D-rows.

The first tip works but doesn't change along. Not a huge problem, but with future changes, it might be confenient for it to do so.

thanks so much so far!!

Erik

 

[ck1999]

Do your c and d columns have formulas or are you copying and pasting these summaries?

ck1999

 

[superik84]

copy pasting at the moment.

The information is allready on the sheet, just not where I want it.

does this require a formula, and if so, How would I do this, within the macro?

Erik

 

[ck1999]

where are the values you want in col c and d located?

In col type =g40 for example if g40 contains the value you want in col c

ck1999

 

[superik84]

I tried to put it online but didn't succeed.

anyway, What I have right now is sheet 1:

H50 - I55, AL48 - AM54, AR48 - AS54 and so on..

This info is now given in C and D, so:

H50 --> C11, I50 --> D11 AL48 --> C12, AM48 --> D12
H51 --> C18, I51 --> D18 Al49 --> C19, AM49 --> D19
H52 --> C26, I51 --> D26 and so on, and so on
and so on..

However, on the next sheet the info of (H50 - I55, AL48 - AM54, AR48 - AS54 and so on..) might be in H65 - I70 etc, but still has to appear in de same C, D-rows.

As it is doing right now. but preferably adaptive..

 

[ck1999]

So if you put in
c11 =h40
c18 =h51
c26 =h52

etc

what do you get

ck1999

 

[superik84]

that wouldn't be helpfull. There are 250 variable sheets. Right nog I have the following Macro for C:

I have 7 macro's for different tables. these 7 can be used on al sheets for both C and D. putting it in manually would take ages! right?

or can I insert this into VBA?

erik

invulling tabel ritten


ActiveCell.Offset(-6, 0).Range("A1").Select

Range("C11") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("C18") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("C25") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("C32") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("C39") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("C46") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

 

[ck1999]

Like I said before you can do this however your code needs to run each time

If you have 7 codes that work for all the spreadsheets you may want to look at changing your code to
Range("c3").Formula = "=$H$11"

and then run the code on each sheet. So you only have to update 7 codes and then run them. This way it will update and you will not have to keep running a macro on each change.

ck1999

 

[superik84]

this works fine for the sheet I'm currently working with. However, the next sheet has the needed figures in for example H58 (C4) and the next sheet H31 (C4)... So I can't create a fixed H value right?

 

[ck1999]

Does the code you posted at 9:20 work currently for you? on all the sheets?

What did you mean by:
I have 7 macro's for different tables. these 7 can be used on al sheets for both C and D.

Can you post 1 complete macro

ck1999

 

[superik84]

Sub macro()

' naam

'

ActiveCell.Offset(2, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "max"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "min"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "verschil"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "DRU's"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "uurblok start"

ActiveCell.Offset(2, 0).Range("A1").Select


'locatie vanaf onderste halte

ActiveCell.Offset(-1, 0).Range("A1").Select

' verklaren integer

Dim I As Integer

I = 0

'loop

Do

ActiveCell.Offset(-5, 1).Range("A1").Select

' maximum tijd
'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=R[-2]C"


'celeigenschap
Selection.NumberFormat = "h:mm"
'cel 1 naar beneden
ActiveCell.Offset(1, 0).Range("A1").Select
'
' minimum tijd
'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=MIN(R2C:R3C)"
Selection.NumberFormat = "h:mm"
ActiveCell.Offset(1, 0).Range("A1").Select

'
' verschil tijd
'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=IF(R[-2]C>R[-1]C,R[-2]C-R[-1]C,RC[-1])"
Selection.NumberFormat = "h:mm"
ActiveCell.Offset(1, 0).Range("A1").Select

'
' van tijd naar dru
'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=HOUR(R[-1]C)+MINUTE(R[-1]C)/60"
Selection.NumberFormat = "0.00"
ActiveCell.Offset(1, 0).Range("A1").Select

'
' tijdsbl Macro
'
'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=IF(HOUR(R[-3]C)=0,0,HOUR(R[-3]C))"
Selection.NumberFormat = "0"
ActiveCell.Offset(1, 0).Range("A1").Select

'
' tellen cellen
'

I = I + 1

'loop voorwaarde 

Loop Until IsEmpty(ActiveCell.Offset(-7, 1))

' waarde van i

ActiveCell.Offset(1, 1 - ).Range("A1").Select
ActiveCell.FormulaR1C1 =
ActiveCell.Offset(1, 0).Range("A1").Select

' som berekend totaal DRU's

Dim A As Double
Dim B As Double

A = 0: B = I

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C[" & A & "]:R[-4]C[" & B & "])"

' kolomnamen

ActiveCell.Offset(-1, -1).Range("A1").Select

ActiveCell.FormulaR1C1 = "totaal ritten"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "totaal DRU's"

ActiveCell.Offset(3, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "00:00 - 06:00"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "06:00 - 09:00"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "09:00 - 12:00"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "12:00 - 15:00"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "15:00 - 18:00"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "18:00 - 24:00"

ActiveCell.Offset(1, 0).Range("A1").Select

Selection.Font.Bold = True

ActiveCell.FormulaR1C1 = "totaal"

ActiveCell.Offset(-7, 2).Range("A1").Select

Selection.Font.Bold = True

ActiveCell.FormulaR1C1 = "RITTEN"

ActiveCell.Offset(0, 1).Range("A1").Select

Selection.Font.Bold = True

ActiveCell.FormulaR1C1 = "DRU's"


' klaar zetten basis


ActiveCell.Offset(1, -1).Range("A1").Select


' berekenen tijden

Dim F As Double
Dim G As Double

F = -1: G = I

ActiveCell.FormulaR1C1 = "=COUNTIF(R[-6]C[" & F & "]:R[-6]C[" & G & "],""<=5"")-COUNTIF(R[-6]C[" & F & "]:R[-6]C[" & G & "],""<0"")"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = _
"=COUNTIF(R[-7]C[" & F & "]:R[-7]C[" & G & "],""<=8"")-COUNTIF(R[-7]C[" & F & "]:R[-7]C[" & G & "],""<=5"")"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = _
"=COUNTIF(R[-8]C[" & F & "]:R[-8]C[" & G & "],""<=11"")-COUNTIF(R[-8]C[" & F & "]:R[-8]C[" & G & "],""<=8"")"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = _
"=COUNTIF(R[-9]C[" & F & "]:R[-9]C[" & G & "],""<=14"")-COUNTIF(R[-9]C[" & F & "]:R[-9]C[" & G & "],""<=11"")"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = _
"=COUNTIF(R[-10]C[" & F & "]:R[-10]C[" & G & "],""<=17"")-COUNTIF(R[-10]C[" & F & "]:R[-10]C[" & G & "],""<=14"")"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = _
"=COUNTIF(R[-11]C[" & F & "]:R[-11]C[" & G & "],""<=23"")-COUNTIF(R[-11]C[" & F & "]:R[-11]C[" & G & "],""<=17"")"



' berekenen DRU's

ActiveCell.Offset(-5, 1).Range("A1").Select

Dim H As Double
Dim J As Double

H = -2: J = I - 3

ActiveCell.FormulaR1C1 = _
"=SUMIF(R[-6]C[" & H & "]:R[-6]C[" & J & "],"">=0"",R[-7]C[" & H & "]:R[-7]C[" & J & "])-SUMIF(R[-6]C[" & H & "]:R[-6]C[" & J & "],"">=6"",R[-7]C[" & H & "]:R[-7]C[" & J & "])"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = _
"=SUMIF(R[-7]C[" & H & "]:R[-7]C[" & J & "],"">=6"",R[-8]C[" & H & "]:R[-8]C[" & J & "])-SUMIF(R[-7]C[" & H & "]:R[-7]C[" & J & "],"">=9"",R[-8]C[" & H & "]:R[-8]C[" & J & "])"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = _
"=SUMIF(R[-8]C[" & H & "]:R[-8]C[" & J & "],"">=9"",R[-9]C[" & H & "]:R[-9]C[" & J & "])-SUMIF(R[-8]C[" & H & "]:R[-8]C[" & J & "],"">=12"",R[-9]C[" & H & "]:R[-9]C[" & J & "])"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = _
"=SUMIF(R[-9]C[" & H & "]:R[-9]C[" & J & "],"">=12"",R[-10]C[" & H & "]:R[-10]C[" & J & "])-SUMIF(R[-9]C[" & H & "]:R[-9]C[" & J & "],"">=15"",R[-10]C[" & H & "]:R[-10]C[" & J & "])"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = _
"=SUMIF(R[-10]C[" & H & "]:R[-10]C[" & J & "],"">=15"",R[-11]C[" & H & "]:R[-11]C[" & J & "])-SUMIF(R[-10]C[" & H & "]:R[-10]C[" & J & "],"">=18"",R[-11]C[" & H & "]:R[-11]C[" & J & "])"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = _
"=SUMIF(R[-11]C[" & H & "]:R[-11]C[" & J & "],"">=18"",R[-12]C[" & H & "]:R[-12]C[" & J & "])-SUMIF(R[-11]C[" & H & "]:R[-11]C[" & J & "],"">24"",R[-12]C[" & H & "]:R[-12]C[" & J & "])"

ActiveCell.Offset(1, 0).Range("A1").Select

'invullen totalen

ActiveCell.FormulaR1C1 = "=R[-9]C[-2]"

ActiveCell.Offset(0, -1).Range("A1").Select

ActiveCell.FormulaR1C1 = "=R[-10]C[-1]"


' invulling tabel ritten


ActiveCell.Offset(-6, 0).Range("A1").Select

Range("C11") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("C18") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("C25") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("C32") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("C39") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("C46") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select


' invulling tabel DRU's


ActiveCell.Offset(-6, 1).Range("A1").Select

Range("D11") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("D18") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("D25") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("D32") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("D39") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select

Range("D46") = ActiveCell

ActiveCell.Offset(1, 0).Range("A1").Select


End Sub

 

[superik84]

I coppied this macro since I need to use information from a different table for C5,12,19,etc and D5,12,19,etc where this one is used for C4,11,18,etc and D4,11,18,etc.

the tables are different, variating from 1 to 7 per sheet.

they all have different sizes and therefore I can't use $H$..

Is there a way for putting the file online for free? I find it very hard to explain.

Erik

 

[ck1999]

try changing to this

ActiveCell.Offset(-6, 1).Range("A1").Select

Range("D11").Formula = "=" & ActiveCell.AddressLocal

ActiveCell.Offset(1, 0).Range("A1").Select

Range("D18").Formula = "=" & ActiveCell.AddressLocal

ActiveCell.Offset(1, 0).Range("A1").Select

Range("D25").Formula = "=" & ActiveCell.AddressLocal

ActiveCell.Offset(1, 0).Range("A1").Select

Range("D32").Formula = "=" & ActiveCell.AddressLocal

ActiveCell.Offset(1, 0).Range("A1").Select

Range("D39").Formula = "=" & ActiveCell.AddressLocal

ActiveCell.Offset(1, 0).Range("A1").Select

Range("D46").Formula = "=" & ActiveCell.AddressLocal

ActiveCell.Offset(1, 0).Range("A1").Select

ck1999

 

[superik84]

YES!

that's perfect! thanks so much!

However, this brings me to the next step.. getting this info into one huge sheet. But I will post a new topic on this tomorrow morning!

thanks so much!

Erik