I am trying to write a macro which opens a workbook, selects data and
copies it to another workbook. This is to list outstanding items.

My problem is the workbook has various sheet names, the workbook is
updated regularly and I do not have control of the sheet names.

I would like to have something along the lines of

-Open workbook (I can do this)- :)
Select First Sheet (unknown name)
-Select/ Copy/ Paste Information (I can do this)- :)
Select Second Sheet (unknown name)
-etc etc etc-

The next problem is as records are added or removed so the number of
sheets varies. I need something that will start at the first sheet and
continue on to the last without specifically indicating the number of
sheets.

Any help would be greatly appreciated as I do this manually at present


--
Donkin
------------------------------------------------------------------------
Donkin's Profile: http://www.excelforum.com/member.php...o&userid=23715
View this thread: http://www.excelforum.com/showthread...hreadid=482732

Hi Donkin,

You can refer to a sequential sheet by using its index property. Thus the
first sheet will be sheets(1) and the nth sheet will be sheets(n).

One way of iterating though the workbook's worksheets would be:

Dim SH As Worksheet

For Each SH In ActiveWorkbook.Worksheets
'your code
Next SH

If you wish to refer to specific sheets, independently of the sheet's name
or position, try using the sheet's codename property, e.g: Sheet1, Sheet2
etc. For more information see CodeName property in VBA help.


---
Regards,
Norman



"Donkin" wrote in
message ...

I am trying to write a macro which opens a workbook, selects data and
copies it to another workbook. This is to list outstanding items.

My problem is the workbook has various sheet names, the workbook is
updated regularly and I do not have control of the sheet names.

I would like to have something along the lines of

-Open workbook (I can do this)- :)
Select First Sheet (unknown name)
-Select/ Copy/ Paste Information (I can do this)- :)
Select Second Sheet (unknown name)
-etc etc etc-

The next problem is as records are added or removed so the number of
sheets varies. I need something that will start at the first sheet and
continue on to the last without specifically indicating the number of
sheets.

Any help would be greatly appreciated as I do this manually at present


--
Donkin
------------------------------------------------------------------------
Donkin's Profile:
http://www.excelforum.com/member.php...o&userid=23715
View this thread: http://www.excelforum.com/showthread...hreadid=482732

Thanks for the info

Excuse me for being dumb but I tried to use it and it does iterate fo
the number of worksheets but can you review the code and tell me wher
I have gone wrong.

This should open Book1 copy cell a1, paste to Book2 but doesn,t

Sub Macro3()
Range("A1").Select

Dim SH As Worksheet
For Each SH In ActiveWorkbook.Worksheets
Windows("Book1.xls").Activate

Range("a1").Select
Selection.Copy

Windows("Book2").Activate
ActiveSheet.Paste
Application.CutCopyMode = False

Application.Wait TimeSerial(Hour(Now()), Minute(Now())
Second(Now()) + 1)

Windows("Book1.xls").Activate
Next SH

End Sub

Thanks in advanc

--
Donki
-----------------------------------------------------------------------
Donkin's Profile: http://www.excelforum.com/member.php...fo&userid=2371
View this thread: http://www.excelforum.com/showthread.php?threadid=48273

Hi Donkin,

You appear to wish to copy a range from each worksheet in one workbook to a
second workbook (Book2.xls).

You do not, however, indicate which sheet(s) the Book1 ranges should be
copied to.

---
Regards,
Norman



"Donkin" wrote in
message ...

Thanks for the info

Excuse me for being dumb but I tried to use it and it does iterate for
the number of worksheets but can you review the code and tell me where
I have gone wrong.

This should open Book1 copy cell a1, paste to Book2 but doesn,t

Sub Macro3()
Range("A1").Select

Dim SH As Worksheet
For Each SH In ActiveWorkbook.Worksheets
Windows("Book1.xls").Activate

Range("a1").Select
Selection.Copy

Windows("Book2").Activate
ActiveSheet.Paste
Application.CutCopyMode = False

Workbooks("Book1.xls")
Windows("Book1.xls").Activate
Next SH

End Sub

Thanks in advance


--
Donkin
------------------------------------------------------------------------
Donkin's Profile:
http://www.excelforum.com/member.php...o&userid=23715
View this thread: http://www.excelforum.com/showthread...hreadid=482732

What I am trying to do is copy Cell (a1) from each sheet in Book1 to a
list in Book2. The number of sheets in Book1 is variable and the sheet
names change regularly.

Sub Sheet_Select()
Windows("Book1.xls").Activate
Dim SH As Worksheet
For Each SH In ActiveWorkbook.Worksheets
Windows("book1.xls").Activate
Range("a1").Select
Selection.Copy
Windows("Book2.xls").Activate
Set W = Range("a1:a50").End(xlDown)
W.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next SH

End Sub

This will select Cell A1 in book1 and copy to a list in Book2
unfortunately this repeats for the number of sheets in Book1 but does
not cycle through the sheets.


--
Donkin
------------------------------------------------------------------------
Donkin's Profile: http://www.excelforum.com/member.php...o&userid=23715
View this thread: http://www.excelforum.com/showthread...hreadid=482732

Hi Donkin,

Try something like:

'==============
Public Sub Tester()
Dim WB1 As Workbook, WB2 As Workbook
Dim destSH As Worksheet
Dim SH As Worksheet
Dim destRng As Range

Set WB1 = Workbooks("Book1.xls") '<<===== CHANGE
Set WB2 = Workbooks("Book2.xls") '<<===== CHANGE
Set destSH = WB2.Sheets("Sheet1") '<<===== CHANGE

For Each SH In WB1.Worksheets
Set destRng = destSH.Cells(Rows.Count, "A").End(xlUp)(2)
SH.Range("A1").Copy Destination:=destRng
destRng(1, 2).Value = SH.Parent.Name & " " & SH.Name
Next SH
End Sub
'<<==============


---
Regards,
Norman


"Donkin" wrote in
message ...

What I am trying to do is copy Cell (a1) from each sheet in Book1 to a
list in Book2. The number of sheets in Book1 is variable and the sheet
names change regularly.

Sub Sheet_Select()
Windows("Book1.xls").Activate
Dim SH As Worksheet
For Each SH In ActiveWorkbook.Worksheets
Windows("book1.xls").Activate
Range("a1").Select
Selection.Copy
Windows("Book2.xls").Activate
Set W = Range("a1:a50").End(xlDown)
W.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next SH

End Sub

This will select Cell A1 in book1 and copy to a list in Book2
unfortunately this repeats for the number of sheets in Book1 but does
not cycle through the sheets.


--
Donkin
------------------------------------------------------------------------
Donkin's Profile:
http://www.excelforum.com/member.php...o&userid=23715
View this thread: http://www.excelforum.com/showthread...hreadid=482732

Thanks for the pointers, finally got the macro to work, scrolls throug
about 250 records set on 40 sheets and list the outstanding items i
seconds. Saving hours of tedious work. ;

--
Donki
-----------------------------------------------------------------------
Donkin's Profile: http://www.excelforum.com/member.php...fo&userid=2371
View this thread: http://www.excelforum.com/showthread.php?threadid=48273