Nick has attended:
No courses
Copy and Paste Column including formulas.
I am trying to copy the column with the "x" in row 9, then insert it to the right.
I have the below so far - which can copy the 1st column and past to to the next, just not sure how to get the reference in row 9
Sub LastColumn()
Dim LastCol As Long
With ThisWorkbook.Sheets("Capital Accounts")
LastCol = Cells(1, .Columns.Count).End(xlToLeft).Column
Columns(LastCol).Copy Destination:=Cells(1, LastCol + 1)
End With
End Sub
RE: Copy and Paste Column including formulas.
Hi Nick,
Thank you for the forum question.
I hope I understand you right. The code below will find x in row 9. Copy the whole column and paste the column to the right of the column with the x.
Sub LastColumn()
Dim ColCell As Range
Dim ColNumber As Integer
Set ColCell = Rows(9).Find("X")
ColNumber = ColCell.Column
Rows(9).Find("X").EntireColumn.Copy Destination:=Cells(1, ColNumber + 1)
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
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
RE: Copy and Paste Column including formulas.
Hi Jens,
This definitely looks like it would work, however, I am running into the below error on the "Rows(9).Find("X").EntireColumn.Copy Destination:=Cells(1, ColNumber + 1)"
Compile error: Expected: expression
and the := is highlighted.
Then I got the compile error to clear, but then receieved an error message on the "ColNumber = ColCell.Column" stating Run-time error '91': Object wariable or with block variable not set
I also believe I would need to change the "Destination:=Cells(1,ColNumber +1) so not have Cells(1.. as i need it to paste to the right of the copied column not the first column
I would also definitely be interested in your trainings!
RE: Copy and Paste Column including formulas.
Hi Nick
Thank you for using the forum to ask a question.
I can see you’ve received a helpful and valuable reply from our Training Team.
We can’t see a record of you attending a VBA course with us. We’d encourage you to continue your learning on VBA in our F2F or Virtual Classrooms which can be booked here
https://www.stl-training.co.uk/microsoft/vba-training-london.php
I’ll reach out to you directly to support you with your learning.
Kind regards
Richard Bailey
Head of Learning & Development
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
