Solved VBA code to insert specific no. of new row & copy from above

June 3, 2015 at 04:14:45
Specs: Windows 7
Hi, I have x number of groups of data from Column A to G. Within each group, Column A contains the reference period and B is a figure. Column C to G contains the same data for the remaining rows. Each group contain different number of rows. I have included a new column H to indicate the number of time period for each group. Data are in month, quarter, annual frequency.
Sample annual data below:
 
Period (Year)	Sales	Brand	Model	Serial No.	Colour	Size	No. of periods
31/12/1960	100	ABC	XYZ	123456	Blue	S	6
31/12/1961	200	ABC	XYZ	123456	Blue	S	6
31/12/1988	500	DEF	abc	789101	Red	M	10
31/12/1989	1000	DEF	mno	789101	Red	M	10
31/12/1990	1500	DEF	rte	12345	Blue	S	10
31/12/1975	300	RSQ	IJK	890123	Green	L	26
31/12/1976	600	RSQ	abc	678901	Yellow	S	26
31/12/2005	500	XYZ	XYZ	123456	Blue	S	8

The common identifier within each group of data is Col C(Brand). For 1st group, Brand=ABC, it should contain 6 time period. Row2 and 3 consist of the existing data for 2 the periods:1960 & 1961.
I wish to insert 4 new rows below and copy the data & paste from the previous row. Col A should contain the next 4 reference period and B to be blank.
The number of new rows to be inserted is not fixed but should be based on Col H (number of time period) minus existing number of time period within each group.
After this template has been setup, I will key in Col B manually.

Subsequently, I will also be updating Col H daily to check for a change in number of time period. The code will be run on daily basis. If there is no change in time period, no new rows should be inserted or copied.

Can I have the VBA code to perform the above? Thanks.

message edited by lhm


See More: VBA code to insert specific no. of new row & copy from above

Report •


#1
June 3, 2015 at 09:49:26
✔ Best Answer
I have included 2 pieces of VBA code. The first one allows you to rebuild your original data after you have tested the "main" code. As written, the code assumes that your original data (as posted above) is in Sheet(1), starting in A1.

In order to use the Rebuild code, you must first copy the original data from Sheet(1) of your workbook to Sheet(2). Then, each time the Rebuild code is run, it will clear Sheet(1) and copy the (original) data from Sheet(2). This will allow you test the various options of the InsertPeriodRows code and then "rebuild" Sheet(1) with its original data.

Sub RebuildSheet1()
'Clear Sheet1, Copy Sheet2
  Sheets(1).Cells.ClearContents
  Sheets(2).Range(UsedRange.Address).Copy Sheets(1).Cells(1, 1)
End Sub

Based on your example data, the following code will insert the required number of rows and add either 1, 3, or 12 months to each new row, depending on which section you un-comment. As posted, all 3 "Increment" options have been commented out so the only thing the code will do is insert rows, retaining the original date.

Sub InsertPeriodRows()
'Determine last Row with Data in Column C
 lastRw = Cells(Rows.Count, "C").End(xlUp).Row
  
'Loop through rows in reverse order, looking for a change in Column C
   For nxtRw = lastRw + 1 To 3 Step -1
     If Cells(nxtRw - 1, "C") <> Cells(nxtRw, "C") Then
   
'When change occurs, determine number of rows to insert and periods to add
        cur_Count = Application.WorksheetFunction.CountIf(Columns(3), _
                   Cells(nxtRw - 1, "C"))
        ins_Rows = Cells(nxtRw - 1, "H")
        per_Num = ins_Rows - cur_Count
    
'Copy & Insert Rows
          For ins_Rw = 1 To ins_Rows - cur_Count
            Cells(nxtRw - 1, "C").EntireRow.Copy
            Cells(nxtRw - 1, "A").Insert
    
'*** To Increment by 1 Month ***
'               Cells(nxtRw, "A") = _
                  Application.WorksheetFunction.EDate(Cells(nxtRw - 1, "A"), _
                                                               1 * per_Num)
     
'*** To Increment by 1 Quarter (3 months) ***
'               Cells(nxtRw, "A") = _
                  Application.WorksheetFunction.EDate(Cells(nxtRw - 1, "A"), _
                                                               3 * per_Num)

