Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting data from master sheet into sub sheets and automatic updating. | Links and Linking in Excel | |||
Updating Excel Data on Multiple Sheets | Excel Worksheet Functions | |||
Inputting data on one sheet and automatically updating other sheets | Excel Programming | |||
Excel VBA - Updating of data problem | Excel Programming | |||
Updating sheets to include new data beside old data | Excel Programming |