Need help copying to another tab . . [Closed]

Report
Posts
1
Registration date
Thursday July 30, 2015
Status
Member
Last seen
July 31, 2015
-
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
-
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

Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!