goto next blank row and paste

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
667
Office Version
  1. 365
Platform
  1. Windows
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
 
hi,

I paste the data into a & b , but only column B is used.
MTIA
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

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
Back
Top