Sheet Updation VB

[samitnair]

Hi Mr. XL Board members,

I am really becoming a fan of this board and I get to learn a lot......Now am again in need of ur expert opinions....

Purpose of this code: I am trying to combine the data from Sheet 1,2,3 into to sheet namd "AIO". All sheets are in the same workbook. But the code only supports creating a new sheet (AIO) every time i run the code...

I would need the sheet "AIO" only to be updated with the data

OR

Any data i modify (enter/delete) in Sheets 1,2,3 must reflect in sheet "AIO"
CAN this be done???

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "AIO"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(3, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

please help!!! and Thanks in adv

 

[Jerry Sullivan]

Hi Samitnair,

It sounds like you want your "AIO" sheet to be the result of combining the data in Sheets 1,2,3. The easiest way to do this would be to have a macro that clears "AIO" and then copies the data from each sheet.

OR
Any data i modify (enter/delete) in Sheets 1,2,3 must reflect in sheet "AIO"
CAN this be done???

It is possible to write code that would do adds/deletes from AIO as they are done to Sheets 1,2,3...but that is much more complex and probably not any benefit unless you are working with huge datasets.

Instead of using a Button, you could trigger the "Clear and Recopy" to AIO each time someone activates the Sheet AIO. That way you reduce the likelihood that AIO isn't updated.

Please confirm I'm understanding what you want and I'll be glad to help with some code. You might also check on this site for examples of how to combine data from each sheet on to a master, since it is a common question.

 

[samitnair]

Hi Jerry,

Thanks for helping me and keeping ur promise

1. Yes, I need "AIO" as a combined file of sheet 1,2,3
2. Its a basic Logistics work sheet (Not a big worksheet)
3. Am comfortable with "Clear an recopy" technique.

OK i will check the site and respond asap so as to save some time

Thanks again

 

[Jerry Sullivan]

Here is some code that you can try.

Sub Combine()
    Dim lngIndex As Long
    Dim rngCurr As Range
 
    On Error Resume Next
    Application.ScreenUpdating = False
    If (IsError(Worksheets("AIO").Activate)) Then
        Worksheets.Add before:=Worksheets(1)
        ActiveSheet.Name = "AIO"
    Else 'sheetname already exists
        With Worksheets("AIO")
            .Move before:=Worksheets(1)
            .Cells.Clear
        End With
    End If
    With Sheets("AIO")
        Worksheets(2).Rows(1).Copy _
            Destination:=.Range("A1")
        For lngIndex = 2 To Worksheets.Count
            Set rngCurr = Get_Data(rngFirstCell:= _
                Worksheets(lngIndex).Range("A2"))
            If Not (rngCurr Is Nothing) Then
                rngCurr.Copy Destination:=.Cells(Rows.Count, 1) _
                    .End(xlUp).Offset(1)
            End If
        Next lngIndex
    End With
    Set rngCurr = Nothing
End Sub
 
Private Function Get_Data(rngFirstCell As Range) As Range
    Dim lngLastCol As Long, lngLastRow As Long
    Dim c As Range
    With rngFirstCell
        Set c = .Parent.Cells.Find(What:="*", After:=Parent.Cells(1), _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        If c Is Nothing Then GoTo ReturnEmpty
        If c.Column < .Column Then GoTo ReturnEmpty
        lngLastCol = c.Column
        Set c = .Parent.Cells.Find(What:="*", After:=.Parent.Cells(1), _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        If c.Row < .Row Then GoTo ReturnEmpty
        lngLastRow = c.Row
        With Range(.Cells(1), .Parent.Cells(lngLastRow, 1)).EntireRow
             Set c = .Find(What:="*", After:=.Cells(1), _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
             If c Is Nothing Then GoTo ReturnEmpty
             If c.Column < rngFirstCell.Column Then GoTo ReturnEmpty
             lngLastCol = c.Column
        End With
        With Range(.Cells(1), .Parent.Cells(1, lngLastCol)).EntireColumn
             Set c = .Find(What:="*", After:=.Cells(1), _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        End With
        Set Get_Data = Range(.Cells(1), .Parent.Cells(c.Row, lngLastCol))
    End With
    Set c = Nothing
    Exit Function
ReturnEmpty:
    Set c = Nothing
    Set Get_Data = Nothing
End Function

The Get_Data function is probably better for your application than using the CurrentRegion property. It will get all the data below your header, even if there are some empty rows or columns.

That function might look like overkill; however I prefer to use a separate function to get the used data range in a sheet rather than having to put all the last cell finding and error checking in the main Sub.

 

[samitnair]

Hi Jerry,

Correct me if am wrong, Should I create a button and paste this code inside...or do something else.....as the code is not working it is only copying the headings....Ur code for me is like a jungle and am lost in it......am very new to VB....

So far i could get is....

1. The first part of the code create a sheet AIO (which supports updation of values)
2. code only copies the cell or row with a value in it

Please guide how to set the code or the procedure to get the desired result.....

Thanks

 

[Jerry Sullivan]

Hi Samitnair,

Ur code for me is like a jungle and am lost in it.

Sorry to have sent you into that jungle without a better compass!

The code I posted earlier should be placed in a Standard Code Module, following the instructions below.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code)

1. Copy the code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

I'd suggest you first get the code to run correctly using ALT+F8, before adding a button. Please give this a try, and let me know if it works.

Then, I'll explain the easy process of adding a button.

 

[samitnair]

Hi....Thanks..it works perfect....

Thanks again