Copy range to next empty row

NikolaiJ

New Member
Joined
Feb 14, 2022
Messages
2
Office Version
  1. 2021
Platform
  1. Windows
Hi all

I hope you can help.

I have a sheet "Input" where i via VBA would like to copy data from B7:E26 and paste it in the next empty row (B:E) in the sheet "Skift".

I do not know if this has any relevance but a row in the input sheet will always be filled from B to E, but the amount of rows with content will vary from 1 (row 7) to 20 (row 26)

(I am scanning a barcode in cell C4, which then automatically fills out the B7:E26. I would then like to click on a button, transfer the content of B7:E26 to the "Skift" sheet and delete the content of B7:E26.)

Hope this makes sense.

Thak you in advance
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,299
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Try;
VBA Code:
Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Input")
    Set desWS = Sheets("Skift")
    With srcWS
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("B7:E" & LastRow).Copy desWS.Cells(desWS.Rows.Count, "B").End(xlUp).Offset(1)
        .Range("B7:E" & LastRow).ClearContents
    End With
    Application.ScreenUpdating = True
End Sub
 

NikolaiJ

New Member
Joined
Feb 14, 2022
Messages
2
Office Version
  1. 2021
Platform
  1. Windows
Hi mumps

Thank you for your suggestion. It seems to be copying and deleting it in "Input" but it is not pasted in "Skift"
 

Marc L

Banned User
Joined
Apr 5, 2021
Messages
2,030
Office Version
  1. 2010
Platform
  1. Windows
Hi, as this is just a direct copying without an useless pasting !​
According to Excel basics a VBA demonstration as a beginner starter :​
VBA Code:
Sub Demo1()
    With [Input!B7:E26]
        .Copy Range("Skift!B" & Rows.Count).End(xlUp)(2)
        .ClearContents
    End With
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,299
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not a pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 

Forum statistics

Threads
1,181,540
Messages
5,930,505
Members
436,743
Latest member
Cpdennis1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top