Inserting and copying Rows based on a variable
 

I have a very long spreadsheet that has a beginning year in column A and the
ending year in column B. Column C (which currently has no data in it) =
Specific Year. The rest of the row is specific information that applies to
each year in the range. I need to insert the number of rows based on the
number of years in the range, i.e Column A = 2004, Column B=2006. I would
need to insert 3 rows and add each specific year in the range to column C.
Assuming the data starts in Row 2 and Column A = 2004, Column B = 2006,
Column C = " ", Column D thru Column AY has data. The resulting data should
be: Row 3: Column A = " ", Column B = " ", Column C = 2004, Column D thru AY
= Row 2 data Column D thru AY. Row 4 would be the same as row 3 except Column
C would index to 2005. I appreciate anyone out there can help me out with a
Macro.

Inserting and copying Rows based on a variable
 

Hi Darwin

Try and feedback the below macro which works on the activesheet.

Sub MyMacro()
Dim lngRow As Long, intTemp As Integer
lngRow = 2
Do
If Range("A" & lngRow) < "" Then
For intTemp = Range("A" & lngRow) To Range("B" & lngRow)
Rows(lngRow + 1).Insert: Range("C" & lngRow + 1) = intTemp
Range("D" & lngRow & ":AY" & lngRow).Copy _
Range("D" & lngRow + 1 & ":AY" & lngRow + 1)
lngRow = lngRow + 1
Next
End If
lngRow = lngRow + 1
Loop Until lngRow Cells(Rows.Count, "A").End(xlUp).Row
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Darwin" wrote:

I have a very long spreadsheet that has a beginning year in column A and the
ending year in column B. Column C (which currently has no data in it) =
Specific Year. The rest of the row is specific information that applies to
each year in the range. I need to insert the number of rows based on the
number of years in the range, i.e Column A = 2004, Column B=2006. I would
need to insert 3 rows and add each specific year in the range to column C.
Assuming the data starts in Row 2 and Column A = 2004, Column B = 2006,
Column C = " ", Column D thru Column AY has data. The resulting data should
be: Row 3: Column A = " ", Column B = " ", Column C = 2004, Column D thru AY
= Row 2 data Column D thru AY. Row 4 would be the same as row 3 except Column
C would index to 2005. I appreciate anyone out there can help me out with a
Macro.

Inserting and copying Rows based on a variable
 

Jacob

The Macro worked like a dream. It saved me many hours of clicking and
inserting. I appreciate the help.

Darwin

"Jacob Skaria" wrote:

Hi Darwin

Try and feedback the below macro which works on the activesheet.

Sub MyMacro()
Dim lngRow As Long, intTemp As Integer
lngRow = 2
Do
If Range("A" & lngRow) < "" Then
For intTemp = Range("A" & lngRow) To Range("B" & lngRow)
Rows(lngRow + 1).Insert: Range("C" & lngRow + 1) = intTemp
Range("D" & lngRow & ":AY" & lngRow).Copy _
Range("D" & lngRow + 1 & ":AY" & lngRow + 1)
lngRow = lngRow + 1
Next
End If
lngRow = lngRow + 1
Loop Until lngRow Cells(Rows.Count, "A").End(xlUp).Row
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Darwin" wrote:

I have a very long spreadsheet that has a beginning year in column A and the
ending year in column B. Column C (which currently has no data in it) =
Specific Year. The rest of the row is specific information that applies to
each year in the range. I need to insert the number of rows based on the
number of years in the range, i.e Column A = 2004, Column B=2006. I would
need to insert 3 rows and add each specific year in the range to column C.
Assuming the data starts in Row 2 and Column A = 2004, Column B = 2006,
Column C = " ", Column D thru Column AY has data. The resulting data should
be: Row 3: Column A = " ", Column B = " ", Column C = 2004, Column D thru AY
= Row 2 data Column D thru AY. Row 4 would be the same as row 3 except Column
C would index to 2005. I appreciate anyone out there can help me out with a
Macro.