VBA Macro Assistance Requested: insert column, increment formulas- row

[Maranda022]

Hello,

Each year, companies input metric data into this workbook. I am developing a Macro that needs to update all tables and charts (stored in seperate worksheets) with new data. The attachment contains an example of how the is the source data is stored for each chart. Charts are stored in seperate individual worksheets.

Current Macro functionality searches for key word "Insert"on line 7 then adds a new column.
Need it to copy and paste C Values into new D column, then formulas need to be udpated so the row number is incremented by 1

Same needs to happen on line 18. i.e. search for "insert," but the new column has already been created, so the c values just need to be copied into column d and incremented by 1.

All source data is on separate worksheets i.e. company 1, company 2, etc… the data is stored the same way on each worksheet.

The number of companies and Tables will vary over time, so I will need to incorporate loops

Next Macro will need to update chart source data to include/display the data for the new year. Any help on this macro will be greatly appreciated as well!

Current Macro code (comments include code from recorded macro, that essentially does what I need to do, but the columns/ranges will vary):

Sub SearchAndInsertcolumn()


For i = 1 To Sheets.Count
If Worksheets(i).Name Like "Reg" & "*" Then
Worksheets(i).Activate

Dim SearchRange As Range
Dim FindWhat As Variant
Dim FoundCells As Range
Dim FoundCell As Range

Set SearchRange = Rows("7")
FindWhat = "Insert"
Set FoundCells = FindAll(SearchRange:=SearchRange, _
FindWhat:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
MatchCase:=False)
If FoundCells Is Nothing Then
Debug.Print "Value Not Found"
Else
For Each FoundCell In FoundCells
Debug.Print "Value Found In Cell: " & FoundCell.Address(False, False)

FoundCell.EntireColumn.Insert
Debug.Print "Value Found In Cell after insert: " & FoundCell.Address(False, False)

'Range("D718").Select
' Selection.Copy
'Range("E7").Select
'ActiveSheet.Paste
'Range("E7").Select
'Application.CutCopyMode = False

'Range("E8").Select
'ActiveCell.FormulaR1C1 = "='ACN Metrics'!R[25]C25"

'Range("E9").Select
'ActiveCell.FormulaR1C1 = "='Cadence Metrics'!R[24]C25"

'Range("E10").Select
'ActiveCell.FormulaR1C1 = "='Cisco Metrics'!R[23]C25"

'Range("E11").Select
'ActiveCell.FormulaR1C1 = "='Deloitte Metrics'!R[22]C25"

'Range("E12").Select
'ActiveCell.FormulaR1C1 = "='HP Metrics'!R[21]C25"

'Range("E13").Select
'ActiveCell.FormulaR1C1 = "='Microsoft Metrics'!R[20]C25"

'Range("E15").Select
'ActiveCell.FormulaR1C1 = "='Nortel Metrics'!R[18]C25"

'Range("E16").Select
'ActiveCell.FormulaR1C1 = "='Novell Metrics'!R[17]C25"

'Range("E17").Select
'ActiveCell.FormulaR1C1 = "='Unisys Metrics'!R[16]C[21]"

'Range("E18").Select
'Columns("E:E").EntireColumn.AutoFit

Next FoundCell
End If
End If

Next
End Sub

 

[SkipVought]

Hi,

There is no attachment.

Please ask FOCUSED questions. I'm not sure what you are aksing for. The clearer, more concise and complete you are, the better answers you will get.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Maranda022]

Hi!

Thank you for your prompt reply. I have posted the .xls with the sample tables to demonstrate what I am trying to accomplish with my macro.

2 primary problems -
1.) need help constructing loops to copy paste formulas
2.) need to know how to increment row references numbers in the formulas

I need create a loop that copies previous year's data into a new column (copy paste formulas).

Then I need to increment row reference numbers by one, so new data is added to a table (i.e. &Y30 to $Y31). If I fill down, the row numbers increment the way I need them to. When I copy/fill across, I get duplicate formulas &Y30 to &Y30 instead of &Y30 to &Y31.

Does that make more sense?

Thanks in advance, your help is desperately needed!