Need help copying to another tab . .

Closed
blenderman Posts 1 Registration date Thursday July 30, 2015 Status Member Last seen July 31, 2015 - Jul 31, 2015 at 01:16 AM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Jul 31, 2015 at 03:39 AM
Hello,

I need to copy a row (multiple rows) if a cell in that row contains certain text. For example if C12 contains the word "expense" I need the entire C row copied to another tab or sheet (or possibly another worksheet). I'll be honest I'm new to macros but willing to learn and try it out! Thanks in advance for your help

Brad

1 reply

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Jul 31, 2015 at 03:39 AM
Hello Brad,

Perhaps the following code may help:-

Sub CopyIt()

Application.ScreenUpdating = False

   Dim lRow As Long

lRow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("SHeet2").Range("A2:J" & lRow).ClearContents

Sheets("Sheet1").Select

For Each cell In Range("C2:C" & lRow)
      If cell = "Expense" Then
      cell.EntireRow.Copy
      Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      End If
  Next

Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Sheet2").Select

End Sub


Following is a link to my test work book for you to peruse:-

https://www.dropbox.com/s/yeagzj8lrzvbk26/Brad.xlsm?dl=0

You'll see two sheets. Sheet 1 as your "Input" sheet and Sheet 2 where you want the data stored. Click on the "Copy to Sheet 2" button to activate the code and the transfer of data. The code searches Column C for any cell with the criteria "Expense" in it and transfers each relevant row of data associated with the criteria to sheet 2. "Expense" is case sensitive.

To implement the code into your work book, press ALT + F11 which will open the Visual Basic Editor. From the ribbon at the top, select "Insert" and from the menu that appears, select "Module". The big grey field will turn white and in this field, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.
0