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!

 

[ck1999]

This will be alot easier or should

new workskeet name is say "summary"

then code
counter = 1 ' to start in col 1
vcol = 1
dim sh as worksheet
for each sh in worksheets
sheets("summary").range("a1").name
sheets("summary").cells(counter,vcol) = sh.range("c11").value
sheets("summary").cells(counter,vcol+1) = sh.range("d11").value
counter = counter+2
vcol = vcol+1
next sh

try something like this.

ck1999

 

[superik84]

The information on each sheet is as follows.

What I want to create is 1 sheet with a row in which most of this information is summarized, starting with, 11, next collumn, a, next collumn MV basis Ritten, next collumn MV basis DRU's, etc.

Nince I roughly have 250 sheets, I would like to use a macro for it.

11
a RITTEN DRU's
GGD Stad
totaal MV basis 84,00 40,33
MV kort 63,00 30,42
MV zomer - -
MV vak - -
Z basis+kort 63,00 27,92
Z zomer 46,00 20,67
Z&F dagen 34,00 14,73

0 - 6 uur
MV basis 2,00 0,87
MV kort 2,00 0,87
MV zomer
MV vak
Z basis+kort 1,00 0,43
Z zomer 1,00 0,43
Z&F dagen 1,00 0,43

6 - 9 uur
MV basis 15,00 7,20
MV kort 12,00 5,65
MV zomer
MV vak
Z basis+kort 7,00 2,97
Z zomer 7,00 2,97
Z&F dagen 3,00 1,30
9-12 uur
MV basis 18,00 8,45
MV kort 12,00 6,00
MV zomer
MV vak
Z basis+kort 12,00 5,27
Z zomer 9,00 4,15
Z&F dagen 6,00 2,60

12 - 15 uur
MV basis 18,00 8,85
MV kort 12,00 6,00
MV zomer
MV vak
Z basis+kort 17,00 7,67
Z zomer 9,00 4,20
Z&F dagen 6,00 2,60

15 - 18 uur
MV basis 18,00 9,00
MV kort 12,00 6,00
MV zomer
MV vak
Z basis+kort 14,00 6,38
Z zomer 8,00 3,72
Z&F dagen 6,00 2,60
18 - 24 uur
MV basis 13,00 5,97
MV kort 13,00 5,90
MV zomer
MV vak
Z basis+kort 12,00 5,20
Z zomer 12,00 5,20
Z&F dagen 12,00 5,20


thanks,

Erik

 

[ck1999]

Do you want the totals of say "c11" in all the worksheets to be on the "summary" sheet?

If so look at my last post

ck1999

 

[superik84]

I tried your last post but can't seem to make it work.. It says it doesn't know

sheets("summary").range("a1").name

did I do something wrong with this?

I really don't know where to start with a macro like this!

Erik

 

[ck1999]

Did you create a sheet name "summary"? What is the sheet nam e for your summary sheet?

ck1999

 

[superik84]

I created a sheet summary and right now am using the following:

Sub Macro1()
'
' Macro1 Macro

counter = 2
vcol = 1
Dim sh As Worksheet
For Each sh In Worksheets

Sheets("summary").Cells(counter, vcol + 1) = sh.Range("A1").Value
Sheets("summary").Cells(counter, vcol + 2) = sh.Range("A2").Value
Sheets("summary").Cells(counter, vcol + 4) = sh.Range("C4").Value
Sheets("summary").Cells(counter, vcol + 5) = sh.Range("C5").Value
Sheets("summary").Cells(counter, vcol + 6) = sh.Range("C6").Value
Sheets("summary").Cells(counter, vcol + 7) = sh.Range("C7").Value
Sheets("summary").Cells(counter, vcol + 8) = sh.Range("C8").Value
Sheets("summary").Cells(counter, vcol + 9) = sh.Range("C9").Value
Sheets("summary").Cells(counter, vcol + 10) = sh.Range("C10").Value
Sheets("summary").Cells(counter, vcol + 11) = sh.Range("D4").Value
Sheets("summary").Cells(counter, vcol + 12) = sh.Range("D5").Value
Sheets("summary").Cells(counter, vcol + 13) = sh.Range("D6").Value

counter = counter + 1

Next sh


End Sub




This works fine but has a few problems,

first, it doesn't update when sheetinfo is changed,
second, it starts in row 6 instead of the preferred row 4,
third, it gives info uncalled for (as far as I know) in row 2-4

thanks!

E

 

[ck1999]

1.
Replace these formulas with this formula format

Range("a1").Formula = "=" & sh.name & "!" & Sheets(sh).Range("c1").Address

