copy pastespecial loop

[copleyr]

Hello,

The following macros will:

I'm desperate for a copy paste macros that will:

1) Copy H3:H143 (141 rows) from "Sheet 1" and place this on "Sheet 2" on D4:D144 (141 rows)

2) Copy I:3:I143 (141 rows) from "Sheet 1" and place this on "Sheet 2" on I4:I144 (141 rows)

So, in essence, it will copy every single column across on "Sheet 1", but will paste every 5 columns on "Sheet 2" .


It will then loop 33 times to continually copy and paste across columns.

Please Login or Register  to view this content.

My only problem is that it is not finished:

I need the same "Sheet 1" information pasted to "Sheet 3", "Sheet 4", "Sheet 5", and "Sheet 6". In the same format (every single column across on "Sheet 1", but will paste every 5 columns on "Sheet 3", "Sheet 4", "Sheet 5" and "Sheet 6"


With that in mind, as the financial data is "Sheet 1" is copied, I need it to be pasted 3.5% higher than it was copied from.........So that Sheet 2 will be 3.5% higher than sheet 1, Sheet 3 will be 3.5% higher in sheet 2, and so forth.

I also need it to paste special values because it is currently pasting formulas instead of values.


Any suggestions?

Thank you

[StephenR]

I suggest you attach a sample workbook to save anyone here having to spend time mocking something up. Not all of us are clever enough to write ad hoc code without testing.

[copleyr]

Here is a sample

[StephenR]

Give this a whirl. I put the factor in A1 of each sheet assuming you don't use that cell, but modify if you do.

Please Login or Register  to view this content.

[copleyr]

Hey thanks for the quick reply!

I think my problem is that Sheets 2 through Sheets 6 will not be those actual sheet names. They will actually be called:

"Year 1 Ceiling Rates", "Year 2 Ceiling Rates", "Year 3 Ceiling Rates", ....., "Year 5 Ceiling Rates".

The "Sheet 1" information that is being copied is actually called "Sheet 1" but will not be at the beginning of the tabs. It will be located at the end of the tabs.


Any suggestions?

[copleyr]

There will also be other random tabs in the mix so I'm not sure if

Please Login or Register  to view this content.

\

will work?

[StephenR]

Which is why it helps if you attach a workbook which represents your actual file. Do that, and I will amend the code.

[copleyr]

you mind if i send to your email?

[copleyr]

It wont upload the sample file is too large....

And I cannot delete the pertinent data because it is password protected...

[JBeaucaire]

A continuation of our original thread...

Just edit the "array" you see listed, put the sheets you want copied TO in there in the order you want.

Please Login or Register  to view this content.

NOTE: No more crossposting without including links.

[JBeaucaire]

Code above updated and second loop removed. Use updated code. (9:37am)

[copleyr]

Thanks JB. I put in the necessary values in the array and it worked out.. Except,

The sheets in the array are password protected. The only place I can paste values are in the 141 Rows (after every fifth column) I had defined.

Your very first macro from last night allowed me to copy and paste into the correct columns....it did it perfectly...just needed to add paste values.


Now it is not working and saying that the worksheet is protected.



Would you have any suggestions?

Thank you in advance.....i hate password protection

[StephenR]

So I wasted precious minutes of my life helping you with a problem you'd posted elsewhere, which is incidentally against the rules.

[copleyr]

No, it was a continuation of the original post I had posted in this thread.

The last thread is closed.

I appreciate your help.

[copleyr]

I guess I'm SOL