Transfering data from one worksheet to another

[Dan Vollmer]

Hi,

I am trying to transfer data from one worksheet to another worksheet. The first worksheet is contains up to 100 days of data which includes billable hours. During the billing process I open a user form in which the beginning billing date is entered. I then calculate the ending date of the month and with this information I can determine both the beginning and ending date columns and I also know the beginning and ending row columns. I then want to transfer the data in this block of cells to a second worksheet which is the billing worksheet. In the second works sheet I have defined the block of cells as PTReport1Data. I am trying to use the upper left and lower right cells to define the cells on the first worksheet that I want to copy and then paste them into the second worksheet. I have included the code below. I keep getting an error when I get to the copying line of code and not sure what I am doing wrong. Thanks for your help.

Please Login or Register  to view this content.

Thanks again,

Dan

[Leith Ross]

Hello Dan Vollmer,

I keep getting an error when I get to the copying line of code and not sure what I am doing wrong.

It is difficult to evaluate what the problem is because there is no code in the macro that either copies or pastes data.

When you post about errors you should at least include the error code.

[AlphaFrog]

Hard to diagnose without data.

My guess is C1 is not calculated correctly. If BSD is the beginning date and TSD is the end date, then your C1 calculation will return a negative value which in turn will cause the error on the Range.Select line.

I think you may just have to swap the dates in the C1 calculation.

C1 = TSD - BSD + 5

Again, this is a total guess.

Debug your code:
When the code errors, hover the mouse cursor over the variables within the line of code that errors and examine their values from the pop up comment.



TIP: this will determine the Length of month including for February leap years.

Please Login or Register  to view this content.

The way it works is Day-zero of the next month is the last day of this month.
The -1 subtracts one more day which is what your current method is doing.

[Winon]

Hello Dan Vollmer,

Why not just try it like this?

Please Login or Register  to view this content.

Mabe that could help?

[Dan Vollmer]

Sorry about not giving the error code. Here it is: Run time error '1004': Select method of range class failed.

Please let me know if you need more information. Thanks

Dan

[Dan Vollmer]

The problem with using your suggestion is that I only want a block of cells inside the first worksheet to be copied into the second worksheet but not in the same relative position as the data was in the first sheet. In the first worksheet the data block I want may start at cell 5,12 but in the second work sheet the same cell my start in postion 7,10.

Thanks

Dan

[Leith Ross]

Hello Dan Vollmer,

If you post a copy of your workbook, all these question can be addressed and a working solution can posted.

How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

[Dan Vollmer]

Attached please find the workbook which I am working on. Please note that PT-Treatments is the first workbook I am copying from and PT-Monthly Report 1 is the workbook I am pasting the information into.

Thanks

Dan

[Leith Ross]

Hello Dan,

Thanks for posting back with th workbook. This will make clear what you want to do.

[Leith Ross]

Hello Dan,

The error occurs because you are trying to select some cells that are "locked" on a protected worksheet.

[Dan Vollmer]

Hi Leith,

I looked at the worksheet and I can not find any locked cells where I am targeting. Can you tell me what worksheet and any of the cells that are locked?

Thanks

Dan

[Leith Ross]

Hello Dan,

Sheet 2, row 12.

[Dan Vollmer]

Hi Leith,

I don't think my code is going after row 12... isn't it row 13 through row 50?

[Dan Vollmer]

Hi,

Anyone have any ideas how I can solve my problem?

Thanks

Dan