macro to link worksheets

[Todd]

I'm creating a spreadsheet that will have several worksheets added over time.
i.e. a new worksheet for each new customer. All of the individual
worksheets will have the customers information added to it when it is
received.

There will be a summary sheet added to create a master list of all customers
and their basic info...Name, Location, Contact Name....etc.

How would I write a simple macro that will take the customer basic info and
add it to the summary sheet each time a new worksheet is added? Basically
just a macro that will enter the formula ='Customer (1)'!A1 into the
summary sheet from the customer sheet.

 

[Gord Dibben]

Couple of questions Todd.

When you add a new sheet there will be no customer information in it so what is
there to add to the summary sheet?

Is the added sheet a copied one or a new one?

This macro will add a sheet and enter the formula into the last cell in Summary
Sheet column A and copies across to column G

Sub add_copy()
Dim newsheet As Worksheet
Set newsheet = Sheets.Add
Set rng = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
rng.Formula = "=" & newsheet.Name & "!A1"
Sheets("Summary").Range(rng.Address & ":G" & rng.Row).FillRight
End Sub


Gord Dibben MS Excel MVP

 

[Todd]

Hi Gord,

1) You are correct. When I add a new customer sheet, initially there will
be no information, it will have to be added manually. After I have entered
all the relevant info, then I would run this macro to update the summary
sheet.

2) The new sheet added will be a copied sheet, and just require the new
customer info to replace the existing info from the copied sheet. Each new
sheet, will have exactly the same format.

I will try the macro you provided and let you know.

Thank you!

 

[Gord Dibben]

If all you're doing is copying an existing sheet and changing the data you don't
need my macro which Inserts a new blank sheet.

You could use part of the macro after you have copied the sheet and entered the
new data.

Sub add_copy()
Dim rng As Range
Set rng = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
rng.Formula = "='" & ActiveSheet.Name & "'!A1"
Sheets("Summary").Range(rng.Address & ":G" & rng.Row).FillRight
End Sub

Run the macro with the copied sheet as the active sheet.


Gord

 

[Todd]

Thanks again, Gord. I'm really close to getting this to work.

However, the macro is not copying the information from the customer sheet
into the summary sheet. it merely duplicates the headings of the summary
sheet to the line below.

how can i modify this to grab the newly created customer information?

 

[Gord Dibben]

What range on the copied sheet contains the customer info?

You probably have titles in Row1 of new sheet which you are seeing.

You originally asked for a formula of =Customer(2)!A1 to be copied to the
Summary sheet. I extended it to A1:G1

Change the A1 in the macro to some other cell address like A2


Gord