Excel Row copy through VBA

[skg0009]

Hi,

I am seeking a help from all excel VBA expert.

I have attached the excel file for reference.

In the "Input" sheet there is production schedule that will be generated on submit button click. And the same schedule will also have to COPIED in "Output" sheet. But I am not able to paste it in desired location (blue highlighted area).

Another challenge for me to retain relevant data corresponding to year and clear the rest.
That means suppose its a schdule for 34 Years, now if I again generate it with 20 years it will keep the values of class I and class II till 20 years only and clear the rest.

The same has to be in Output sheet also.

Some one please help at earliest.

Thanks and regards,

Santosh

The code within Submit_Click:

Sub Submit_Click()
Dim no_years As Integer
Dim i_val As Integer
Range("c10").Activate
 
ActiveCell.EntireRow.Clear
 
ActiveCell = Range("b3").Value
no_years = Range("b4").Value
i_val = Range("b5").Value
 
 
Do While no_years > 0
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = DateAdd("YYYY", i_val, ActiveCell.Offset(0, -1).Value)
 
no_years = no_years - 1
 
Loop
 
' copy production schedule calender to all sheets
ActiveCell.EntireRow.Copy Destination:=Sheets("Output").Range("A" & Rows.Count).End(xlUp).Offset(1)
 
MsgBox ("Production schedule calendar generated")
End Sub

[zmbd]

skg0009,
+ For various reasons, most of us will not open unrequested attachments.

+ One question per thread.

Line22 is too much.
Say I have a table Range Sheet1!A1:C4 that I want to copy to sheet2 starting in cell Z5... then

    Worksheets("sheet1").Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Copy Destination:=Worksheets("Sheet2").Range("Z5")
    Range("A1").Select

There are a ton of other methods... this is the more straight forward of them and works for a contiguous region of data. You can of course select the data range using various methods.

the above will answer part of your second question too with a bit of thought and novel application.

[skg0009]

Thanks a lot.
Can you give me an idea, how do I clear irrelevant data, for ex, after copy and paste new data place in sheet 2 from cell Z5 to AI5, but already few data exist in sheet 2 till AI6.

How do I clear this extra cell data ?

Regards,

[zmbd]

new question new thread please