goto next blank row and paste

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
515
Hi,

i have this code:-



VBA Code:
Sub sortRawData()
'
' sortRawData Macro

    Sheets("Raw").Select
    Range("B1").Select
    Selection.Cut
    Sheets("New_unregistered_devices_requir").Select
    Range("B2").Select
 ActiveSheet.Paste
    Sheets("Raw").Select
    Range("B2").Select
    Selection.Cut
    Sheets("New_unregistered_devices_requir").Select
    Range("C2").Select
    ActiveSheet.Paste
    Sheets("Raw").Select
    Range("B3").Select
    Selection.Cut
    Sheets("New_unregistered_devices_requir").Select
    Range("D2").Select
    ActiveSheet.Paste
    Sheets("Raw").Select
    Range("B4").Select
    Selection.Cut
    Sheets("New_unregistered_devices_requir").Select
    Range("E2").Select
    ActiveSheet.Paste
    Sheets("Raw").Select
    Range("B5").Select
    Selection.Cut
    Sheets("New_unregistered_devices_requir").Select
    Range("F2").Select
    ActiveSheet.Paste
    Sheets("Raw").Select
    Range("B6").Select
    Selection.Cut
    Sheets("New_unregistered_devices_requir").Select
    Range("G2").Select
    ActiveSheet.Paste
    Sheets("Raw").Select
    Range("B7").Select
    Selection.Cut
    Sheets("New_unregistered_devices_requir").Select
    Range("H2").Select
    ActiveSheet.Paste
    Sheets("Raw").Select
    Range("B8").Select
    Selection.Cut
    Sheets("New_unregistered_devices_requir").Select
    Range("I2").Select
    ActiveSheet.Paste
    Sheets("Raw").Select
    Range("B9").Select
    Selection.Cut
    Sheets("New_unregistered_devices_requir").Select
    Range("J2").Select
    ActiveSheet.Paste
    Sheets("Raw").Select
    Range("B10").Select
    Selection.Cut
    Sheets("New_unregistered_devices_requir").Select
    Range("K2").Select
    ActiveSheet.Paste
    Sheets("Raw").Select
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    Sheets("New_unregistered_devices_requir").Select
    Range("A2").Select
End Sub

sometimes there is more than one entry to copy over, and I was wondering if some clever coder can sort so that it would paste the data into the next available blank row and a Yes No box would pop up asking to repeat or end?

MTIA & stay safe
Trevor3007
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub sortRawData()
   Dim Ws As Worksheet
   Dim i As Long
   
   Set Ws = Sheets("Raw")
   With Sheets("New_unregistered_devices_requir")
      For i = 1 To Ws.Range("B" & Rows.Count).End(xlUp).Row Step 10
         .Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 10).Value = Application.Transpose(Ws.Cells(i, 2).Resize(10).Value)
      Next i
   End With
   Ws.Range("A:B").Delete
   Sheets("New_unregistered_devices_requir").Select
   Range("A2").Select
End Sub
 

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
515
Where's the next entry?
That is, at the end of your macro you are removing columns A and B, does it mean that now in A and B you have the data from C and D, and there are the data to copy?

VBA Code:
Sub sortRawData()
  Sheets("New_unregistered_devices_requir").Range("B" & Rows.Count).End(3)(2).Resize(1, 10).Value = _
    Application.Transpose(Sheets("Raw").Range("B1:B10").Value)
  Sheets("Raw").Columns("A:B").Delete Shift:=xlToLeft
End Sub
thanks Dante,

sorry I misunderstood...yeah that works great.

Have a cracking easter...
Trevor3007
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,516
Messages
5,625,258
Members
416,086
Latest member
CaptainGD

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