Referencing the worksheet index in a formula or just to pull data into new worksheet

[Vonsteiner]

Hello everyone,

I have a macro which is creating a new sheet in front of the only other worksheet in the workbook. In this new sheet I need to input some data from the original worksheet. To break it down a little better here is the whole process of what I am doing:

Step 1
From a Master Invoice List I have created an separate workbook for each company I need to invoice. Each sheet is numbered whichever number it was in the original workbook before I separated them into individual workbooks. So, the 6th sheet in the Master Invoice List is showing as Sheet6 in it's individual workbook
Step 2
I have a macro to create a Summary worksheet before the company worksheet in the new workbook. I am not sure of how to proceed with pulling in the information from the company sheet
Step 3
I want to include in the macro I use to create the Summary sheet formulas or the ability to pull in the data I need from the company sheet

I need to use the index number of the worksheet as the company name in each file will be different. So using the sheet name or even the sheet # will not work unless there is a way to reference them somehow even though they are different in every workbook. The formulas will not necessarily be in the same place in each Company workbook (each company will have a different amount of invoices that are being summed up). So these formulas need to also be able to find the last row of the column.

Right now my code looks like this

    Worksheets.Add(Before:=Worksheets(1)).Name = "Summary"

    Range("B2").Select
    ActiveCell.FormulaR1C1 = "='Company Name'!RC[-1]"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "='Company Name'!R[-1]C[1]"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "='Company Name'!R[3]C[6]"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=-'Company Name'!R[2]C[7]"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "=-'Company Name'!R[1]C[11]"
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "=-'Company Name'!RC[16]"
    Range("C8").Select
    ActiveCell.FormulaR1C1 = "='Company Name'!R[-1]C[21]"
    Range("C9").Select
    ActiveCell.FormulaR1C1 = "='Company Name'!R[-2]C[27]"
    Range("C10").Select

After I add the Summary worksheet I have more code that adds some formatting. I believe once I add the Summary worksheet the Company Name worksheet is now Worksheets(2). I need to add more, but once I get how to incorporate one of the above I should be able to use that for the rest.

Hopefully I made sense. Any help would be greatly appreciated.

On a side note...if there is a way to have this macro run on all the 'Company Name' workbooks in the folder without having to open each one individually that would be helpful as well.

 

[par60056]

Here are a few suggestions.
1) Selecting cells is a slow process. You can reference a cell directly by row and column.
2) Save the active sheet name before adding the new sheet.

Dim sourceSheet as string
sourceSheet = activeSheet.Name

Worksheets.Add(Before:=Worksheets(1)).Name = "Summary"

Cells(2,2).FormulaR1C1 = "='" & sourceSheet & "'!RC[-1]"
Cells(3,2).FormulaR1C1 = "='" & sourceSheet & "'!R[-1]C[-1]"

you can find the last row of a column like this:
dim lastRow as Long
lastRow = range("c100000").End(xlUp).row

 

[JLGWhiz]

Do you have to ues a formula. You could get the value from the original sheet with code like

Range("B2") = Sheets(2).Range("A2")

Plus it gets rid of the Select and ActiveCell syntax which are extraneous anyhow.

 

[Vonsteiner]

Thank you both for responding so quickly. JLGWhiz--that worked perfectly.

 

[JLGWhiz]

Thank you both for responding so quickly. JLGWhiz--that worked perfectly.

You're welcome,
regards, JLG