How can I make a spreadsheet automatically update and add data from another spreadsheet?

So here is the situation:

I have one workbook with all the data. Its called Pending List. In that workbook is a sheet called Pending Booked Submitted List. That sheet has various coloumns, but I am really only interested in the following four: Member Company, Insured Name, Location, Status.

I would like to import that data, and keep it up to date, in a workflow document. The workbook will have 58 sheets, one for each member company (57) and one for a combined total. I would like the workflow document to check the Pending List and import each entry into the correct sheet.

Is this possible? And if so, how would I do this? I understand how to use Excel, and I understand the concept of linking, but I am not sure how I would use linking to add new entries because I would like any new insureds or new locations to be automatically updated from the Pending List.

The issue is not getting the data into the workbook - that can be accomplished with an external data connection or link. The issue is getting the right data in the right sheet. For example, getting all the locations for Company A in the Company A sheet, and all the locations for Company B in the Company B sheet.

-------------Problems Reply------------

You could use an event macro.

I have assumed that, for each sheet, your headers are in row 1 and your first column of data is A, and all the data is contiguous.

The first macro below will set up a data summary sheet to have the same headers as the other sheets, but will insert two new columns, A & B. The header on A is "Code" and is used to keep track of changes made on the other sheets. The header on B is "Company", to show what company the data is for.

I have named the Summary sheet "Summary Sheet" and the code will delete any existing Summary Sheet, so it can be run at any time to update the data set to only include current data.

After you have run the first macro, copy the second macro below, and double click the ThisWorkbook object in your project, then paste the code in the window that appears.

When a new entry is created on any sheet, it will also be added to Summary Sheet.

Bernie

Sub SetUpSummarySheet()
Dim myS As Worksheet
Dim mySumm As Worksheet
Dim myR As Range
Dim i As Integer
Dim strName As String

strName = "Summary Sheet"

On Error Resume Next

Application.DisplayAlerts = False
On Error Resume Next
Worksheets(strName).Delete
Application.DisplayAlerts = True

Set mySumm = Worksheets.Add(Worksheets(1))

mySumm.Name = strName

Application.EnableEvents = False

For i = 2 To Worksheets.Count
Set myS = Worksheets(i)
With myS
If i = 2 Then
.Range(.Range("A1"), .Cells(1, Columns.Count).End(xlToLeft)).Copy _

