Group cells

[sasa]

Well, to group sheets in excel I select the first sheet in the workbook, hold the Shift key and click the last sheet tab in the workbook.
This way I can enter my data in other sheets but the same cell adresses.
Can someone help me with a macro that group worksheets automatically and allow me to select the cell adresses where I want my data appear om other sheets.

Thanks in advance for any help


Sasa

[p45cal]

I strongly suspect you HAVE to let the VBA do some selecting. In the code below add the names of the sheets you want to change to the list (array) in the first line:
Sheets(Array("Sheet1", "Sheet3")).Select
Range("B17").Select
ActiveCell.FormulaR1C1 = "blah"

[sasa]

I strongly suspect you HAVE to let the VBA do some selecting. In the code below add the names of the sheets you want to change to the list (array) in the first line:
Sheets(Array("Sheet1", "Sheet3")).Select
Range("B17").Select
ActiveCell.FormulaR1C1 = "blah"

Thanks, but this way I can not input my data on Sheet1 and copy these,where I prefer on sheet3

[mdmackillop]

Something like
Option Explicit

Sub MultipleCopy()
Dim r As Range, cel As Range
Dim arr()
Dim sh As Worksheet
Set r = Selection

arr = Array("Sheet1", "Sheet2", "Sheet3")

For Each sh In Sheets(arr)
For Each cel In r
cel.Copy sh.Range(r.Address)
Next
Next
End Sub

[sasa]

Pls, how I have to set the input and output data ?
For example from A1 on sheet1 to B7 on sheet 3 ?

With regard
Sasa

[mdmackillop]

In Sheet3 B7 enter =Sheet1!A1

[sasa]

After an accurate analysis of my problem , I realized that I need another type of Macro.

I need a Macro that can receive in input , always, 8 cells and sort them in another sheet in this order:

For Example: I've selected 8 cells " A1, A2, A3 , A4 , A5, A6, A7, A8" and I want my macro to copy them in another sheet in this order :
x y
1 A1 A2
2 A3 A4
3 A5 A6
4 A7 A8

It would be great if I could select more the one row, so 16 or 24 or 32 cells and so on, and sort the data just under the other already sorted.

[mdmackillop]

Sub Transfer()
Dim r As Range, cel As Range
Dim i As Long
Set r = Selection
i = 1
For Each cel In r
i = i + 1
Sheets(2).Cells(i \ 2, (i Mod 2) + 1) = cel
Next
End Sub

[sasa]

Can you give me another little help, to realize a new order of the list ?

For Example: I've selected 8 cells " A1, A2, A3 , A4 , A5, A6, A7, A8" and I want my macro to copy them in another sheet in this new order :
x y
1 A1 A5
2 A2 A6
3 A3 A7
4 A4 A8

It would be great if I could select more the one row, so 16 or 24 or 32 cells and so on, and sort the data just under the other already sorted.


Local Time: 03:24 AM
Local Date: 01-17-2012
Location: http://www.vbaexpress.com/forum/images/flags/Alderney%203D.gif http://www.vbaexpress.com/forum/images/flags/provinces/Noord-Brabant%203D.gif

http://www.vbaexpress.com/forum/images/buttons/quote.gif (http://www.vbaexpress.com/forum/newreply.php?do=newreply&p=258104)

[mdmackillop]

The purpose of this site is provide assistance, not to do your work. Let us see your own best attempt at a solution.

[sasa]

Sub Transfer1()
Dim r As Range, cel As Range
Dim i As Long
Dim cellCount As Long
Dim myRow As Long
Dim myCell As Long
Set r = Selection
cellCount = r.Count
i = 0
For Each cel In r
i = i + 1
myRow = (i Mod (Int(cellCount / 2)))
If myRow = 0 Then myRow = Int(cellCount / 2)
myColumn = Int((i - 1) / Int(cellCount / 2)) + 1
Sheets(2).Cells(myRow, myColumn) = cel
Next
End Sub

ok....
but how to group four by four the way to have this output:

x y
1 A1 A5
2 A2 A6
3 A3 A7
4 A4 A8

5 A9 A13

6 A10 A14

7 A11 A14

8 A12 A15

and so on

Thanks

[mdmackillop]

Sub Transfer1()
Dim r As Range, Rw As Long, Col As Long
Set r = Selection
For Each cel In r
i = i + 1
Rw = 1 + ((i - 1) Mod 4) + ((i - 1) \ 8) * 4
Col = 1 + ((i - 1) \ 4) Mod 2
Sheets(1).Cells(Rw, Col) = cel
Next
End Sub

[p45cal]

A flexible solution will involve supplying:
1. The source (single column assumed).
2. The destination region (or its top left cell) including sheet info.
3. How high in numbers of rows you want each grouping. (You said 4)
4. How wide in numbers of columns you want each grouping (you said 4, but you showed 2!)
Sub Transfer1()
Dim r As Range, DestTopLeft As Range
Dim ClusterHeight As Long, DestWidth As Long, rw As Long, colm As Long, DestRow As Long

'The 4 starting data:
Set r = Selection
Set DestTopLeft = Sheets("Sheet14").Range("B3") 'the Destination (includes sheet ref.)
ClusterHeight = 4
DestWidth = 2

For rw = 1 To r.Rows.Count Step ClusterHeight
colm = ((rw - 1) / ClusterHeight) Mod DestWidth + 1
DestRow = Int((rw - 1) / (ClusterHeight * DestWidth)) * ClusterHeight + 1
r.Cells(rw, 1).Resize(ClusterHeight).Copy DestTopLeft.Cells(DestRow, colm)
Next rw
End Sub