Insert Range of cells from one sheet to another with shifting cells down.

[PitMax]

Hi,
I am looking for the VBA that will copy and insert the range of cells from one sheet to another within the same workbook.

Ideally would be to run macro with button on the source sheet.

All items should be copied and inserted to blank document. The number of items (rows) in source sheet might be different (1~300). If it helps, I have available the cell that counts the number of lines (rows) that should be inserted.

After press the button, the ready document should have all items inserted and the cells in columns B8:F8 should be shifted down.

It is important to keep document formatting (border lines, font etc.)

It would be great If I could also back to blank document.

Does anyone know how it can be done?

Thank you in advance

 

[mohadin]

Simply

Sub test()
    Range(Range("A3:E3"), Range("A3:F3").End(xlDown)).Copy
    Sheets("Sheet2").Range("B7").Insert Shift:=xlDown
End Sub

"It would be great If I could also back to blank document."

Sub test()
Dim rng As Range
Set rng = Range(Range("A3:E3"), Range("A3:F3").End(xlDown))
    rng.Copy
    Sheets("Sheet2").Range("B7").Insert Shift:=xlDown
    rng.ClearContents
End Sub

 

[PitMax]

Thank you
In my spreadsheet the range on source sheet is every time different. There are the same columns, but number of rows is changing.
Is there any solution, that excel will automatically move and insert the required cells from the table (excluding table headings)?
i.e. on source sheet, in column AA there could be the control letter "X" in all rows that should be copied to blank document.

....and clear document to blank one. (i.e. In column AA in document there would be the control mark "X" in all lines that belong to blank document, so all rows that has no "X" can be deleted)

Do you think it might work in this way?

 

[PitMax]

Simply

Sub test()
    Range(Range("A3:E3"), Range("A3:F3").End(xlDown)).Copy
    Sheets("Sheet2").Range("B7").Insert Shift:=xlDown
End Sub

Sub test()
Dim rng As Range
Set rng = Range(Range("A3:E3"), Range("A3:F3").End(xlDown))
    rng.Copy
    Sheets("Sheet2").Range("B7").Insert Shift:=xlDown
    rng.ClearContents
End Sub

Above code clears the source sheet (Sheet 1)
Regarding "back to blank document" I was rather thinking about the sheet2 document and the command button placed on sheet2 that restore the document to the prior insert look - (see the picture)

It could also be the command button that will be placed on sheet1, so the first step will be restore Sheet2 document to blank, and the second step will be copying and inserting the range from Sheet1.
In this way the user will be sure that copied data will be inserted to blank document and won't be duplicated or added to former/existing data.

 

[mohadin]

It could also be the command button that will be placed on sheet1, so the first step will be restore Sheet2 document to blank

Sub test()
    Dim rng As Range
    Dim lr&
    With Sheets("Sheet2")
        lr = .Cells(Rows.Count, 5).End(xlUp).Row - 7
        If lr > 1 Then
            With .Cells(7, 2)
                .Resize(lr, 5).ClearContents
                .Resize(lr - 1, 5).Delete xlUp
            End With
        End If
    End With
 Range(Range("A3:E3"), Range("A3:F3").End(xlDown)).Copy
    Sheets("Sheet2").Range("B7").Insert Shift:=xlDown
End Sub

 

[PitMax]

It works great!!
Thank You Mohadin! You are a star!

 

[mohadin]

You are very welcome
And thank toy for the feedback
Be happy and safe

 

[PitMax]

I have found one small issue. When I have one item (one row), the Run-Time error '1004' appears:

What should be amend in the code to:
1) Copy and insert 12 columns.

2) run the button on the sheet 3 to copy from sheet1 to sheet2

3) is it possible to run this macro with "clean document" effect to more than one sheet, so by clicking the button the data will be inserted to more than one document from the same source sheet?
(i.e. by describing exact name of the target sheet in the code)

Cheers,

 

[PitMax]

"Small" update

What should be amended in the code to copy and insert 11 columns:

If on the Sheet1 L6="" than source sheet data range to copy starts in C8:M8, otherwise C8:K8 & N8:O8

Destination document #1: Sheet2 - the first row of inserted data If L6="" than consequently (A21:K21) otherwise C8:K8 to A21:I21 & N8:O8 to J21:K21

Destination Document #2: Sheet4 - the first row of inserted data (A10,C10:E10) , so the cells in the first row should be copied and inserted as follow: C8 to A10, D8 to C10, E8 to D10, K8 to E10

I would like to run the code with button which is placed on the sheet 3 and already has one macro that is unhiding requested sheets (see below).
________________________________________________________________

Private Sub CommandButton3_Click()

'Unide all sheets that contain a value in a specific cell

Dim ws As Worksheet

'Unide sheets with value of Hide in cell AA2
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("AA2").Value = CELLVALUE Then
ws.Visible = xlSheetVisible
End If
Next ws

End Sub
________________________________________________________________


Could you please advise me how to run both macros from on the same button click:
1# copy and insert
2# unhide...

Cheers,

 

[mohadin]

Confused