Pasting Data Into A New Sheet without Overlapping | Excel forum

Resolved · High Priority · Version 2010

Mon 24 Nov 2014, 11:28 Delegate Mark said...

Mark has attended:
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel PowerPivot course
Excel Dashboards for Business Intelligence course

Pasting Data Into A New Sheet without Overlapping

Hi,

I'm trying to write a sub routine that will copy a column of data and paste it into a separate worksheet, however, I want it to count the number of columns in the new sheet and then paste it into the next empty column. So far my code is very simple (below);

Windows("MB of Anywhere").Activate
Columns("D:D").Select
Selection.Copy
Windows("MA Summary Totals v2").Activate
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

What I think I need to do is where the line of code is Columns("B:B").Select I need to have something that counts the columns and then does a +1 to then put it into an empty column.

Thanks
Mark

Tue 25 Nov 2014, 13:05 Trainer Jens said...

RE: Pasting Data Into A New Sheet without Overlapping

Hi Mark,

Thank you for the forum question.

You will need to count the number of columns you have in the currentregion in the destination sheet and add one. Store this information in a variable (iCol in my example).

Please have a look at the code below:

Dim iCol As Integer '(variable to store number of column +1)

Sheets(1).Select '(source sheet)
Columns("b").Copy '(column to copy)
Sheets(2).Select '(Destination sheet)

iCol = Range("a5").CurrentRegion.Columns.Count + 1
ActiveSheet.Columns(iCol).Select

ActiveSheet.Paste

I hope the code make sense but please let me know if you need more explanation.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector