goto next blank row and paste

[Trevor3007]

Hi,

i have this 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

 

[Fluff]

How about this for part 1

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]

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?

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]

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]

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

 

[Fluff]

This will do 10 rows for as many columns as there are with data in row 1

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]

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?

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]

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]

This will do 10 rows for as many columns as there are with data in row 1

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]

Is your data in one column or multiple columns?