Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
If If is offline
external usenet poster
 
Posts: 2
Default Automation Procedure with Worksheet_Activate

Hello,
I have the code below which becomes too long since I must go to line 200.
I would like to automate this procedure but I really do not see how.

The A6 cell of the "Finale" sheet must take again the data being in B3 on
the sheet "Data"
and the B6 cell of the "Finale" sheet must take again the data being in A3
on the "Data" sheet.

Procedure with cells(i, 1) with "for i =) ?????

Would somebody have a track?
Thank you for your assistance.


Private Sub Worksheet_Activate()
With Sheets("Finale")
..Range("A6") = Sheets("Data").Range("B3")
..Range("B6") = Sheets("Data").Range("A3")
..Range("C6") = Sheets("Data").Range("C3")
..Range("D6") = Sheets("Data").Range("D3")
..Range("E6") = Sheets("Data").Range("E3")
..Range("F6") = Sheets("Data").Range("F3")
..Range("G6") = Sheets("Data").Range("G3")
..Range("H6") = Sheets("Data").Range("H3")
..Range("I6") = Sheets("Data").Range("I3")
..Range("J6") = Sheets("Data").Range("J3")

..Range("C7") = Sheets("Data").Range("C4")
..Range("D7") = Sheets("Data").Range("D4")
..Range("E7") = Sheets("Data").Range("E4")
..Range("F7") = Sheets("Data").Range("F4")
..Range("G7") = Sheets("Data").Range("G4")
..Range("H7") = Sheets("Data").Range("H4")
..Range("I7") = Sheets("Data").Range("I4")
..Range("J7") = Sheets("Data").Range("J4")

- - - - -

End With
End Sub


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27
Default Automation Procedure with Worksheet_Activate

Two ways are possible

1)

Sheets("Finale").Range("A6:J6").Value = _
Sheets("Date").Range("A3:J3").Value

Sheets("Finale").Range("C7:J7").Value = _
Sheets("Date").Range("C4:J4").Value

2)
dim rw as long, cl as long
rw=3
for cl = 1 to 10
Sheets("Finale").Cells(rw+4,cl).Value = _
Sheets("Date").Cells(rw,cl).Value
next
rw = 4
for cl = 3 to 10
Sheets("Finale").Cells(rw+4,cl).Value = _
Sheets("Date").Cells(rw,cl).Value
next


depend sor your needs, but obviously (1) is faster

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Hello,
I have the code below which becomes too long since I

must go to line 200.
I would like to automate this procedure but I really do

not see how.

The A6 cell of the "Finale" sheet must take again the

data being in B3 on
the sheet "Data"
and the B6 cell of the "Finale" sheet must take again

the data being in A3
on the "Data" sheet.

Procedure with cells(i, 1) with "for i =) ?????

Would somebody have a track?
Thank you for your assistance.


Private Sub Worksheet_Activate()
With Sheets("Finale")
..Range("A6") = Sheets("Data").Range("B3")
..Range("B6") = Sheets("Data").Range("A3")
..Range("C6") = Sheets("Data").Range("C3")
..Range("D6") = Sheets("Data").Range("D3")
..Range("E6") = Sheets("Data").Range("E3")
..Range("F6") = Sheets("Data").Range("F3")
..Range("G6") = Sheets("Data").Range("G3")
..Range("H6") = Sheets("Data").Range("H3")
..Range("I6") = Sheets("Data").Range("I3")
..Range("J6") = Sheets("Data").Range("J3")

..Range("C7") = Sheets("Data").Range("C4")
..Range("D7") = Sheets("Data").Range("D4")
..Range("E7") = Sheets("Data").Range("E4")
..Range("F7") = Sheets("Data").Range("F4")
..Range("G7") = Sheets("Data").Range("G4")
..Range("H7") = Sheets("Data").Range("H4")
..Range("I7") = Sheets("Data").Range("I4")
..Range("J7") = Sheets("Data").Range("J4")

- - - - -

End With
End Sub


.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 31
Default Automation Procedure with Worksheet_Activate

Hello Yves

Have I missed something here? This seems to be just
copying block data from one sheet to another albeit in
slightly offset positions

Lets get some data to work with. Enter the following data
in sheet2, row 2 for the heading. Enter a sum Formula in
column F, SUM(A3:E3) and copy it into F4.

Now select the range from A3 to F4 and drag the selection
down to row 20. You should now have a good set of data to
workk with.


Original Data
1 2 3 4 5 15
2 3 4 5 6 20

There are three methods of coping data. We are copying the
values in sheet2 in range from A3 to F17 into sheet1 A6 to
f23

The copy method copies all contents over including
formulas. The same care should be taken in using this
method as in a worksheet. i.e. make sure you do not get a
reference error.

Copy syntax; declare the range to copy, declare the action
(copy) then declare the destination as in:
rng.Copy Target
rng is the range to be copied, copy is the action and
target is the destination of the copied data.

Sub CopyData()
Set rng = Sheets("Sheet2").Range("A3:F20")
Set Target = Worksheets(1).Range("A6")
rng.Copy Target
End Sub

The formula method only copies values. The formula (SUM)
in column F on sheet2 is not copied only the values.

Sub FormData()
Set rng = Sheets("Sheet2").Range("A3:F20")
Set Destination = Sheets("Sheet1").Range("A6:F23")
Destination.Value = rng.Value
End Sub

The for cells method takes a bit longer to perform but is
very flexible and can be used with step values to miss out
rows or columns. Definately worth mastering.

Sub CellsCopy()
Worksheets(1).Select 'or .Activate
For i = 6 To 23 'number of rows
For j = 2 To 7 'number of columns
'1) Destination cell is always shown on the left of
the equation.
'2) This time the data is copied to over a column as
well as three rows down.
'3) See how flexible 'the formula is just adding or as
in this case subtracting values.

Cells(i, j).Value = Worksheets(2).Cells(i - 3, j -
1).Value
Next j 'next column
Next i 'next row
End Sub

The copy cells method can be speeded up by declaring both
i and j as integers. It is usual to refer to the rows as i
and j for the columns. try the following program and
compare with the previous.

Sub CellCopy2()
Dim i As Integer, j As Integer
Worksheets("Sheet1").select
For i = 6 To 23 'number of rows
For j = 2 To 7 'number of columns
Cells(i, j).Value = Worksheets(2).Cells(i - 3, j -
1).Value
Next j 'next column
Next i 'next row
End Sub

You might not notice much difference with these but extend
the rows and columns of data and it becomes more noticable.

Hope this helps.
Regards
Peter

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stop a Procedure from another procedure Ayo Excel Discussion (Misc queries) 1 October 30th 08 02:42 AM
Procedure is too big Frank Situmorang Excel Worksheet Functions 2 May 2nd 07 05:06 AM
VBA Procedure Jeff Excel Discussion (Misc queries) 0 January 20th 06 05:22 PM
Worksheet_Activate not working for multiple cells [email protected] Excel Discussion (Misc queries) 0 December 28th 05 05:15 PM


All times are GMT +1. The time now is 07:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"