Vba copy data from a range and paste to empty column [Closed]

Report
Posts
4
Registration date
Friday February 5, 2016
Status
Member
Last seen
February 8, 2016
-
vcoolio
Posts
1256
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 24, 2020
-
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

3 replies

Posts
1256
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 24, 2020
208
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.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5347 users have said thank you to us this month

vonnyS
Posts
4
Registration date
Friday February 5, 2016
Status
Member
Last seen
February 8, 2016
1
Hello vcoolio,

Thanks!! the code seems to work in the file provided. However, it doesn't seem to work when I apply it to a different workbook. I changed "Sheet1" to the name of the worksheet and that seems to create an issue for some reason. any reason that would be the case?

Thanks
Von
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
"Sheet1" is the name Excel uses internally. It is not the name of the tab that you are looking at if you have renamed it.
To use the given name of the tab replace "Sheet1" with "Worksheets("yourtabname")".
Posts
4
Registration date
Friday February 5, 2016
Status
Member
Last seen
February 8, 2016
1
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.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5347 users have said thank you to us this month

vonnyS
Posts
4
Registration date
Friday February 5, 2016
Status
Member
Last seen
February 8, 2016
1
Here is the current code

Application.ScreenUpdating = False
Dim NextCol As Long
NextCol = Worksheets("PNL").Cells(6, Columns.Count).End(xlToLeft).Column + 1

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

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
The macro works just fine as it stands and is doing it correctly.

This line:
NextCol = Worksheets("PNL").Cells(6, Columns.Count).End(xlToLeft).Column + 1

is finding the next blank column on row 6.
So there must be values in that row that the command is detecting.

You can step through the macro using the F8 key to see what values are being assigned to the NextCol variable.
Posts
1256
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 24, 2020
208
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.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5347 users have said thank you to us this month