Problem with Updating data from several Sheets
 

Hi!

Ok, I have tried to solve this problem that I have with my lovely Excel. I
have tried all kinds of formulas, but...it's not working the way I would want
it to.
I'm not so familiar with this VBA programming and I might say I'm quite
clueless when it comes to VBA, so I'm not even so sure my self what I'm
trying to ask here... :) ...try to bear with me, please...

So I have these four Sheets: MasterSheet:"Katselu" ; SlaveSheets:"Urakka",
"Ylityo" and "Tuntityo"

In my MasterSheet there's a CommandButton:"Update" . I need a VBA code that
works like this:
Every time I write a new information into spesific cells in a row,in to
any/all of these SlaveSheets, all the cells in that row will be
updated/inserted when clicking "Update" button into my MasterSheet, in to a
next available row in their own spesific cell places......

I try to make this more.....simple( u decide )?With an example....
Lets say these are my columns, Columns are named the same way in each Sheet
but they are in different places...so:

date time place
2008-8-9 10.00 somewhere

When i write new data in to these columns in any/all of my SlaveSheets, it
should update into my MasterSheet in to a new empty row when clicking the
"Update" Button. And ofcourse to the right columns...

Can anyone give me some advice or should i just delete the whole MasterSheet
:D
I actually don't have a clue what would be the best/easiest way to execute
this whole update funktion.....Yes, i have a problem :)

I'd really appreciate all the help i can get.

Problem with Updating data from several Sheets
 

On Sep 11, 3:57*am, CompletelyClueless
wrote:
Hi!

Ok, I have tried to solve this problem that I have with my lovely Excel. I
have tried all kinds of formulas, but...it's not working the way I would want
it to.
I'm not so familiar with this VBA programming and I might say I'm quite
clueless when it comes to VBA, so I'm not even so sure my self what I'm
trying to ask here... :) ...try to bear with me, please...

So I have these four Sheets: MasterSheet:"Katselu" ; *SlaveSheets:"Urakka",
"Ylityo" and "Tuntityo"

In my MasterSheet there's a CommandButton:"Update" . I need a VBA code that
works like this:
Every time I write a new information into spesific cells in a row,in to
any/all of these SlaveSheets, all the cells in that row will be
updated/inserted when clicking "Update" button into my MasterSheet, in to a
next available row in their own spesific cell places......

I try to make this more.....simple( u decide )?With an example....
Lets say these are my columns, Columns are named the same way in each Sheet
but they are in different places...so:

date * * * * * * *time * * * * * * place
2008-8-9 * * *10.00 * * * * *somewhere

When i write new data in to these columns in any/all of my SlaveSheets, it
should update into my MasterSheet in to a new empty row when clicking the
"Update" Button. And ofcourse to the right columns...

Can anyone give me some advice or should i just delete the whole MasterSheet
:D
I actually don't have a clue what would be the best/easiest way to execute
this whole update funktion.....Yes, i have a problem :)

I'd really appreciate all the help i can get.

Hi:

I have an older macro I created, assisted by a couple of functions I
acquired from Ron de Bruin. This will do the trick for you. Press Alt
+F11 to open the VB Editor, InsertModule, paste in the following
code. Test it out on a sample book, not your primary. There is NO undo
for changes made in a macro. This macro clears the master sheet and
replaces it with data from the other sheets. So long as you are not
deleting data from the other sheets, there will be no data loss.

This code could be better, but it does the trick.

Steven

Function LastRow(SH As Worksheet)
On Error Resume Next
LastRow = SH.Cells.Find(What:="*", _
After:=SH.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Function LastCol(SH As Worksheet)
On Error Resume Next
LastCol = SH.Cells.Find(What:="*", _
After:=SH.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
Sub UpdateMaster()
'This macro updates the Master Spreadsheet, it is linked to a button
on the Reports tab
Dim Last As Long
Dim LastSrc As Integer
Dim DestSh As Worksheet
Dim CopyRng As Range

' Runs macro in the background without showing all worksheet
activity.
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

' Clears existing data on Master spreadsheet, except for header
Row
Sheets("Sheet1").Select
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.ClearContents
Set DestSh = Sheets("Sheet1")

' Copies data from each spreadsheet in the workbook, excluding
sheets named in Case, and pastes data in Master
For Each Thing In Sheets
Select Case Thing.Name
Case "Sheet1", "Katselu"
'do nothing
Case Else
Last = LastRow(DestSh)
Sheets(Thing.Name).Activate
LastSrc = LastRow(ActiveSheet)
Set CopyRng = Thing.Range("A2:AZ" & LastSrc)
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End Select
Next
Sheets("Sheet1").Activate
Range("A2").Select
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With

End Sub

Problem with Updating data from several Sheets
 

On Sep 11, 1:37*pm, wrote:
On Sep 11, 3:57*am, CompletelyClueless



wrote:
Hi!

Ok, I have tried to solve this problem that I have with my lovely Excel.. I
have tried all kinds of formulas, but...it's not working the way I would want
it to.
I'm not so familiar with this VBA programming and I might say I'm quite
clueless when it comes to VBA, so I'm not even so sure my self what I'm
trying to ask here... :) ...try to bear with me, please...

So I have these four Sheets: MasterSheet:"Katselu" ; *SlaveSheets:"Urakka",
"Ylityo" and "Tuntityo"

In my MasterSheet there's a CommandButton:"Update" . I need a VBA code that
works like this:
Every time I write a new information into spesific cells in a row,in to
any/all of these SlaveSheets, all the cells in that row will be
updated/inserted when clicking "Update" button into my MasterSheet, in to a
next available row in their own spesific cell places......

I try to make this more.....simple( u decide )?With an example....
Lets say these are my columns, Columns are named the same way in each Sheet
but they are in different places...so:

date * * * * * * *time * * * * * * place
2008-8-9 * * *10.00 * * * * *somewhere

When i write new data in to these columns in any/all of my SlaveSheets, it
should update into my MasterSheet in to a new empty row when clicking the
"Update" Button. And ofcourse to the right columns...

Can anyone give me some advice or should i just delete the whole MasterSheet
:D
I actually don't have a clue what would be the best/easiest way to execute
this whole update funktion.....Yes, i have a problem :)

