copying columns from one sheet to another

I know i'm doing this wrong, so any help is appreciated

this is the code I am using:

Please Login or Register  to view this content.

I'm trying to copy rows from the active sheet(where the button is located)
and copy them into another sheet "Modify Data"

It is faulting on this line:

Please Login or Register  to view this content.

I tried that and its still highlighting the same line and saying "type mismatch"

I have amended the code
You will get some funny result if your active sheet happens to be Worksheets("Modify Data")
You might need to change the name of active sheet in to an actual sheet name.

it's still telling me type mismatch.

I have attached the workbook

Emil,
Try the attached.
You can not add/sub an entire column from another column, so I commented that line as you will get an error. You can do it manually.

Do not also put your code on sheets, but should be in standard module.

okay, great it works!

Is there a way for me to not have to do that manually? I won't be the one using the workbook, and there are still a bunch of steps I need to complete after this one.

as an option

Please Login or Register  to view this content.

Okay I will try that, can you explain what "=RC[-1] - RC[-2]" means?

It may be, but the dates are formatted as text, I can not even add/sub them manually. Dates are nightmare in excel

the original workbook I sent you, the dates were add/sub.

and they are formatted as text?

do you know why its not working once ran through the macro?

Nilem's RC formula works, but you have lots of date which are not dates.
For e.g. Look at rows 20-22, they have dates
01/01/40
01/01/40
01/01/40

You can not have also a zero date, that is, if the two columns have the same date.

Emilyloz,
change this line in my code

Please Login or Register  to view this content.

This code works for me in your file

Thanks nilem.

Quick question:
if I have code in modules, do I also have to keep them in the sheet code as well?

I removed them from the sheet and nothing was working, so now I have them in both the sheet and module and its working fine. Is this coincidence?

Emil,
No,
You should put your code in standard module unless it is an event driven code. An event code where the code is triggered by what goes in a sheet events (hence the reason for storing it on sheet). For instance, by selecting a range, or change in range, so when this happens the code is auto triggered.
To find out which of your codes are working:
Go to each code, put your cursor with in the code and step over the code by using F8. This will tell you if you have some unknown event code which could trigged the codes. If the code runs smoothly, the code should finishes and see the result.

they both are running smoothly.

where should the code be located?
module?
sheet?
or both?

In the VBA project,
You have modules, sheet1,sheet2 and thisworkbook.
If the code is en event driven code, it should go in either sheet, or thisworkbook.
To insert(Create a new module)
Right click on the VBA project then insert,
OR
on the menu-Insert then module.
So unless otherwise stated, the default storing place for a code is in "MODULE"

sorry,

does a button click count as event driven?

No, the button is linked to a code.
Remember, the button is created in excel and could be attached to a code.
I do not think you can run an event driven code from a button(Though not 100%).

Okay.

well what I was saying is that these codes:

Please Login or Register  to view this content.

do not work when only in a module.

They are currently working (with the code being in both the module and the sheet code)
and they work when only in the sheet code.

and what youre explaining seems to be contradicting that?

How do you know if code works or not?
You have click buttons for both codes. The functions of these buttons is just for ease of running the codes from buttons instead of going to the module to run them. Nothing else!

You need to check what codes are attached to these buttons.

You may be clicking these buttons hoping to run these codes, but these buttons may be attached to different codes.

I have commented out the code in the sheets and tried using the buttons, and nothing worked.

I have commented out the modules with the code being in the sheets, and the buttons worked and did everything they were supposed to do.

when I have the same code in both their own modules and in the sheet, it works as well.

Emile,
I have seen lots of people put all their codes either in sheet or thisworkbook, not in a Module.
I personally would not since I put in lots of stuff on VBA project, I would not know what would trigger my codes and this will have unexpected consequence.
When you say, you can run your codes from sheet, how do you run them, from a button?
If you look my code and Nilem's one they are not linked to any button, so you have to physically go in to the module to run them, unless of course ,you have attached them to buttons.

Yes, I use a button for both of them.

They are both under a private sub button_click() of some sort.
I do not understand what you are explaining to me.

If you look my code and Nilem's one they are not linked to any button

And if you look at my workbook. you will notice that the same code lies with in module 1 and 2 as it does in Claimskl

They do not have Click()
I suspect this is a button