Cut and Paste to a new sheet

[JiminyKricket]

Hi all,

In my spreadsheet, column G, i have a number of stock tickers, occasionally i will have a currency, written GBP, NOK, SEK, USD etc.. Does anyone know how find them, cut the entire row (deleting the now blank row) and paste into a second worksheet, ensuring that the results get listed and do not overwrite eachother? (then cycles through the remaining rows..)

Any help would be greatly appreciated as i am at work and have been tasked with creating an enormous spreadsheet and have pretty much fallen at the first hurdle!



Jiminy

 

[JiminyKricket]

oh i should point out i mean using VBA not CTRL-X CTRL-V

 

[John Davis]

Hi all,

In my spreadsheet, column G, i have a number of stock tickers, occasionally i will have a currency, written GBP, NOK, SEK, USD etc.. Does anyone know how find them, cut the entire row (deleting the now blank row) and paste into a second worksheet, ensuring that the results get listed and do not overwrite eachother? (then cycles through the remaining rows..)

Any help would be greatly appreciated as i am at work and have been tasked with creating an enormous spreadsheet and have pretty much fallen at the first hurdle!



Jiminy

For a macro this does it, but I know it's not the best way (rushed).

Sub JiminyKricket()
Dim i As Long
Dim lr As Long


lr = Cells(Rows.Count, 1).End(3).Row

For i = lr To 2 Step -1

    Select Case Range("H" & i).Value
    
        Case Is = "GBP"
        
         Sheets("Destination").Range("A2").EntireRow.Insert SHIFT:=xlDown
    
    
        Range("H" & i).EntireRow.Cut Sheets("Destination").Range("A2")
   
        Case Is = "NOK"
        
        Sheets("Destination").Range("A2").EntireRow.Insert SHIFT:=xlDown
    
    
        Range("H" & i).EntireRow.Cut Sheets("Destination").Range("A2")
        
        Case Is = "SEK"
        
        Sheets("Destination").Range("A2").EntireRow.Insert SHIFT:=xlDown
    
    
        Range("H" & i).EntireRow.Cut Sheets("Destination").Range("A2")

        Case Is = "USD"

        Sheets("Destination").Range("A2").EntireRow.Insert SHIFT:=xlDown
    
    
        Range("H" & i).EntireRow.Cut Sheets("Destination").Range("A2")
    
    
        End Select
        
Next i


End Sub

 

[JiminyKricket]

Ah your a star! i spent the afternoon playing with nested 'If or' statements and just couldnt get it to work!

i do have one last question though..

once the row has been cut and pasted to the new sheet (works perfectly btw) is there a way i can delete that row (now blank) on the original sheet?

so far i have..

Dim i As Long
Dim lr As Long

lr = Cells(Rows.Count, 1).End(3).Row
For i = lr To 2 Step -1
Select Case Range("G" & i).Value

Case Is = "AUD"

Sheets("Cash Items").Range("A2").EntireRow.Insert SHIFT:=xlDown

'repeated for each currency

End Select

Next i

End Sub


Thanks for your help

 

[John Davis]

Ah your a star! i spent the afternoon playing with nested 'If or' statements and just couldnt get it to work!

i do have one last question though..

once the row has been cut and pasted to the new sheet (works perfectly btw) is there a way i can delete that row (now blank) on the original sheet?

so far i have..

Dim i As Long
Dim lr As Long

lr = Cells(Rows.Count, 1).End(3).Row
For i = lr To 2 Step -1
Select Case Range("G" & i).Value

Case Is = "AUD"

Sheets("Cash Items").Range("A2").EntireRow.Insert SHIFT:=xlDown

'repeated for each currency

End Select

Next i

End Sub


Thanks for your help

Like this perhaps:

    Select Case Range("H" & i).Value
    
        Case Is = "GBP"
        
         Sheets("Destination").Range("A2").EntireRow.Insert SHIFT:=xlDown
    
    
        Range("H" & i).EntireRow.Cut Sheets("Destination").Range("A2")
      
        Range("H" & i).EntireRow.Delete Shift:=xlUp

Add the last line between each Case. Hope I've been clear.

 

[JiminyKricket]

Excellent required a small bit of adaptation but works now perfectly!

Many many thanks for your help

 

[John Davis]

Excellent required a small bit of adaptation but works now perfectly!

Many many thanks for your help

You're welcome. Glad I could help out.