Copy and paste from one worksheet to another depending on date

[darrenb78]

Hello

I am only new to this forum and I have only recently started to work with macros. I have very limited exposure to programming, so I am looking to improve my knowledge. I am currently stuck trying to set up a new macro button. I have a main worksheet that a user can input data into. I have made a macro for this to copy the contents into another worksheet, which works fine. However I know what to cut or copy this date from this worksheet into another worksheet depending on the date in the first field. I have recorded a macro to do part of this (the cut and paste bit) but I cannot seem to get it to loop properly. Any help I can get on this would be extremely helpful. I would also appreciate an explanation on the solution if there is any. I will post the workbook if needs be.

Please Login or Register  to view this content.

Thank you
Darren Brady

I have also tried the below but it is giving me a value mismatch. This is kind of what I want to do except I want it to paste into a new tab based on the month

Please Login or Register  to view this content.

Please also see a copy of my spreadsheet

[JOHN H. DAVIS]

Can you attach a copy of your spreadsheet?

[darrenb78]

Hello I am new to this I have tried to add a file and it does not seem to be working. It is an Xlsm and only 78 kb, but it wont upload. Do you have any ideas?

[JOHN H. DAVIS]

Did you try the following? To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[darrenb78]

Hi I dont think I am allowed to upload anything. Based on the information giving ion my previous email would I be able to get a suggestion on how to move forward. Thanks

[6StringJazzer]

All registered members are allowed to upload.

To post a file:

1. Under the text box where you type your reply click the Go Advanced button.
2. In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
3. In the pop-up, click Add then Select to select a file from your local drive
4. Click the Upload button to upload the file
5. Click Done to attach it.

It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

[darrenb78]

hello All

I am trying to do all the below but it does not seem to be working. I might not be able to upload due to restrictions on my firewall (that i dont have access to).

Thanks

[darrenb78]

Hello All

I finally figured out most of what I was trying to do. But I am getting an error in my code.

Please Login or Register  to view this content.

This code is activating even if the cell (activecell) is a blank cell. Is there a way to fix this? Let me know if more information is required Thanks

[6StringJazzer]

Excel treats a blank cell as a zero if you use the cell for arithmetic. Due to the way Excel treats dates, a zero value for a date is interpreted as December 30, 1899. I suggest

Please Login or Register  to view this content.

Also, your code activates every cell it wants to work on. This is not causing your problem but is unnecessary, and there are reasons it is undesirable. I could make better suggestions if you provide all the code, or if you attach your file as I requested earlier.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[darrenb78]

Thank you for your replies

I will try both suggestions. Please see my full code below. As I previously said I am only a beginner so my code might not be as precise as it should be. The function of the code is to check the first field and if it is a date, to calculate the date and then copy and paste the range a1:m1 into the relevant tab. Again thank you for any help

Darren Brady

Please Login or Register  to view this content.

[darrenb78]

Hello

Thank you for the information. I tried the solution above and it worked perfectly.

@6stringjazzer, no there is no reason why I need to activate them except for evaluating what month the first cell in the row A:m is so to copy this row into the relevant tab. Any suggestions you may have to clean up and speed up the code would be appreciated. Thank you. Also as I am only new to VBA, would I be able to get an explanation for any changes to build up my knowledge

Thank you
Darren

[6StringJazzer]

Your code indicates that you have some confusion about how loops work. There are a number of problems and I don't know if the code does what you want it to do. I can offer some significant improvements using the following assumptions:

Code is in a Module, such as Module1
Active sheet when calling this Sub is "stats"
The sheets where you are doing the paste (09, 10, 11, 12) are in the same workbook as "stats"

I have compiled this but not tested it. I did not have time to create a workbook that mimics what I could only guess that yours looks like.

Please Login or Register  to view this content.

[darrenb78]

Hello 6StringJazzer,

Thank you for the above code. Your assumptions are correct. I did get the code to work (sort of) but it was taking a while to run through the spreadsheet. I have only recently tried to look into doing macros so this is all new to me. I input your code as above but I seem to be getting a runtime error on the below

Please Login or Register  to view this content.

Do I not have to evaluate the month of the curcell before making it the dest spreadsheet?

Thanks for your all your help so far.

Regards
Darren

[6StringJazzer]

Do I not have to evaluate the month of the curcell before making it the dest spreadsheet?

Indeed you do, good analysis. I missed that due to the inability to test.

Please Login or Register  to view this content.

[darrenb78]

Hello 6string


Apologies about this again but I am now getting an applicationdefined or user defined error on the below

Please Login or Register  to view this content.

Again any help you can give me would be great.

Darren

[6StringJazzer]

I really don't know how to diagnose that without having your file. Here is a mock-up I did based on your description and this version is working perfectly. If you can't adapt it to your file then you need to identify exactly what's different between your file and mine.