Code shortening

[drgkt]

Can the code below be made shorter?
I mean, instead of the repeating "copy/paste", is there another shorter way?

Please Login or Register  to view this content.

[xlnitwit]

Hi,

You could use a separate routine for the copy and paste

Please Login or Register  to view this content.

and then the code you posted would become

Please Login or Register  to view this content.

[drgkt]

Please Login or Register  to view this content.

So in essence, when I execute macro "test", it would call the subroutine "CopyPasteValueAndFormat"?
And both can be in the same module?

[xlnitwit]

Yes, that's correct.

[drgkt]

Ok. Got it. Thanks!

And, I suppose, if I do this many times, I can set up several subroutines (CopyPasteValueAndFormat1, CopyPasteValueAndFormat2, etc) and specify in my macro which one to use.

[xlnitwit]

Yes you can. You may even be able to have one routine but use a few more parameters (e.g. the sheet to copy to) to make it more generic.

[TMS]

Do the columns all have the same number of rows of data? If so, you could just determine the last row once and use that.

[drgkt]

Not sure what you are asking but we are taking a total of 7 cells here and there and paste them in last row of columns 28 through 34.
And yes, the last empty row for any column in 28-34 is the last row for all of them. In other words, the pasted data will be in one row.

[TMS]

Just for fun:

Please Login or Register  to view this content.

[TMS]

Less distracting:

Please Login or Register  to view this content.