I want to insert the copied column into the column next to the destination column instead of pasting it over the destination column. Or it could be inserted anywhere as long as it is not pasting over the columns in the destination worksheet. I tried to insert and entirecolumn.insert after the range, but the error says it does not work with copy method. See code below please.
Sub Simple_Match()
Dim Answer As VbMsgBoxResult
Answer = MsgBox("Are you sure you want to run the Create Reconciliation Sheet Macro ?", vbYesNo, "Run Create Reconciliation Sheet")
If Answer = vbYes Then
Sheets("District").Select
desc = WorksheetFunction.Match("Description", Rows("1:1"), 0)
Sheets("District").Columns(desc).Copy Destination:=Sheets("Members").Range("A1")
End If
End Sub
Copying column from different worksheet
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copying column from different worksheet
Welcome to Eileen's Lounge!
Try this:
If it doesn't work: do the source or target columns contain merged cells?
Try this:
Code: Select all
Sub Simple_Match()
Dim Answer As VbMsgBoxResult
Dim desc As Long
Answer = MsgBox("Are you sure you want to run the Create Reconciliation Sheet Macro ?", vbYesNo, "Run Create Reconciliation Sheet")
If Answer = vbYes Then
desc = WorksheetFunction.Match("Description", Sheets("District").Rows("1:1"), 0)
Sheets("District").Columns(desc).Copy
Sheets("Members").Columns(1).Insert
Application.CutCopyMode = False
End If
End Sub
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 15 Sep 2022, 20:09
Re: Copying column from different worksheet
Hello, it doesn't work. They do not contain merged cells. They are tables though. I attached a picture of what it looks like after the code ran. It added a blank column. The error is "Copy method of Range class failed.
You do not have the required permissions to view the files attached to this post.
-
- NewLounger
- Posts: 3
- Joined: 15 Sep 2022, 20:09
Re: Copying column from different worksheet
Sorry it was an error on my end. It is working. Thank you so much.