Copying non blank rows from a range from one worksheet to another

Question

Hi I am a beginner at VBA. I have a question. I am trying to paste data from one worksheet range (A27:K35) to another from worksheet range (A9:K17). There is a slight problem. The user doesn’t necessarily have to enter all the rows. He/She can enter 1-8 roaws as desired. For example, if rows A27:K30 is entered in worksheet 1, then that data should be copied to rows A9:K12 in worksheet 2 and not copy the blanks. I managed to write something, but it copies all 8 rows at a time. Can someone help me with the problem of avoiding blanks?

Submit is a button with the macro as described below on worksheet 1.

Sub Submit()

Range("A27:K35").Select
    Selection.Copy

    Sheets("Worksheet2").Select
    Range("A9:A17").Select
    Selection.Insert Shift:=xlDown

MsgBox "Data copied"

End Sub

Davesexcel Davesexcel · Accepted Answer · 2015-01-27T19:51:05

You don't need to select the range to paste to, just the first cell, the code below will copy range A27:K35 Then paste it to the first empty row in Column A ("Worksheet2")

   Dim sh As Worksheet
    Set sh = Worksheets("Worksheet2")
    ActiveSheet.Range("A27:K35").Copy Destination:=sh.Cells(Rows.Count, "A").End(xlUp).Offset(1)

Copying non blank rows from a range from one worksheet to another

1 Answers