2.
Delete these if you do not want this information
Sheets("summary").Cells(counter, vcol + 1) = sh.Range("A1").Value
Sheets("summary").Cells(counter, vcol + 2) = sh.Range("A2").Value

if you want this information but in another row change the 1 and 2 respectively until its in the correct row

ck1999

 

[superik84]

first step gives an error 13..

types do not compute(?translation?)

' Macro1 Macro

counter = 2
vcol = 1
Dim sh As Worksheet
For Each sh In Worksheets

Range("a1").Formula = "=" & Summary & "!" & Sheets(sh).Range("c1").Address

counter = counter + 1

Next sh


End Sub




2. The information I want, I get. However, when I enlarge the macro, it starts giving extra information in the first rows!

this marcostuff is really hard! thanks so much for helping me out!

E

 

[ck1999]

ignore my last post and try this

Sub Macro1()
'
' Macro1 Macro

counter = 6
vcol = 1
Dim sh As Worksheet
For Each sh In Worksheets
if sh.name <> "summary" then
   Sheets("summary").Cells(counter, vcol + 1).Formula = "=" & sh.name & "!" & Sheets(sh).Range("a1").Address
   Sheets("summary").Cells(counter, vcol + 2).Formula = "=" & sh.name & "!" & Sheets(sh).Range("a2").Address
   Sheets("summary").Cells(counter, vcol + 4).Formula = "=" & sh.name & "!" & Sheets(sh).Range("c4").Address
   Sheets("summary").Cells(counter, vcol + 5).Formula = "=" & sh.name & "!" & Sheets(sh).Range("c5").Address
   Sheets("summary").Cells(counter, vcol + 6).Formula = "=" & sh.name & "!" & Sheets(sh).Range("c6").Address
   Sheets("summary").Cells(counter, vcol + 7).Formula = "=" & sh.name & "!" & Sheets(sh).Range("c7").Address
   Sheets("summary").Cells(counter, vcol + 8).Formula = "=" & sh.name & "!" & Sheets(sh).Range("c8").Address
   Sheets("summary").Cells(counter, vcol + 9).Formula = "=" & sh.name & "!" & Sheets(sh).Range("c9").Address
   Sheets("summary").Cells(counter, vcol + 10).Formula = "=" & sh.name & "!" & Sheets(sh).Range("c10").Address
   Sheets("summary").Cells(counter, vcol + 11).Formula = "=" & sh.name & "!" & Sheets(sh).Range("d4").Address
   Sheets("summary").Cells(counter, vcol + 11).Formula = "=" & sh.name & "!" & Sheets(sh).Range("d5").Address
   Sheets("summary").Cells(counter, vcol + 11).Formula = "=" & sh.name & "!" & Sheets(sh).Range("d6").Address
   
   counter = counter + 1
end if   
Next sh

    
End Sub

If you have any blank workbooks then you will get blank rows

try this code

ck1999

 

[superik84]

it gives the same error 13..

Do I need to fill anything in or can I just copy paste it?

 

[ck1999]

should just copy and paste

Where does the error occur in code? if it does not highlight use f8 and step through the code


if still get error try changing address to addresslocal at each occurance.

ck1999

 

[superik84]

The error occurs on:

Sheets("summary").Cells(counter, vcol + 1).Formula = "=" & sh.name & "!" & Sheets(sh).Range("a1").Address

I changed it into .Adresslocal but get an error.

It says error 13; types do not connect(?)

E

 

[ck1999]

to simplify you can use

Sub Macro1()
'
' Macro1 Macro

counter = 6
vcol = 1
Dim sh As Worksheet
For Each sh In Worksheets
if sh.name <> "summary" then
   Sheets("summary").Cells(counter, vcol + 1).Formula = "=" & sh.name & "!a1"
   Sheets("summary").Cells(counter, vcol + 2).Formula = "=" & sh.name & "!a2"
   Sheets("summary").Cells(counter, vcol + 4).Formula = "=" & sh.name & "!c4"
   Sheets("summary").Cells(counter, vcol + 5).Formula = "=" & sh.name & "!c5"
   Sheets("summary").Cells(counter, vcol + 6).Formula = "=" & sh.name & "!c6"
   Sheets("summary").Cells(counter, vcol + 7).Formula = "=" & sh.name & "!c7"
   Sheets("summary").Cells(counter, vcol + 8).Formula = "=" & sh.name & "!c8"
   Sheets("summary").Cells(counter, vcol + 9).Formula = "=" & sh.name & "!c9"
   Sheets("summary").Cells(counter, vcol + 10).Formula = "=" & sh.name & "!c10"
   Sheets("summary").Cells(counter, vcol + 11).Formula = "=" & sh.name & "!d4"
   Sheets("summary").Cells(counter, vcol + 12).Formula = "=" & sh.name & "!d5"
   Sheets("summary").Cells(counter, vcol + 13).Formula = "=" & sh.name & "!d6"
  
   counter = counter + 1