'*** To increment by 1 Year ***
'                Cells(nxtRw, "A") = _
                  Application.WorksheetFunction.EDate(Cells(nxtRw - 1, "A"), _
                                                              12 * per_Num)
                  
'Decrement period variable
                    per_Num = per_Num - 1
                    
'Clear Column B
                    Cells(nxtRw, "B") = ""
          Next
     End If
   Next
End Sub

I would like to add one more comment:

I don't know if the original question you asked (the single row insert question) was just a simple example of your end goal (multiple rows based on Column H) or if they are 2 different projects. If you were attempting to simplify your overall requirements in your first thread, may I request that you don't do that anymore. Very often in these forums a simple set of requirements is posted first and then once a solution is offered, other requirements start to dribble out. "Thanks for that, now can you make it do this?" "Ok, thanks for that new code, now can you add this?"

What typically happens in those situations is one of two things:

1 - We end up with the original code and a bunch of bolt-ons that make the code bloated and inefficient, or

2 - We end up throwing away the original code (which is a waste of our time) and starting from scratch.

Please try to include all requirements with your original question so that we can work on the entire task at once and not in small pieces. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 23, 2015 at 05:09:10
Hi,

So sorry for the late reply. I have to thank you so much for providing the VBA codes.
The 'main codes' to insert the required number of rows and add either 1, 3, or 12 months to each new row works well. However, I realised that the incremented date does not follows the actual calendar (last day of the month), eg if I indicate 30/11/1980 in row 2, the incremented date is 30/12/1980 instead of 31/12/1980. May I know if it is possible to increment the date in Col A that follows the actual calendar?

I am so sorry that I did not indicate in my thread earlier that this worksheet actually consist of a combination of different frequencies: Month, Quarter and Annual. This is unlike in first thread that the data are separated by frequency in different worksheets.
I have included a new column I to indicate the frequencies. As usual, Column A contains the reference period and should increment by the number of periods of 1 month, 1 quarter or 1 year based on Column I.
Example: Row 15 consist of Jan1990 data and Column H=4 and I=Month, 3 rows should be inserted and A16 should be Feb1990, A17=Mar1990, A18=Apr1991.

The Rebuild code does not work. There is an error at the 3rd line: Sheets(2).Range(UsedRange.Address).Copy Sheets(1).Cells(1, 1)

I have several projects with varying requirements. Thus far, the codes provided by you are very useful and have been fully made use of for my projects. Thanks.

In my first thread, I asked for codes to insert 1 row and copy from the previous row.
I have an excel file with existing data already updated. Assuming that new data will come in at a regular interval (every month) and number of new datapoints is predictable (eg 1 new datapoint per update). I was intending to insert the new row to perform the data entry for that new datapoint. If there are more than 1 new datapoint, I shall run the code multiple times. As the number of rows is large, I have separated them by frequency: Month, Quarter and Annual for ease of updating.

For this thread, I have asked for codes to insert required number of rows and add either 1, 3, or 12 months to each new row. This is for another project which I have to build a template from scratch. As the nature of the data is slight different, the data does not come in at fixed interval, eg new data for product A is available but B might not be available yet. Hence, it may not be efficient to insert 1 row in the entire file resulting in blank rows that makes filtering difficult.
Given this, I am intending to insert new rows only when new data points are available. As it is built from scratch, the number of rows is not too huge.

Can I have the VBA code to perform the above? Thanks.

Your advice on posting the actual requirements is well noted and useful. In future, I will state my actual requirements as precise as possible to make the coding more efficient. Very sorry for the trouble.

message edited by lhm


Report •

#3
June 26, 2015 at 09:48:32
re: "The Rebuild code does not work. There is an error at the 3rd line: Sheets(2).Range(UsedRange.Address).Copy Sheets(1).Cells(1, 1)"

VBA can produce a huge number of errors, from a simple syntax error to a fatal event that crashes Excel. Simply telling me that "There is an error at the 3rd line" doesn't give me anything to work with. Since it works for me, there is nothing I can test nor any help I can offer without more detail related to the error.

re: "As the nature of the data is slight different, the data does not come in at fixed interval, eg new data for product A is available but B might not be available yet. Hence, it may not be efficient to insert 1 row in the entire file resulting in blank rows that makes filtering difficult."

I'll admit that I am confused by this request. The most recent code I’ve posted does not add any rows unless there is an update and it only adds rows where the value in Column H has changed. Is that not what you want the code to do?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question