VBA Copy & Paste Function

**sal07** · 07-10-2007, 08:12 AM

Hi all,

Basically I am recording a macro to cut & paste several columns of text into
the same sheet at the next available FREE column.

However whilst this works fine, every time I click the macro from the button I
have assigned, it overwrites the previous data i.e. it doesnt assign the
columns to the next blank available column.

I am presuming I would have to set up some form of array & maybe use offset
function?

This is an example piece of code that I have just typed (but the principle is the same for my code):

Please Login or Register  to view this content.

So I want to copy all formulas & cell formating but no actual cell values.

I am new to VBA so dont really know how to achieve anything beyond basic code.

Can someone perhaps point me in the right direction?

Thanks in advance.

Rob

**jasoncw** · 07-10-2007, 08:40 AM

Hi, Rob.

First, please wrap your code in the future using the CODE tags. It helps to read the code. As for the next available column, you can find the next available column by using this code:

Please Login or Register  to view this content.

To use this in your macro, you can use the following:

Please Login or Register  to view this content.

HTH

Jason

**sal07** · 07-11-2007, 02:36 AM

Hi Jason,

Firstly, I will wrap the code in using the code tags (see what you mean about easier reading).

Secondly, thanks for the code this works perfect, however their is a slight problem in that the code only works if I take the title of the excel spreadsheet & some of the headings out.

I have made sure that this is a generic problem & not an issue in my sheet & have found that it is. For example if you set up a simple spread sheet with the columns a, b, c & setup a macro to use the code you have provided, it works if a, b, c are in the very top rows of the excel sheet. However if you put a title in & then & have a blank line in & then the code does not work as intended. Does this make sense?

Also the second issue is that I only want to copy the column formatting & formulas across from the columns, not the actual data itself (any ideas how I can incorporate this within your code example)?

Any help is appreciated.

Thanks

Rob

**jasoncw** · 07-11-2007, 08:02 AM

Hi, Rob. So you want to look for the last column, but ignore row 1, correct? The code I used above looked for the last column in row 1, so we can just change it to look for the last column in row 2 by changing 1 number:

Please Login or Register  to view this content.

Now, if you ony want the formulas, there is a quick way to delete any cells that do not have formulas by using:

Please Login or Register  to view this content.

So these can be incorporated into the procedure as follows:

Please Login or Register  to view this content.

Let me know whether that works for you.

Jason

**sal07** · 07-11-2007, 10:15 AM

Hi Jason,

Thanks for helping me with this - it works perfect.

I wasnt sure what all of the code was doing until you explained.

Couple of questions though:

Please Login or Register  to view this content.

- For my own development, what is the significance of lastcol + 3 in the above code (I understand the rest, just that the code seems to work fine if I put 2 or 3 in)?

- I am trying to learn to use VBA & excel advanced queries more & more (I normally just experiment by recording macros & ammending them) same with formulas, but are there any books or sites you can recommend to improve my knowledge in such areas?

Again thanks for all your help.

Rob

**jasoncw** · 07-11-2007, 10:22 AM

That line of code simply determines the range of cells where the constants will be deleted. The basic format of the range is:

Please Login or Register  to view this content.

The above refers to range A1:A10. Another piece of code I used was:

Please Login or Register  to view this content.

This determines the last used cell on the worksheet (similar to typing CTRL + END on a worksheet). I used this to determine the last row to select.

The lastCol + 3 was used because the range to delete the constants is 4 columns (since we copied columns A:D, which is 4 columns). So the range is from row 2, last Col, to the last row, last Col + 3.

For VBA, I highly recommend John Walkenbach's Power Programming with VBA, in combination with reading the various threads on different Excel VBA forums.

Glad I could help you out.

Jason