Vonsteiner
New Member
- Joined
- Apr 14, 2014
- Messages
- 45
- Office Version
- 365
- Platform
- Windows
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
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.
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
VBA Code:
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.