end if   
Next sh
    
End Sub

replace your procedure with this one.

ck1999

 

[superik84]

never mind for now.. I can work with it like this in the short run..

I have one last and big problem remaining..

my tables look like this:

HaltenaamRitnummers: 7001 7005 a7009 7013
Uithuizen, BusstationV 8:00 10:35
Uithuizen, Engersmastraat 8:01 10:36
Uithuizen, Hunsingoheerd 8:02 10:37
Uithuizen, Snik 8:03 10:37
Uithuizen, J Cohenstraat 8:04 10:38
Uithuizen, Wilgenbos 8:04 10:38
Uithuizen, John F Kennedylaan 8:05 10:39
Lage van de weg, Bovenhuizen 8:06 10:40
Lage van de weg, Streeksterweg 8:08 10:42
Usquert, Weg naar Lutjebos 8:10 10:43
Usquert, Sportpark 8:11 10:44
Usquert, Spoorwegovergang 8:12 10:45
Rottum, 't Lage Eind 8:16 10:49
Kantens, Kolpendestraat 8:18 10:51
Kantens, Oude Gemeentehuis 8:18 10:51
Kantens, Middelstumerweg 8:18 10:51
Middelstum, Weg naar Toornwerd 8:21 10:53
Middelstum, Brouwerslaan 8:22 10:54
Middelstum, Concordiaplein 8:23 9:57 10:55 10:57
Middelstum, Mentheda 8:23 9:57 10:57
Fraamklap, Fraamklap 8:25 9:59 10:59
Middelstum, Stitswerderweg 8:27 10:01 11:01
Onderdendam, Brug 8:28 10:02 11:02
Onderdendam, Kerk 8:28 10:02 11:02
Onderdendam, Bedumerweg 8:29 10:03 11:03
Bedum, Wroetende Mol 8:31 10:05 11:05
Bedum, Zuivelfabriek 8:32 10:06 11:06
Bedum, Wilhelminalaan 8:32 10:06 11:06
Bedum, van Heemskerckstraat 8:33 10:07 11:07
Bedum, Molenweg 8:33 10:07 11:07
Bedum, Schoolstraat 8:34 10:08 11:08
Bedum, Bedumerbos 8:36 10:10 11:10
Ellerhuizen, Brug 8:39 10:13 11:13
Zuidwolde, Plattenburg 8:40 10:14 11:14
Zuidwolde, Noordwolderweg 8:41 10:15 11:15
Zuidwolde, Gemaal 8:44 10:18 11:18
Noorderhoogebrug, Boterdiepsbrug 8:45 10:19 11:19
Groningen, Bedumerweg/Poortstraat 8:47 10:21 11:21
Groningen, Bedumerweg/Floresstraat 8:47 10:21 11:21
Groningen, Bedumerweg/Nw.Ebbingestr 8:48 10:22 11:22
Groningen, Nw Ebbingestr/Noorderplsn 8:49 10:23 11:23
Groningen, Nw Ebbingestr/Brouwerstr 8:49 10:23 11:23
Groningen, Nw Ebbingestr/Korenstraat 8:50 10:24 11:24
Groningen, Oude Ebbingestraat 8:51 10:25 11:25
Groningen, Grote Markt/Stud Soc 8:52 10:26 11:26
Groningen, Gelkingestraat 8:53 10:27 11:27
Groningen, Zuiderdiep 8:54 10:28 11:28
Groningen, Zuiderdiep/Schoolholm 8:55 10:29 11:29
Groningen, Emmaplein 8:56 10:30 11:30
Groningen, Busstation CSA 8:57 10:31 11:31


What I want to do is select the first and last time and put it down in 2 columns. For this I am currently using:

' 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



This being part of a Macro.

However, When the first or last cells of the table are empty, I get an #Value#- mark! How do I select the first cell WITH a time in it?

When I have this right in my macro, I can finish it up! Would be great if you knew what to do!

E

 

[superik84]

the last procedure opens up the window for opening a new file!

 

[ck1999]

Please post this new request in a new thread.

This thread is getting rather long

ck1999

 

[superik84]

your right.

thanks so far!

 

[ck1999]

The last code works fine on my computer excel 2003.

Did you copy and paste?

ck1999