I'd really appreciate all the help i can get.

Hi:

I have an older macro I created, assisted by a couple of functions I
acquired from Ron de Bruin. This will do the trick for you. Press Alt
+F11 to open the VB Editor, InsertModule, paste in the following
code. Test it out on a sample book, not your primary. There is NO undo
for changes made in a macro. This macro clears the master sheet and
replaces it with data from the other sheets. So long as you are not
deleting data from the other sheets, there will be no data loss.

This code could be better, but it does the trick.

Steven

Function LastRow(SH As Worksheet)
* * On Error Resume Next
* * LastRow = SH.Cells.Find(What:="*", _
* * * * * * * * * * * * * * After:=SH.Range("A1"), _
* * * * * * * * * * * * * * Lookat:=xlPart, _
* * * * * * * * * * * * * * LookIn:=xlFormulas, _
* * * * * * * * * * * * * * SearchOrder:=xlByRows, _
* * * * * * * * * * * * * * SearchDirection:=xlPrevious, _
* * * * * * * * * * * * * * MatchCase:=False).Row
* * On Error GoTo 0
End Function
Function LastCol(SH As Worksheet)
* * On Error Resume Next
* * LastCol = SH.Cells.Find(What:="*", _
* * * * * * * * * * * * * * After:=SH.Range("A1"), _
* * * * * * * * * * * * * * Lookat:=xlPart, _
* * * * * * * * * * * * * * LookIn:=xlFormulas, _
* * * * * * * * * * * * * * SearchOrder:=xlByColumns, _
* * * * * * * * * * * * * * SearchDirection:=xlPrevious, _
* * * * * * * * * * * * * * MatchCase:=False).Column
* * On Error GoTo 0
End Function
Sub UpdateMaster()
'This macro updates the Master Spreadsheet, it is linked to a button
on the Reports tab
Dim Last As Long
Dim LastSrc As Integer
Dim DestSh As Worksheet
Dim CopyRng As Range

* * ' Runs macro in the background without showing all worksheet
activity.
* * With Application
* * * * .ScreenUpdating = False
* * * * .Calculation = xlCalculationManual
* * * * .EnableEvents = False
* * End With

* * ' Clears existing data on Master spreadsheet, except for header
Row
Sheets("Sheet1").Select
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.ClearContents
Set DestSh = Sheets("Sheet1")

* * ' Copies data from each spreadsheet in the workbook, excluding
sheets named in Case, and pastes data in Master
For Each Thing In Sheets
* * Select Case Thing.Name
* * * Case "Sheet1", "Katselu"
* * * * * *'do nothing
* * * Case Else
* * Last = LastRow(DestSh)
* * Sheets(Thing.Name).Activate
* * LastSrc = LastRow(ActiveSheet)
* * Set CopyRng = Thing.Range("A2:AZ" & LastSrc)
* * * * * * CopyRng.Copy
* * * * * * With DestSh.Cells(Last + 1, "A")
* * * * * * * * .PasteSpecial xlPasteValues
* * * * * * * * .PasteSpecial xlPasteFormats
* * * * * * * * Application.CutCopyMode = False
* * * * * * End With
* * *End Select
*Next
*Sheets("Sheet1").Activate
*Range("A2").Select
* * With Application
* * * * .ScreenUpdating = True
* * * * .Calculation = xlCalculationAutomatic
* * * * .EnableEvents = True
* * End With

End Sub

Just watch for unintentional line breaks in the code. Copying/pasting
from web is notorious for things like that.