MACRO THAT BUILDS ON ANOTHER MACRO'S DATA

[Tree]

I have posted this 2ce but I think I did a very bad job of explaining what I
was looking for.. Hopefully I have done a better job now as I really need the
assistance!

Ron Bruin helped with a macro that combines data from several tabs into one
sheet and that macro works great..

I want to create a macro that runs Ron's macro and then with that resulting
data, does the following:

(Row 1 contains headers from Ron's macro and I can't find how to have the
Resulting Data Macro "Start"? on Row 2-also, Ron's macro seems to create 3
total header rows and I have to delete 2 of them in the "Resulting Data "
macro);

Columns A-H contain the data from the combo macro; Columns I:L would contain
formulas (each column has a unique formula and uses the data from Columns
D-H... BUT they only run IF Column C has text..

The formulas are as follows:

Column I has Column D + Column E - Column F..
Column J has Column D + Column E - Column H
Column K = Column G.
Column L has Column J - Column K.

And these formulas need to insert themselves in the respective rows in
Columns I-L as long as there is text in Column C from the already run combo
macro..

I appreciate your assistance!!!

 

[Luke M]

Here's a macro that calls out another macro. Change the Application.Run line
as appropriate to match the name of your workbook and macro the Ron gave you.
You may also need to change the name of the sheet to be selected.

Sub AddFormulas()

'Change this line as appropriate
Application.Run "YourWorkbook!NameOfOtherMacro"

'Change as appropriate
Sheets("Name of Sheet").select
For Each Cell In Range("C:C")

x = Cell.Row

'Checks to make sure past header rows
'and that cell in column C is not empty
If x < 2 Or IsEmpty(Cell) Then
'do nothing
Else

'Create your formulas
Cells(x, "I").Formula = "=D" & x & "+E" & x & "-F" & x
Cells(x, "J").Formula = "=D" & x & "+E" & x & "-H" & x
Cells(x, "K").Formula = "=G" & x
Cells(x, "L").Formula = "=K" & x & "-K" & x
End If

Next
End Sub

 

[Tree]

WOW! Wonderfully brilliant!! .. This seems to work perfectly! Now, if I
could impose further? I need to delete Rows 2 and 3 AFTER the first macro has
run b/c for some reason it puts in 3 header rows and the ones on Rows 2 and 3
I don't need.. and I would like the data that comes in from the formulas in
this macro to have the same formatting (borders, font, etc) as the rest of
the sheet..

Is this possible???

 

[Luke M]

After the Application.Run command, add this line of coding:

Range("2:3").EntireRow.Delete

As for the formatting, that's a little more tricky (you'd have to define
each parameter individually, or try and copy the formatting from somewhere
else). I would suggest that you record a short macro of you formatting the
cells (I:L) the way you want, with borders, font, etc. Then in the VBE, copy
that part of the macro over to the end of the macro I gave you.

 

[Tree]

Once again.. WONDERFULLY BRILLIANT! That works perfectly for the deleting of
the 1st 2 rows.. as to the formatting, I had already tried what you suggested
except I don't know how to get it to only copy formats to what is being
populated and that changes based on how much data is there everytime you run
the macro.. for now, the code looks like this - see my comments within the
code..
Also, I tried to find the proper structure of the routine/command to make
sure it fit correctly within the macro but wasn't sure if this was ok or if
other commands were needed.. and exactly where in the macro does it go?
Range("H3").Select
Selection.Copy
Range("I2:L53").Select EXCEPT THIS LINE i WOULD LIKE TO HAVE IT ONLY
POST THE FORMAT TO THE ACTUAL AREAS THAT HAVE DATA IN THEM.. THIS RANGE WILL
VARY EVERYTIME THE MACRO IS RUN..
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

 

[Luke M]

Here's the complete code, with row deletion and format copied to cells with
formulas:

'==================
Sub AddFormulas()

'Change this line as appropriate
Application.Run "YourWorkbook!NameOfOtherMacro"
Range("2:3").EntireRow.Delete

'Change as appropriate
Sheets("Name of Sheet").Select

For Each Cell In Range("C:C")
x = Cell.Row
'Checks to make sure past header rows
'and that cell in column C is not empty
If x < 2 Or IsEmpty(Cell) Then
'do nothing
Else
'Create your formulas
Cells(x, "I").Formula = "=D" & x & "+E" & x & "-F" & x
Cells(x, "J").Formula = "=D" & x & "+E" & x & "-H" & x
Cells(x, "K").Formula = "=G" & x
Cells(x, "L").Formula = "=K" & x & "-K" & x
End If

Next

Range("H3").Copy
'Grabs only the cells in I:L that have formulas
Range("I:L").SpecialCells(xlCellTypeFormulas, 23).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
'=================

 

[Tree]

WOW!!! YOU ARE AMAZING!! THANK YOU! It works perfectly!!!!!!!!!
THANK YOU!