Posts by Binning

  • Copy all rows if cell contains specific text string

    I've tried looking through the forum for this but I can't seem to find anything that suits what I need.


    I need to create a macro which selects all the rows containing a specific word in column A then pastes these rows into another worksheet.


    I don't know if it's worth mentioning that column A will already be in alphabetical order, so the text strings will already be grouped together one rows after the other.


    The logic of the macro would be as follows:


    Find all cells in column A containing word "Banana"
    Select entire rows
    Copy all these rows to worksheet "Sheet2"


    Thanks :)

  • Range between two key words in specific column

    Hi there,


    Currently running a macro which selects all the rows between two specific words and pastes the selection into another worksheet. However, it runs this search on the entire workbook, whereas I'd just like it to run the search in a particular column (column D in this instance).


    This is the code as it currently stands:


    Code
    With ActiveSheet
    .Range(.Cells.Find("financial assets"), .Cells.Find("liabilities")).EntireRow.Select
End With
    
Selection.Copy
Sheets("Paste Currency").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


    Is there any way to focus the search on only one column?


    Thanks,
    Binning

  • code not to copy formula

    Re: code not to copy formula


    Text formula?


    For instance, in sheet 3:


    =TEXT(SHEET2!I3,"£0.00")


    So for cell I3 on Sheet3, the formula would be:


    =IF(SHEET2!M3="Y",TEXT(SHEET2!I3,"£0.00"),"")

  • Copy and Paste into next available cell on new worksheet

    Re: Copy and Paste into next available cell on new worksheet


    Hi Smallman,


    Thanks for the quick reply. I feel I may have explained this very confusingly. I will be pasting data into Sheet2 from another file entirely and then I need that information to be copied across to Sheet1.


    The way it's layed out so far is I'll be pasting information from another workbook into a new sheet (call it sheet 3). Sheet 2 will then do an Index-Match on sheet 3 to pull all the values I need. However, this will leave zero values, and for the purposes of the excel file I can't have any formulae anywhere. Thus, I need to select all the data in Sheet2 which has been found using Index-Match and paste it into the next available row in Sheet1, pasting values only.


    I hope this isn't too confusing, I feel like I've explained it horribly.

  • Copy and Paste into next available cell on new worksheet

    Hi there,


    This is my first post on the forum and I'm not entirely competent with VBA (yet).


    I was looking for a macro which will select all of the data on one sheet (call it Sheet2) from cells A2 onwards and paste it into the next available cell on another sheet (call it Sheet1).


    For instance, if I paste data into Sheet2 which populates cells B2:K8, I would want that to copy across to A1 on Sheet1. Then, if I removed the information on Sheet2 and pasted new data which populates cells B2:K9, I would want that to copy across starting on cell A8 on the next sheet (and then A16 when I repeat the process again, and so on).


    I would also like it to paste only the values and number formatting.


    EDIT:


    As a side note, it would be helpful for the data selection from A2 onwards to be Visible Cells only, as I'm currently running a macro which hides 0 values before copying it across.


    Thanks!