mySumm.Range("C1")
mySumm.Range("A1").Value = "Code"
mySumm.Range("B1").Value = "Company"
End If
myS.UsedRange.Offset(1, 0).Copy mySumm.Cells(Rows.Count, 3).End(xlUp)(2)
mySumm.Range(mySumm.Cells(Rows.Count, 2).End(xlUp)(2), _
mySumm.Cells(Rows.Count, 3).End(xlUp)(1, 0)).Value = _
myS.Name
With mySumm.Range(mySumm.Cells(Rows.Count, 1).End(xlUp)(2), _
mySumm.Cells(Rows.Count, 2).End(xlUp)(1, 0))
.Formula = "=""" & myS.Name & """ & TEXT(ROW(A2),"" 0000"")"
.Value = .Value
End With
End With
Next i

Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myS As Worksheet
Dim myR As Range
Set myS = Worksheets("Summary Sheet")
If Sh.Name = myS.Name Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
With myS
Set myR = .Range("A:A").Find(Sh.Name & Format(Target.Row, " 0000"), lookat:=xlWhole)
If myR Is Nothing Then
Set myR = .Cells(Rows.Count, 1).End(xlUp)(2)
myR.Value = Sh.Name & Format(Target.Row, " 0000")
.Cells(myR.Row, 2).Value = Sh.Name
End If
.Cells(myR.Row, Target.Column + 2).Value = Target.Value
End With
Application.EnableEvents = True
End Sub

There must be a line wrap error: take out the continuation character ( _ - an underscore) and have this all on one line:

.Range(.Range("A1"), .Cells(1, Columns.Count).End(xlToLeft)).Copy mySumm.Range("C1")

More Information:

For member company there are 57 options for a user to input.
For Insured Name there are an infinite number of possibilities.
For Location there are an infinite number of possibilities.
Status is a formula: =IF(Table1[[#This Row],[Submitted Date (from RAS)]],"Complete",IF(Table1[[#This Row],[Appt. Booked]],"In Progress","Pending")) -- Basically what this does is look at the Date Submitted column. If there is a date, it is considered Complete. If not, it looks at the Date booked column. If there is a date, it is considered In Progress. If there is no date it is considered pending.

Some general advice. Data is data - keep it in a database, and use Excel tools to create the reports. Do not use 58 sheets, use one sheet. Then you have a few options:

1) The first, and probably best, i to create a view which hides columns that you don't want to see, and apply data filters to show data that matches specific criteria, such as Member Company. All the member companies are there, but when you filter, only the chosen one is shown, and if you use SUBTOTAL functions to SUM COUNT, etc,, the values will update as the filtering changes. When you want to add data, change th view and remove the fiters.

2) use a pivot table - very similar results to filtering, just a little harder to use with text values unless you only want to count them

Bernie

I agree with you 100% about a database. Unfortunately, this data was collected prior to my arrival, and my instructions are very clear:

Create an excel spreadsheet for workflow:
1 - Have it include 57 tabs showing all the member companies plus have a 58th tab showing combined member company information.

2 - Include Insured Name, Location, and Status

3 - Find a way to have this excel sheet be updated/linked when the excel Pending List spreadsheet is updated

Then use a macro based on the key value. Select the cells with the member company column data on the 58th tab (where the data is entered?), and run a macro like

Sub Transfer()

Dim C As Range

For Each C In Selection

With Sheets(C.Value)

C.EntireRow.Copy .Cells(Rows.Count,1).End(xlUp)(2).EntireRow

'or change to only transfer specific data

End With

Selection.Interior.ColorIndex = 3

Next C

End Sub

This assumes that all columns are in the same order, and that column A is filled. After you run the macro, make some notation (with formatting, as used) to show that the data has been transfered.

The other option would be to have a macro delete the 57 tabs and recreate them from the 58th - that way, you would not need to know which darta has already been transfered.

Ok, so the situation has changed. The data is going to be entered by company, with each sheet representing one company. What I need to do now it have a summary sheet that will import and combine all the data from all the other sheets. Any ideas?

Ok, so I figured it out using these instructions: http://msdn.microsoft.com/en-us/library/cc793964%28v=office.12%29.aspx

Now, I'm not too familiar with Visual Basic, and I'm getting a compile error.

Compile Error: Sub or Function not defined.

On the following line:

' Find the last row with data on the summary worksheet.
Last = LastRow(DestSh)

You could use an event macro.

I have assumed that, for each sheet, your headers are in row 1 and your first column of data is A, and all the data is contiguous.

The first macro below will set up a data summary sheet to have the same headers as the other sheets, but will insert two new columns, A & B. The header on A is "Code" and is used to keep track of changes made on the other sheets. The header on B is "Company", to show what company the data is for.

I have named the Summary sheet "Summary Sheet" and the code will delete any existing Summary Sheet, so it can be run at any time to update the data set to only include current data.

After you have run the first macro, copy the second macro below, and double click the ThisWorkbook object in your project, then paste the code in the window that appears.

When a new entry is created on any sheet, it will also be added to Summary Sheet.

Bernie

Sub SetUpSummarySheet()
Dim myS As Worksheet
Dim mySumm As Worksheet
Dim myR As Range
Dim i As Integer
Dim strName As String

strName = "Summary Sheet"

On Error Resume Next

Application.DisplayAlerts = False
On Error Resume Next
Worksheets(strName).Delete
Application.DisplayAlerts = True

Set mySumm = Worksheets.Add(Worksheets(1))

mySumm.Name = strName

Application.EnableEvents = False

For i = 2 To Worksheets.Count
Set myS = Worksheets(i)
With myS
If i = 2 Then
.Range(.Range("A1"), .Cells(1, Columns.Count).End(xlToLeft)).Copy _

mySumm.Range("C1")
mySumm.Range("A1").Value = "Code"
mySumm.Range("B1").Value = "Company"
End If
myS.UsedRange.Offset(1, 0).Copy mySumm.Cells(Rows.Count, 3).End(xlUp)(2)
mySumm.Range(mySumm.Cells(Rows.Count, 2).End(xlUp)(2), _
mySumm.Cells(Rows.Count, 3).End(xlUp)(1, 0)).Value = _
myS.Name
With mySumm.Range(mySumm.Cells(Rows.Count, 1).End(xlUp)(2), _
mySumm.Cells(Rows.Count, 2).End(xlUp)(1, 0))
.Formula = "=""" & myS.Name & """ & TEXT(ROW(A2),"" 0000"")"
.Value = .Value
End With
End With
Next i

Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myS As Worksheet
Dim myR As Range
Set myS = Worksheets("Summary Sheet")
If Sh.Name = myS.Name Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
With myS
Set myR = .Range("A:A").Find(Sh.Name & Format(Target.Row, " 0000"), lookat:=xlWhole)
If myR Is Nothing Then
Set myR = .Cells(Rows.Count, 1).End(xlUp)(2)
myR.Value = Sh.Name & Format(Target.Row, " 0000")
.Cells(myR.Row, 2).Value = Sh.Name
End If
.Cells(myR.Row, Target.Column + 2).Value = Target.Value
End With
Application.EnableEvents = True
End Sub

Thank you so much! Unfortunately, I am still getting a compile error with the first Macro: Invalid use of property. This is the line:
mySumm.Range ("C1")

There must be a line wrap error: take out the continuation character ( _ - an underscore) and have this all on one line:

.Range(.Range("A1"), .Cells(1, Columns.Count).End(xlToLeft)).Copy mySumm.Range("C1")

Category:Office Views:8 Time:2014-06-09

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.133 (s). 11 q(s)