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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
How about this for part 1
VBA Code:
Sub sortRawData()
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Raw")
   With Sheets("New_unregistered_devices_requir")
      .Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 10).Value = Application.Transpose(Ws.Range("B1:B10").Value)
   End With
   Ws.Columns("A:B").Delete Shift:=xlToLeft
   Sheets("New_unregistered_devices_requir").Select
   Range("A2").Select
End Sub
If there is more data to copy, what range should be copied?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
sometimes there is more than one entry to copy over
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
 

Trevor3007

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

wow that code is FAST and works great too :}

the source is always copied to the same location , and the format it comes is vertical and not horizontal ... as shown below: in the test data below-

hello goodbye
yes no
me you
etc etc2


so have to do it one by one hence my i have the VB to reformat so it can then be transferred on to another worksheet, which is then passed onto the end user.


If I were to copy from where I obtain ( data collated is not in spreadsheet ) the data and paste into and there was more than 1, it would look like this in the Raw worksheet:-

hello goodbye
yes no
me you
etc etc2
hello2 goodbye2
yes2 no2
me 2 you2
etc2 etc2
hello3 goodbye3
yes3 no3
me 3 you3
etc3 etc3
hello4 goodbye4
yes4 no4
me 4 you4
etc4 etc4

Obviously it would be FANTASTIC if there was away...but ATM i can only do 1 at a time ( hence why the Yes No and copy to next available blank row)

I hope this makes sense and many thanks again Fluff.
KR
trevor3007
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do you want to copy all rows, rather than just the first 10, or will there only ever be 10 rows?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
This will do 10 rows for as many columns as there are with data in row 1
VBA Code:
Sub sortRawData()
   Dim Ws As Worksheet
   Dim i As Long
   
   Set Ws = Sheets("Raw")
   With Sheets("New_unregistered_devices_requir")
      For i = 2 To Ws.Cells(1, Columns.Count).End(xlToLeft).Column
         .Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 10).Value = Application.Transpose(Ws.Cells(1, i).Resize(10).Value)
      Next i
   End With
   Ws.UsedRange.EntireColumn.Delete
   Sheets("New_unregistered_devices_requir").Select
   Range("A2").Select
End Sub
 

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
515

ADVERTISEMENT

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
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?

Hello Dante,

thanks for getting back to me . As I copy more data into the same area to run the code so it paste's it into the applicable, this why I need to del cols A:B...
Hence why I need a YesNO to repeat and if Yes, run the code and paste into the next available blank row.

The data added into the Raw worksheet, comes a from source that is not in excel format and is vertical not horizontal format this is why i need to reformat to suit the end user. I may have to do this several times , but due to the original format, I have to do this singular .

Hope this makes sense?

MTIA & stay safe.
Trevor3007
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
As I copy more data into the same area to run the code so it paste's it into the applicable, this why I need to del cols A:B...
Hence why I need a YesNO to repeat and if Yes, run the code and paste into the next available blank row.
I don't understand, do you have more data in columns C onwards?

The way the 2 macros are for Fluff and mine, each time you run it, it will paste the data in the next available row.
 

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
515
This will do 10 rows for as many columns as there are with data in row 1
VBA Code:
Sub sortRawData()
   Dim Ws As Worksheet
   Dim i As Long
  
   Set Ws = Sheets("Raw")
   With Sheets("New_unregistered_devices_requir")
      For i = 2 To Ws.Cells(1, Columns.Count).End(xlToLeft).Column
         .Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 10).Value = Application.Transpose(Ws.Cells(1, i).Resize(10).Value)
      Next i
   End With
   Ws.UsedRange.EntireColumn.Delete
   Sheets("New_unregistered_devices_requir").Select
   Range("A2").Select
End Sub
Hi Fluff,

many thanks for your sift reply. yeah that works , great job :}

if this was the the data in raw:-
1
2
3
4
5
6
7
8
9
10
twenty
me
oyu
them
police
car
drum
pad
mouse
cat
skyfall
spectre
dr no
goldfinger
thunderball
ohmss
you only live twice
goldeneye
from russia from love
living daylights
1
2
3
4
5
6
7
8
9
10
twenty
me
oyu
them
police
car
drum
pad
mouse
cat
skyfall
spectre
dr no
goldfinger
thunderball
ohmss
you only live twice
goldeneye
from russia from love
living daylights



is it possible to do this using the above data ? notice that that there are only 10 lines per section?

if not..can the yes/no message box be done?

MTIA
Trevor3007
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Is your data in one column or multiple columns?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,540
Members
416,923
Latest member
jarri

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