So nearly there.......

Skip to first unread message

john d

Jul 17, 2002, 6:43:50 AM7/17/02
I have a macro, which I want to use to extract data (3
cells) from 900 worksheets into a master Doc.

I have a list of the sheets in question in column A;
thanks to postings on this board I have a routine which
repeats the actions on each sheet.

However when switching between the master and the targets
for copying, the macro still only refers to the first
target sheet (ie the one the single routine was written
for, before the indexing routine was added).

To clarify
Initial routine was:
1. Copy file name from A1 in Master Book and use to open
Workbook 1
2. Go to Book 1 and copy data A
3. Go to Master Book and paste data A
4. Go to Book 1 and copy data B
5. Go to Master Book and paste data B
6. Go to Book 1 and copy data C
7. Go to Master Book and paste data C
8. Close Book 1

I have a way of repeating using A2 to open Workbook 2 (up
to Ax)
However when this runs lines 2, 4 & 6 still refer to Book
1 (instead of Book x, where x is row number)
This seems to be because when writing the original single
routine, I was switching between Master Book and Book 1 by
selecting Window from the top toolbar, and then clicking
on Book 1 – the name of Book 1 has therefore been
hardcoded? into the routine (as an absolute reference
rather than relative).
Is there a way of keeping the name of the target 'soft' ie
it needs to change each time the book name changes??

This will save me days of work each month!!

Jim Rech

Jul 17, 2002, 7:11:02 AM7/17/02
>>Is there a way of keeping the name of the target 'soft' ie it needs to
change each time the book name changes??

Yes, of course. But we can't tell what you're doing wrong without seeing
your code.

Jim Rech
Excel MVP

john d

Jul 17, 2002, 7:32:44 AM7/17/02
The '01092' in the macro below is the name of book 1 ie
contents of Cell (R,1) - it is this that needs to be
updated with the cell (R,x)'s contents for each indexing
of the macro. You can see that unless the reference can be
changed automatically the macro keeps opening the first
book every time. Hope this makes sense.

It almost seems that I need a macro to re-write this macro
900 times..........

For r = 3 To 900
Cells(r, 3).Select
Workbooks.Open FileName:="C:\My Documents\John
ActiveWindow.LargeScroll Down:=-1
ActiveWindow.LargeScroll ToRight:=-1
Application.CutCopyMode = False
Windows("PPC DSB codes v3.xls").Activate
Cells(r, 8).Select
ActiveWindow.LargeScroll Down:=1
Application.CutCopyMode = False
Windows("PPC DSB codes v3.xls").Activate
Cells(r, 9).Select
ActiveWindow.LargeScroll ToRight:=1
Application.CutCopyMode = False
Windows("PPC DSB codes v3.xls").Activate
Cells(r, 10).Select
Next r
End Sub


David McRitchie

Jul 17, 2002, 7:42:20 AM7/17/02
Hi John,
See BuildTOC

David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros:
Search Page:

"john d" <> wrote in message news:1aaa801c22d7e$d664ba90$35ef2ecf@TKMSFTNGXA11...

Dave Ramage

Jul 17, 2002, 9:40:44 AM7/17/02

I've started from scratch rather than modify your
original, so let me know if you can't see what's going on.

When you use the 'record macro' function, you often record
stuff that you don't really need such as window scrolls or
the selection of individual cells. I've removed this for a
start, and also created two new varables to store the two
worksheets- source and destination.

Sub DoStuff()
Const cSourceFolder = "C:\My Documents\John D's\"
Dim wsSource As Worksheet, wsDestination As Worksheet
Dim r As Integer

'turn of screen updates to make things faster
Application.scren = False

'define master worksheet
Set wsDestination = ActiveSheet

For r = 3 To 900

'open source workbook, and assign to wsSource variable
Set wsSource = Workbooks.Open(cSourceFolder &
wsDestination.Cells(r, 1).Value).Worksheets(1)
'copy data across
Destination:=wsDestination.Cells(r, 8)
Destination:=wsDestination.Cells(r, 9)
Destination:=wsDestination.Cells(r, 10)
'close source workbook
wsSource.Parent.Close savechanges:=False
Next r

Application.ScreenUpdating = True
End Sub



Jim Rech

Jul 17, 2002, 9:56:59 AM7/17/02
I agree with Dave that recording macros gives you a load of extra baggage
that you don't need. I don't think his macro is complete however so I'll
offer you mine. Note that the macro assumes that the list of filenames
starts in cell A3 of the active sheet and extends in continous cells with no
breaks until the end down column A. It also assumes the file names have
file extensions like "File1.xls", not just "File1".

I have run this macro and it works, but almost inevitably your exact
circumstances are somewhat different and that causes problem. You can come
back with them and I or someone will try to remedy them.

Const SrcDir As String = "C:\My Documents\John D 's\"

Sub CopyRoutineForJohn()
Dim SrcRg As Range
Dim FileNameCell As Range
Dim Counter As Integer
Application.ScreenUpdating = False
Set SrcRg = Range(Range("A3"), Range("A3").End(xlDown))
On Error GoTo SomethingWrong
For Each FileNameCell In SrcRg
Counter = Counter + 1
Application.StatusBar = "Doing workbook " & Counter & " of " &
Workbooks.Open SrcDir & FileNameCell.Value
Range("E1").Copy FileNameCell.Offset(0, 7)
Range("G39").Copy FileNameCell.Offset(0, 8)
Range("V39").Copy FileNameCell.Offset(0, 9)
ActiveWorkbook.Close False
Application.StatusBar = False
Exit Sub
MsgBox "Could not process " & FileNameCell.Value
End Sub


Jul 17, 2002, 12:57:10 PM7/17/02
Hi John

Yes, because you are using several workbooks, you do need to include a
way of varying the workbook name in your code. It may be an idea to
have the workbook name in column 1 and the sheet name in column 2 of
your master sheet.

You can copy/paste and then adapt the macro below (untested):-

Dim MasterSheet As Worksheet
Dim ToRow As Long
Dim MyBookName As String
Dim MySheetName As String
Set MasterSheet = ThisWorkbook.Worksheets("Master")
ToRow = 1 'start at row 1 in the master sheet
'- go down master sheet rows until there is a blank in column A
'- (end of list)
While MasterSheet.Cells(ToRow, 1).Value <> ""
MyBookName = MasterSheet.Cells(ToRow, 1).Value & ".xls"
MySheetName = MasterSheet.Cells(ToRow, 2).Value
'- open workbook
Workbooks.Open Filename:=MyBookName
'- transfer value to column C
MasterSheet.Cells(ToRow, 3).Value =
'- close workbook
Workbooks(MyBookName).Close savechanges:=False
'- go to next master row
ToRow = ToRow + 1
End Sub
'-- EOP ----------------------------------------------------------------

"john d" <> wrote in message news:<1aaa801c22d7e$d664ba90$35ef2ecf@TKMSFTNGXA11>...


Jul 17, 2002, 1:02:05 PM7/17/02
Hi John

sorry. the transfer line should be something like :-
MasterSheet.Cells(ToRow, 3).Value =

(left the Range off)

Brian B

"john d" <> wrote in message news:<1aaa801c22d7e$d664ba90$35ef2ecf@TKMSFTNGXA11>...

Reply all
Reply to author
0 new messages