Try this macro

Sub test()
Dim n As Integer, rng As Range
n = InputBox("type the value of n")
Set rng = Range("a1")
rng.Select
line2:
Range(rng.Offset(1, 0), rng.Offset(3, 0)).EntireRow.Insert
Range(rng, rng.End(xlToRight)).Copy
Range(rng, rng.Offset(n, 0)).PasteSpecial
Set rng = rng.Offset(n + 1, 0)
If rng = "" Then
GoTo line1
Else
GoTo line2
End If
line1:
Application.CutCopyMode = False
Range("a1").Select
MsgBox "macro over"
End Sub

It works in my case. do an experiment open a new workbook.
type a in a1 and s in A2 and d in a3 and f in a4. now copy paste my macro in this workbook's vb editor and then run the macro . in the inptbox type for example 3 . Do you not get what you want . that is a repated 3 tiems, s repeated 3 times etc.



Then you can find out what is the problem in your workbook

a
s
d
f

Venkat,

Your absolutely right it works on the example great. How do i attach a file so i can use the real file to tweak?

1.open your file
2. open vb editor (alt+F11)
3.control+R
4.in the left side window highlight your file name
5. click insert(in the menu bar of vb editor)-module
6.there you copy paste the macro
7. save teh file
8. now you run the macro( I am sure you know how to do it)

any doubts post back

Try this

Sub test()
Dim n As Integer, rng As Range
'n = InputBox("type the value of n")
Set rng = Range("a1")
rng.Select
line2:
n = InputBox("type no. of times you want to be repeated minus 1 for e.g if you wnat to be repeated 3 times type 2")
Range(rng.Offset(1, 0), rng.Offset(n, 0)).EntireRow.Insert
Range(rng, rng.End(xlToRight)).Copy
Range(rng, rng.Offset(n, 0)).PasteSpecial
Set rng = rng.Offset(n + 1, 0)
If rng = "" Then
GoTo line1
Else
GoTo line2
End If
line1:
Application.CutCopyMode = False
Range("a1").Select
MsgBox "macro over"

End Sub

I have a problem that might be similar to the history in this forum entry.


I create a forecast for parts in a cross tabular format (example below)
ColA ColB ColC ColD ColE

Part 1 Month1 Month2 Month3 Month4
Part 2 Month1 Month2 Month3 Month4
Part 2 Month1 Month2 Month3 Month4



To load the forecast into my Enterprise software program, I need the data to be tabular:
ColA ColB

Part 1 Month1
Part 1 Month2
Part 1 Month3
Part 1 Month4
Part 2 Month1
Part 2 Month2
Part 2 Month3
Part 2 Month4
Part 3 Month1
Part 3 Month2
Part 3 Month3
Part 3 Month4
Part 4 Month1
Part 4 Month2
Part 4 Month3
Part 4 Month4


The number of "parts" varies month to month; I would like to define it when the macro first runs. In my example, there were 4 parts.

Most of the time I create a forecast for '12 months' but it would be more useful to also define the date range when the macro runs. In my example, there were just 4 months.


Finally, I would prefer that the macro paste the data in a 'new worksheet' (rather than over write my forecast data worksheet).



Any help would be of immense use. Thank you!

Aces... with minor tweaking for my 'real' document, it WORKS great, and I understand 50% of how it works :)


There is one last ‘need’ for my Enterprise upload... the DATE found in and across “Row 1, ‘$A$2:$A*’”

I need the macro to take the Rawdata, which is the forecast in cross tabular form (see below)
Rawdata:
ColA ColB ColC ColD

Item 6/1/2009 7/1/2009 8/1/2009
Part 1 50 55 6
Part 2 1 5 10



and produce the following results:
sheet2:
ColA ColB ColC

Part 1 39965 6/1/2009
Part 1 39995 7/1/2009
Part 1 40026 8/1/2009
Part 2 39965 6/1/2009
Part 2 39995 7/1/2009
Part 2 40026 8/1/2009


With what you provided me (THANK YOU SO MUCH) I am able to create ColA & ColB.
If I need to, I am able to copy & paste special ("transpose") the date range from the Rawdata. Unfortunately, when I have hundreds of parts it is just a little time consuming… so if the action can be incorporated into the macro, it would make my job go even faster.

Thank you again. Just so you don't think I am a leach wasting your talent, I have already signed up for Tutorial Excel & Access lessons with our Microsoft IS/IT package. Being able to do what you created will definitely make me more valuable.

I apologize, I miss typed in my sample/request above....



I need the additional macro lines to take the Rawdata, which is the forecast in cross tabular form (see below)
Rawdata:
ColA ColB ColC ColD

Item 6/1/2009 7/1/2009 8/1/2009
Part 1 50 55 6
Part 2 1 5 10

("Item", "6/1/2009", "7/1/2009, "8/1/2009" are headers)


and produce the following results:
sheet2:
ColA ColB ColC

Part 1 50 6/1/2009
Part 1 55 7/1/2009
Part 1 6 8/1/2009
Part 2 1 6/1/2009
Part 2 5 7/1/2009
Part 2 10 8/1/2009



As my lack of knowledge regarding macros is abundant, perhaps this is an entirely different macro than the first you provided. No matter what, I appreciate your guidence and effort!

WOW, everything I needed!!! Thank you kindly!

Just out of curiosity, are you self taught or have you taking some training to write in Visual Basic so fluently?
Netscur (Minneapolis, MN)

I am only self taught. that is why I am not an expert. I suggest to learn vba just RECORD macro following the steps you want take and edit the macro This is the best way to learn vba. any doubt go to google search. and also take help of forums.

Sorry the start date on the last row should state 25-09-09

I have added two more rows to check my macro. This is in SHEET1 as follows

Start Date End Date Start Time End Time Group Sub Group Details Manager
9/23/2009 9/25/2009 10:00 AM 11:00 AM Class X section 1 Goes for preparation Teacher 1
9/27/2009 9/29/2009 10:00 AM 11:00 AM class xi section 1 xxxxxxx teacher 2
10/2/09 10/6/2009 10:00 AM 11:00 AM class xi section 1 xxxxxxx teacher 2

carefully type this in sheet1

copy this data in sheet 2 also from A1 to retrieve for rechecking
KEEP SHEET1 AS ACTIVE SHEET
now run this macro "test" and see whether you get what you want in sheet1.

if you want to recheck.
1. run the macro "undo"
and then
2. run the macro "test"(ALWAYS KEEPING SHEET 1 AS ACTIVE SHEET)

Sub test()
Dim j As Integer, k As Integer, m As Integer, n As Integer
j = Range("a1").End(xlDown).Row
'j is hte lsst row
k = j
Do
If k = 1 Then Exit Do
m = Cells(k, "b") - Cells(k, "A")
'MsgBox m
'Range(Cells(k + 1, "A"), Cells(k + m, "A")).Select
Range(Cells(k + 1, "A"), Cells(k + m, "A")).EntireRow.Insert

For n = 1 To m
Cells(k, 1).EntireRow.Copy Cells(k + n, 1)
Next n

For n = 1 To m

Cells(k + n, 1) = Cells(k, 1) + n
Next n
k = k - 1
'MsgBox k
Loop
End Sub

Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").UsedRange.Copy Worksheets("sheet1").Range("A1")

I'm trying to do something very similar but MUCH simpler I think.

I would like to use cell A1 to put text into, Cell B1 to put the n number of times to repeat cell A1, and have the output added to column C

For example:

ColA      ColB      ColC
A1 text   5         A1 text
                        A1 text
                        A1 text
                        A1 text
                        A1 text