VBA to Copy over Certain Information between Sheets

[Boots6]

Hello,

I have made this very basic macro to transfer certain information to Sheet2 from Sheet1 with IF/Then statements:
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Range("A4").Select
ActiveCell.FormulaR1C1 = _
"=IF('Credit Card Expense'!R[8]C[4]=""6575"",'Credit Card Expense'!R[8]C,""0"")"
Range("A4").Select
Selection.Copy
Range("A5:A42").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-14
Range("B4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF('Credit Card Expense'!R[8]C[3]=""6575"",'Credit Card Expense'!R[8]C,""0"")"
Range("B4").Select
Selection.Copy
Range("B5:B41").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-8
Range("G4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF('Credit Card Expense'!R[8]C[-2]=""6575"",'Credit Card Expense'!R[8]C[-1],""0"")"
Range("G4").Select
Selection.Copy
Range("G5:G41").Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

__________________________________________________________________

I would like to figure out how to make it VBA code so that it runs this process, but also eliminates all of the rows that inevitably will be filled with "0"'s when the statement is False. Help? Thanks!

 

[SkipVought]

So what is in columns c-f and to the right of g?

Why are rows 4 to 41 significant?

Will you need to do something with values other than 6575?

Your self-imposed limitations, as stated in your original post in forum68, may end up making your job more difficult.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Boots6]

Columns C and F are different on sheet 2. It's an expense report and Sheet2 is for meals only. This takes the meal code "6575" and transfers the relevant information over to the "Meal Tab" page. The rows are important because 4 is where the input starts, the rest above is titles and column headers. It's a corporate form I'm trying to improve, I don't have the authority to really change it completely. I just wanted to do something to make it more time-efficient while at the same time easy to use for people that don't know excel very well. (like me)

 

[SkipVought]

Select all your data

COPY

Edit > Paste Special -- VALUES (now the formulas are GONE!)

Turn on your AutoFilter and display the rows containing ZEROS.

SELECT those rows

Right-Click > Delete

Remove the filter value. (Now you will have not rows containing ZERO)



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}