CODEWITHSUNDEEP
excelvba
Santosh Kumar Ghosh
Santosh Kumar Ghosh

Reputation: 29

Copy entire row to different sheet using VBA

I have attached the Excel file for reference.

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

Another challenge is to retain relevant data corresponding to year and clear the rest.
That means suppose it is a schedule 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 the Output sheet.

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

Upvotes: 1

Views: 40720

Answers (3)

keong kenshih
keong kenshih

Reputation: 524

I am not sure what is the desired location you mean? Is it the 1st column of the OUTPUT worksheet? I just enhanced your current code. Regarding the second challenge, I totally don't understand what you are trying to express. Perhaps you should print the screen or send me the excel file so that I can look at it.

Option Explicit
Dim CurrentWorkbook As Workbook
Dim InputWorksheet As Worksheet
Dim OutputWorksheet As Worksheet

Sub Submit_Click()

Set CurrentWorkbook = Workbooks(ActiveWorkbook.Name)
Set InputWorksheet = CurrentWorkbook.Sheets("Input")
Set OutputWorksheet = CurrentWorkbook.Sheets("Output")

Dim NumberOfYear As Long
Dim IntervalOfYear As Long
Dim ColumShift As Long

ColumShift = 1
InputWorksheet.Range("c10").EntireRow.Clear
InputWorksheet.Range("c10").Value = InputWorksheet.Range("B3").Value

NumberOfYear = InputWorksheet.Range("b4").Value
IntervalOfYear = InputWorksheet.Range("b5").Value

Do While NumberOfYear > 0
InputWorksheet.Range("c10").Offset(0, ColumShift).Value = DateAdd("YYYY", IntervalOfYear, InputWorksheet.Range("c10").Value)
NumberOfYear = NumberOfYear - 1
ColumShift = ColumShift + 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

Upvotes: 1

Heinreich213
Heinreich213

Reputation: 131

I get similar issue and found the solution as: Acyou can use this range "B§" directly as (I assume your current worksheet is set as Sht)

Sht.Cells(3, 2).EntireRow.Copy 'or the other way in comments
'ActiveCell = Range("b3").Value  
'ActiveCell.EntireRow.Copy
Sheets("Output")Range("A" & Rows.Count).PasteSpecial Paste:=xlValues

Upvotes: 1

David
David

Reputation: 863

The easy way to do this:

ActiveSheet.Rows("1:1").Copy
Sheets("Output").Paste

Is this what you're looking for?

Upvotes: 1

Related Questions