Count number of Row Insert Rows Paste Data Macro

[beantownmt]

Hi all.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Any help that could be offer would be great.<o></o>
I have a master file which has all my formulas in it.<o></o>
My Macro is able to import the data I need into a temp data sheet in the master file.<o></o>
I'm stuck on a couple of issues 1. Need to count the number of rows that are need to be inserted into the Master Sheet.(data report never has the same number of rows) 2. Copy and Paste the data (into the correct columns) 3. Maintain the current formulas and continue to update the line numbers.<o></o>
Hope I wasn't to confusing in my explanation. <o></o>

 

[jbeaucaire]

1) To count the number of rows in your temp data sheet, assuming even row1 has data:

Dim NewRows as Long

With Sheets("Temp Data Sheet")
    NewRows = .Range("A" & .Rows.Count).End(xlUp).Row
End With

For now, let's assume there is a Sum formula of some kind at the bottom of column A and across that whole row, so the new data needs to be inserted just above that.

3) Maintain the formulas and formatting from the last row of data.

Dim InsertRow As Long

With Sheets("Master")
    InsertRow = .Range("A" & .Rows.Count).End(xlup).Row
    .Rows(InsertRow - 1).Copy
    .Rows(InsertRow).Resize(NewRows).Insert xlShiftDown
    .Rows(InsertRow).Resize(NewRows).SpecialCells(xlconstants).Clearcontents
End With

2) Lastly you move the data into the new columns, you'll have to tweak this, of course, since only you know the correct "target" columns, but assuming each of the columns of data on the temp sheet has a matching target column:

With Sheets("Temp Data Sheet")
    Sheets("Master").Range("C" & InsertRow).Resize(NewRows).Value = .Range("A1:A" & NewRows).Value
    Sheets("Master").Range("E" & InsertRow).Resize(NewRows).Value = .Range("B1:B" & NewRows).Value
    Sheets("Master").Range("H" & InsertRow).Resize(NewRows).Value = .Range("C1:C" & NewRows).Value
    'etc...
End With

 

[beantownmt]

Jerry
Thank You for the Fast Reply.
If I want to start inserting the data in MasterSheet row 15 how do I tweak the code.
Thanks

 

[jbeaucaire]

Change this:

InsertRow = .Range("A" & .Rows.Count).End(xlup).Row

...to this:

InsertRow = 16

...or 15, or 17...