Copying to new sheet

  • Thread starter Thread starter s1mon
  • Start date Start date
S

s1mon

Hi,

I have 12 WORKBOOKS (not worksheet) all the same and you can mak
entires into them of stock.

I also have a master workbook - exactly the same as the 12 others.

Is there a way of copying whatever someone enters into any one of th
12 into the master one automaticaly?

for example, i enter something into book 1 and book 6 and then i loo
at the master one and it's been updates for me with those entrie
aswell.

many thanks,

Simon. :confused
 
If you have linked the master workbook to the others with formulas thi
should automatically occur when it is opened, or via macro to refres
links
 
sorry i dont understand that.

i need it to put each entry on a new line and look at all 12 to see if
there is a new one.

how do i go about setting yit up? is there a tutorial or can you
exaplain?

many thanks,

simon.
 
Just my opinion, but I don't think I'd do this.

Too much can go really wrong.

Instead, I'd either do my updating in the 12 workbooks and combine them into the
master whenever I needed to--or do all my updating in the master and split it up
on demand.

But I'd keep only one set of real data either way.
 
I assumed that the master book was just a summary of the *totals* of the
others.

I agree with Dave that your method is not very safe if you are copying
records.

If you really need 12 books, the safest way of making a master list
would be to start from a blank master sheet each time and copy/paste
all the records from the other books. This is easier to program too.

I don't have time to write this at present.
 
Here is some code that works OK if you keep to the "rules" shown,
otherwise you can change it as you wish.

'==============================================
'- Generic code for transferring data from
'- one or more workbooks to a master sheet
'-
'- workbooks must be the only ones in the folder
'- worksheets must be the first one in the book
'- worksheets must contain tables which are
'- identical to the master, headings in row 1.
'- master sheet is remade each time.
'- run this code from the master book
'-
'----------------------------------------------
Dim ToBook As String
Dim ToSheet As Worksheet
Dim NumColumns As Integer
Dim ToRow As Long
Dim FromBook As String
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim LastRow As Long
'-
'----------------
Sub NEW_MASTER()
'----------------
Application.Calculation = xlCalculationManual
ChDrive ActiveWorkbook.Path
ChDir ActiveWorkbook.Path
ToBook = ActiveWorkbook.Name
Set ToSheet = ActiveWorkbook.Worksheets(1)
NumColumns = ToSheet.Range("A1").End(xlToRight).Column
ToRow = ToSheet.Range("A65536").End(xlUp).Row
'- clear master
If ToRow <> 1 Then
ToSheet.Range(Cells(2, 1), Cells(ToRow,
NumColumns)).ClearContents
End If
ToRow = 2
'- main loop
FromBook = Dir("*.xls")
While FromBook <> ""
If FromBook <> ToBook Then
Application.StatusBar = FromBook
Transfer_data
End If
FromBook = Dir
Wend
'-- close
MsgBox ("Done.")
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
End Sub
'-------------------------------------------------

Sub Transfer_data()
Workbooks.Open FileName:=FromBook
Set FromSheet = Workbooks(FromBook).Worksheets(1)
LastRow = FromSheet.Range("A65536").End(xlUp).Row
FromSheet.Range(Cells(2, 1), Cells(LastRow, NumColumns)).Copy _
Destination:=ToSheet.Range("A" & ToRow)
ToRow = ToSheet.Range("A65536").End(xlUp).Row + 1
Workbooks(FromBook).Close savechanges:=False
End Sub
'==== EOP ======================================
 
Back
Top