Define column headlines in row A sheet2, before I copy from sheet1 to sheet2

Question

A kind person helped me with me previous question and this should work out the problem, and it does. Now the problem is that I have 10 files to make a data input from, so I make a data input for the first file and then I want to take the first 3 rows.

So the first row is the headline of the column, and row 2 and 3 is the data I want. How can I add some code, that will take the first three rows in sheet1, copy them, and paste them in the first 3 rows available. Then I delete the data in sheet1, make a new data input with different data (still the same headlines for the columns, and I want the same columns with copied), and gets the data in sheet2.

But the problem would be that I don't want the 3 first rows when I make my second, third and so on, data input. Every data input besides the first, I want row 2 and 3. Is there a smart way to do it?

Is it easier to define what should be in the first row in sheet2, than copy it? If it is, how can I define a headliner for each column in sheet2 before I make the copy?

Option Explicit

Sub call_copy_sub_ranges()
    Dim super_range As Range
    Set super_range = ThisWorkbook.Worksheets("input").Columns("A:T")

    Dim output_sheet As Worksheet
    Set output_sheet = ThisWorkbook.Worksheets("output")

    copy_sub_ranges super_range, output_sheet

End Sub

Sub copy_sub_ranges(ByVal super_range As Range, ByVal output_sheet As Worksheet)

    Dim r As Range

    Set r = super_range.Range("A1:A2")
    Set r = Union(r, super_range.Range("B1:B2"))
    Set r = Union(r, super_range.Range("E1:E2"))
    Set r = Union(r, super_range.Range("H1:H2"))
    Set r = Union(r, super_range.Range("I1:I2"))

    Dim offset As Long
    If IsEmpty(output_sheet.Range("a1").Value) Then offset = 0 Else offset = 1

    'output_sheet.Cells(output_sheet.Cells.Rows.Count, 1).End(xlUp) locates the bottom of anything already in column A
    r.Copy output_sheet.Cells(output_sheet.Cells.Rows.Count,1).End(xlUp).offset(offset, 0)
 End Sub

Can you add some screenshot with an example of what you want to copy to which destination exactly? It would be much easier to understand then. See also minimal reproducible example. — Pᴇʜ

JohnnieL JohnnieL · Accepted Answer · 2021-02-03T16:23:43

:o)

Is this it?

Option Explicit

Sub call_copy_sub_ranges()
    Dim super_range As Range
    Set super_range = ThisWorkbook.Worksheets("input").Columns("A:T")

    Dim output_sheet As Worksheet
    Set output_sheet = ThisWorkbook.Worksheets("output")

    copy_sub_ranges super_range, output_sheet

End Sub

Sub copy_sub_ranges(ByVal super_range As Range, ByVal output_sheet As Worksheet)

    Dim offset As Long
    If IsEmpty(output_sheet.Range("a1").Value) Then offset = 0 Else offset = 1 endif


    Dim r As Range
    Set r = super_range.Range("A1:A2")
    Set r = Union(r, super_range.Range("B" & (2-offset) & ":B3"))
    Set r = Union(r, super_range.Range("E" & (2-offset) & ":E3"))
    Set r = Union(r, super_range.Range("H" & (2-offset) & ":H3"))
    Set r = Union(r, super_range.Range("I" & (2-offset) & ":I3"))


    'output_sheet.Cells(output_sheet.Cells.Rows.Count, 1).End(xlUp) locates the bottom of anything already in column A
    r.Copy _
    output_sheet.Cells(output_sheet.Cells.Rows.Count,1).End(xlUp).offset(offset, 0)
 End Sub

Define column headlines in row A sheet2, before I copy from sheet1 to sheet2

1 Answers