Previous Topic Next Topic

Need Individual Sheet Data to Copy to Main Sh

Closed
Peter T - May 31, 2011 at 09:09 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 1, 2011 at 05:59 AM
Hello,

I have a workbook I've created to track sales leads. I have a main sheet called "directory" and individual sheets for each business I contact. I created a simple macro to copy a blank client sheet whenever I need to add a new one. But when I do, I want basic info: Business Name, Contact Name, and Next Followup Date to appear on the "directory" page without having to just copy the last line on the directory page and then change the tab name to the new one. Any easy way to do it?

1 reply

Answer 1 / 1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 31, 2011 at 09:35 PM
not clear how things are but what ever you are trying to do seems to be doable with a macro
0
Peter T
May 31, 2011 at 09:41 PM
I've made a Macro that will go to the "directory" sheet, add a row, then copy the appropriate cells. However, I don't know how to make that Macro work on the sheet I want it to work on. Example: I made the macro using a client sheet called "Allstate" so whenever I run the Macro it does the same "Allstate" sheet instead of the next sheet I created.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 31, 2011 at 09:49 PM
post your macro
0
Peter T
May 31, 2011 at 10:02 PM
Sub ToDirectory()
'
' ToDirectory Macro
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Directory").Select
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Select
ActiveCell.FormulaR1C1 = "='Allstate RF'!R[-1]C[2]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='Allstate RF'!RC[1]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='Allstate RF'!RC[6]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='Allstate RF'!R[2]C[5]"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 31, 2011 at 10:18 PM
I not following what you are attempting to do here. you seem to be saying that insert row and then do a look up from allstate sheet.
I think you are trying to do is, duplicate some sheet, then from that new sheet copy some information into your directory sheet. If that is true, then perhaps what u need to do is refresh dir sheet by looping thru sheets or at least looping and inserting if a new sheet is found
0
Peter T
May 31, 2011 at 10:34 PM
Not sure I understand your response, maybe I'm going about it the completly wrong way. I recorded a short video demonstrating what I want to happen. http://romestrinity.com/wp-content/uploads/2011/06/add_to_directory.mp4
0