Pulling info from one sheet and placing in another sheet...

[jwdemo]

Sheet: Daily Sales
Column A: UPC
Column B: Price
Column C: Qty
Column D: Extended Cost

Data starts on Row 3.

Sheet: Export
Column A: UPC
Column B: QTY

Data should start on Row 2.

I am wanting a macro that would look at Sheet: Daily Sales Column A, and if a cell contains a number, copy that number as well as the number in column C from the same row and paste it into the next free row on Sheet: Export. Then, clear the data from Sheet: Daily Sales starting from cell A:3 down.

 

[My Aswer Is This]

Try this:

Sub Export_Me()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Dim Lastrow2 As Long
Sheets("Daily Sales").Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrow2 = Sheets("Export").Cells(Rows.Count, "A").End(xlUp).Row + 1
    For i = 3 To Lastrow
        If IsNumeric(Cells(i, 1).Value) Then
            Sheets("Export").Cells(Lastrow2, 1).Value = Cells(i, 1).Value
            Sheets("Export").Cells(Lastrow2, 3).Value = Cells(i, 3).Value
            Lastrow2 = Lastrow2 + 1
        End If
    Next
    Lastrow3 = Cells(Rows.Count, "A").End(xlUp).Row + 1
    Range(Rows(3), Rows(Lastrow3)).ClearContents
Application.ScreenUpdating = True
End Sub

 

[jwdemo]

When I run the macro, I noticed that there are two things that should be changed.

1. The formatting in column D is removed from the lines that had items in them. I'd like to keep the formula and formatting.
2. Would it be possible to get column A pasted in column A and column C pasted in column B? (right now A goes to A and C goes to C)

Is there any way to enable this macro to run on a protected sheet? Perhaps it could be unprotected at the beginning of the macro and then protected after it has run?

 

[My Aswer Is This]

I'm a little confused. Your original post said:
Sheet: Daily Sales
Column A: UPC
Column B: Price
Column C: Qty
Column D: Extended Cost

Data starts on Row 3.

Sheet: Export
Column A: UPC
Column B: QTY
So I need to know exactly what you want.
The data in sheet Daily sales in going where in sheet Export.
Your original post did not mention where the data is column C and D was to go.
At the present time this script is not putting anything in column D

 

[jwdemo]

I wanted the data from Daily sales sheet to go to Sheet export. The Column A from daily sales would go to Export column A. Column c from Daily sales would go to Export column b.

The formatting on sheet Daily Sales column D gets removed when I run the macro. I was hoping to keep that formatting so the macro could be run again without having to reformat cells.

Thanks for your time on this!

 

[My Aswer Is This]

Try this:

Sub Export_Me()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Dim Lastrow2 As Long
Sheets("Daily Sales").Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrow2 = Sheets("Export").Cells(Rows.Count, "A").End(xlUp).Row + 1
    For i = 3 To Lastrow
        If IsNumeric(Cells(i, 1).Value) Then
            Sheets("Export").Cells(Lastrow2, 1).Value = Cells(i, 1).Value
            Sheets("Export").Cells(Lastrow2, 2).Value = Cells(i, 3).Value
            Lastrow2 = Lastrow2 + 1
        End If
    Next
    Lastrow3 = Cells(Rows.Count, "A").End(xlUp).Row + 1
    Range(Rows(3), Rows(Lastrow3)).ClearContents
Application.ScreenUpdating = True
End Sub

This script makes no changes to Column "D" value or formatting