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
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
