copying the same cells from many sheets

[t.o.]

hello
how I can copy the same cells from many sheets.
and paste all cells as a table to the new sheet
for exemplar cell A1 from sheets 1,3,5,7, etc... till 500
cell B3 from sheets 2,4,6,8, etc... till 500
and copy to the newsheet to cells B3:B50
Any help appreciated
thx tom

[Otto Moehrbach]

One way:
Write a macro to loop through all the sheets and copy from the pertinent
sheets. That is a simple macro to write. The critical part is to
differentiate between the pertinent sheets and the non-pertinent sheets. In
other words, what sheets to copy from.
If the sheets are named as you say (1,2,3,4,5,etc) and you want to copy
from only the even or odd sheets, that is simple to do. But if you have
other names for the sheets you want to copy from, you would need to come up
with some way to differentiate the sheets. Perhaps some common
characteristic of the name of the sheets. Or the content of the sheets.
Or....etc.
Post back with more detail about what you have. HTH Otto
"t.o." <[email protected]> wrote in message
news:[email protected]...
> hello
> how I can copy the same cells from many sheets.
> and paste all cells as a table to the new sheet
> for exemplar cell A1 from sheets 1,3,5,7, etc... till 500
> cell B3 from sheets 2,4,6,8, etc... till 500
> and copy to the newsheet to cells B3:B50
> Any help appreciated
> thx tom

[t.o.]

Otto Moehrbach napisał(a):
> One way:
> Write a macro to loop through all the sheets and copy from the pertinent
> sheets. That is a simple macro to write. The critical part is to
> differentiate between the pertinent sheets and the non-pertinent sheets. In
> other words, what sheets to copy from.
> If the sheets are named as you say (1,2,3,4,5,etc) and you want to copy
> from only the even or odd sheets, that is simple to do. But if you have
> other names for the sheets you want to copy from, you would need to come up
> with some way to differentiate the sheets. Perhaps some common
> characteristic of the name of the sheets. Or the content of the sheets.
> Or....etc.
> Post back with more detail about what you have. HTH Otto
> "t.o." <[email protected]> wrote in message
> news:[email protected]...
>
>>hello
>>how I can copy the same cells from many sheets.
>>and paste all cells as a table to the new sheet
>>for exemplar cell A1 from sheets 1,3,5,7, etc... till 500
>>cell B3 from sheets 2,4,6,8, etc... till 500
>>and copy to the newsheet to cells B3:B50
>>Any help appreciated
>>thx tom
>
>
>
thanks for Your advise
My sheets are named as you wrote in a successive order from 1 till 500
how the loop will be look like ?
tom

[Otto Moehrbach]

Tom
Here is a macro that does what I hope you want. You said to paste the
data (some 500 cells worth) into B3:B50. That's not going to work. I
therefore made the assumption that you wanted the A1 cell contents from each
odd-named sheet to go into column A of the New Sheet starting with A3, and
the B3 value of each even-named sheet to Column B of the New Sheet. If this
is not right, post back and clarify what you want where.
As written, this macro will exclude the sheet named "New Sheet". All
other sheets in the file MUST be named an odd or even number or an error
will result. Post back if you need other sheets excluded as well.
HTH Otto
Sub CopyCells()
Dim ws As Worksheet
Dim Dest As Range
Dim DestCol As Long
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "New Sheet" Then GoTo NextSheet
If CDbl(ws.Name) Mod 2 = 0 Then 'Even
DestCol = 2 'Column B
Else 'Odd
DestCol = 1 'Column A
End If
With Sheets("New Sheet")
If IsEmpty(.Cells(3, DestCol)) Then
Set Dest = .Cells(3, DestCol)
Else
Set Dest = .Cells(Rows.Count, DestCol).End(xlUp).Offset(1)
End If
End With
If DestCol = 2 Then
ws.Range("B3").Copy Dest
Else
ws.Range("A1").Copy Dest
End If
NextSheet:
Next ws
End Sub

"t.o." <[email protected]> wrote in message
news:[email protected]...
> Otto Moehrbach napisał(a):
>> One way:
>> Write a macro to loop through all the sheets and copy from the pertinent
>> sheets. That is a simple macro to write. The critical part is to
>> differentiate between the pertinent sheets and the non-pertinent sheets.
>> In other words, what sheets to copy from.
>> If the sheets are named as you say (1,2,3,4,5,etc) and you want to
>> copy from only the even or odd sheets, that is simple to do. But if you
>> have other names for the sheets you want to copy from, you would need to
>> come up with some way to differentiate the sheets. Perhaps some common
>> characteristic of the name of the sheets. Or the content of the sheets.
>> Or....etc.
>> Post back with more detail about what you have. HTH Otto
>> "t.o." <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>hello
>>>how I can copy the same cells from many sheets.
>>>and paste all cells as a table to the new sheet
>>>for exemplar cell A1 from sheets 1,3,5,7, etc... till 500
>>>cell B3 from sheets 2,4,6,8, etc... till 500
>>>and copy to the newsheet to cells B3:B50
>>>Any help appreciated
>>>thx tom
>>
>>
>>
> thanks for Your advise
> My sheets are named as you wrote in a successive order from 1 till 500
> how the loop will be look like ?
> tom