Copy called out items from sheet1 to sheet2

**MICowboy13** · 01-09-2014, 10:35 AM

Good morning,

I have attached a sample workbook. On sheet1 I have inventory items, I need a VBA code to copy only certain items with its corresponding row to sheet2. I believe something similar to a vlookup can be used. I would like to call the items to sheet2 by column A (Part#). Rows will change so i am not able to just call out a row.

To clear things up I would like only the data from Item2, Item4, and Item9 to be copied to sheet2 with all of it's columns (A:F)

Book1.xlsx

Thanks in advance!

**Steve@Rugby** · 01-09-2014, 10:48 AM

Hi,

Not sure if i understand what you need here, what is the trigger to copy rows? Is it always going to be copy row if column "A" contains string text Item2, Item4 or Item9

Regards
Steve

**MICowboy13** · 01-09-2014, 10:57 AM

Steve,

I plan to just add a button on sheet 1 to always copy the same row of item info no matter where they are in the sheet. It will always be the same items (ex. Item2, Item4, and Item9) so I was hoping I could make some kind of list in a VBA. I can accomplish this by =Vlookup("Item1",Sheet1,A:F,1,False) but it would be too much to do in every cell.

I think something like this is possible, but i can't be sure.

I hope this answers your question

Thank you

**JOHN H. DAVIS** · 01-09-2014, 11:24 AM

Maybe:

Sub MICowboy13()
Dim rcell As Range
Application.ScreenUpdating = False
For Each rcell In Range("A2:A" & ActiveSheet.UsedRange.Rows.Count)
    If rcell.Value = "Item2" Or rcell.Value = "Item4" Or rcell.Value = "Item9" Then
        rcell.EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
    End If
Next rcell
Application.ScreenUpdating = True
End Sub

**Steve@Rugby** · 01-09-2014, 11:37 AM

Go with John's code if i were you. I'm just a beginner!

**Steve@Rugby** · 01-09-2014, 11:34 AM

Hi,

This is a bit crude but it seems to work, should help if no one else can give a more elegant bit of code.

Sub CopyItems()

 Sheets("Sheet1").Select
    Cells.Select ' Select all cells
    Selection.AutoFilter ' Apply autofilter
    ActiveSheet.Range("A" & Rows.Count).AutoFilter Field:=1, Criteria1:=Array("Item2", "Item4", "Item9"), Operator:=xlFilterValues ' Filter Item1, 2 & 9 in column A

    With ActiveSheet.UsedRange
        .Offset(1, 0).Resize(.Rows.Count - 1).Select 'Selects used range in active worksheet minus row 1 using "offset"
        Selection.Copy ' Copies selected range
    End With
    
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("H4").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False

End Sub

Regards
Steve

**MICowboy13** · 01-09-2014, 12:19 PM

Both of these accomplish what i was looking for. Thank You John ans Steve!

If you have a few extra minutes i could use a code for to add the sum of column F to cell "G2". If not that is okay too.

**JOHN H. DAVIS** · 01-09-2014, 12:45 PM

Originally Posted by MICowboy13

Both of these accomplish what i was looking for. Thank You John ans Steve!

If you have a few extra minutes i could use a code for to add the sum of column F to cell "G2". If not that is okay too.

You're welcome, try:

Sub MICowboy13zz()
Application.ScreenUpdating = False
With Range("G2")
    .Formula = "=SUM(F2:F" & Range("F" & Rows.Count).End(3)(1).Row & ")"
    .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

Copy called out items from sheet1 to sheet2

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy called out items from sheet1 to sheet2

Re: Copy called out items from sheet1 to sheet2

Re: Copy called out items from sheet1 to sheet2

Re: Copy called out items from sheet1 to sheet2

Re: Copy called out items from sheet1 to sheet2

Re: Copy called out items from sheet1 to sheet2

Re: Copy called out items from sheet1 to sheet2

Re: Copy called out items from sheet1 to sheet2

Thread Information

Users Browsing this Thread

Similar Threads

match data of sheet1 and Sheet2 ColumnA, if matched copy columnB of sheet1 to sheet2

[SOLVED] If cell value on Sheet1 not found on Sheet2 copy row to Sheet2

[SOLVED] Copy and Paste Entire Row from Sheet1->Sheet2 based on text string match in Sheet1 Row

how to copy sheet1 to sheet2 and refresh sheet1

IF Sheet1 Cell A2 = value, THEN copy Sheet1 Row 2 to Sheet2 Row 2....?

Bookmarks

Bookmarks

Posting Permissions