vba copy data from a range and paste to empty column Closed

Question

Hello,

I have a set of values in range P6:P15.  At the end of each month, I would like to copy and paste the data from the current month column to the next empty column on the right until all 12 months are filled up.  For example, at the end of Jan when I run the macro it will copy the data from cells P6:P15 to the Jan column, and at the end of Feb the data will be copied again from cell P6:P15 to the Feb column and so forth.

Is it possible to provide a macro that would do just that?

Thanks!!!

Current month		YTD	Jan	Feb 	Mar	Apr
100			100			
300			100			
400			100			
500			100

vcoolio · Accepted Answer

Hello VonnyS,

Sorry for the late reply. I've had some problems logging in over the last couple of days.

Anyway, what RayH has said in post #6 is correct.

It may be worthwhile uploading a sample of your actual work book so that we can try and determine what is going on. You can upload a sample by using a free file sharing site such as DropBox, ge.tt or SpeedyShare. Upload to your selected site then post back here with the link to your file. Be careful with any sensitive data.

@RayH:

Thanks for looking in!

Cheerio,
vcoolio.

vcoolio · Answer

Hello VonnyS,

Try the following code in a standard module:-


Sub GoToNextCol()

Application.ScreenUpdating = False

Dim NextCol As Long
      NextCol = Sheet1.Cells(6, Columns.Count).End(xlToLeft).Column + 1

Sheet1.Range("P6:P15").Copy Sheet1.Cells(6, NextCol)

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code assumes that the range is fixed at "P6:P15" and is hence not dynamic.

Following is a link to my test work book for you to peruse. Click on the button to see it work:-

https://www.dropbox.com/s/16ejseuwpw10ps9/VonnyS.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.

vonnyS · Answer

Hello,

I tried that and it worked to some extent.  However it's now pasting the data from P6:P15 to AY6, AX etc., every time I run it.  I would like the macro to copy and paste the values from P6:P15 to column V, then W, then X etc. every time I run it.  Not sure if it's because there are headings (although the sample in the first macro provided did have headings).  I've attached a sample, not sure if you can see it.

Vba copy data from a range and paste to empty column

3 responses

Similar discussions

Subscribe To Our Newsletter!