hello i search macro copy columns non adjacent in sheet from workbook to another i want copy column a, c,e to another workbook
copy columns non adjacent in sheet from workbook to another
-
alhagag -
November 13, 2020 at 4:07 PM -
Thread is marked as Resolved.
-
-
-
Do you want to copy columns A, C and E to columns A, B and C of the other workbook or to columns A, C and E of the other workbook?
-
i would copy columns A, C and E to columns A, B and C
-
Make sure that both workbooks are open. Copy/paste this macro into the source workbook.
CodeSub CopyColumns() Application.ScreenUpdating = False Dim srcWS As Worksheet, desWS As Worksheet Set srcWS = ThisWorkbook.Sheets("Sheet1") 'change the source sheet name to suit your needs Set desWS = Workbooks("OtherWorkbookName.xlsx").Sheets("Sheet1") 'change the destination workbook name and sheet name to suit your needs With srcWS Union(.Columns("A"), .Columns("C"), .Columns("E")).Copy desWS.Range("A1") End With Application.ScreenUpdating = True End Sub
-
it gives me error"
This selection isn't valid. Make sure the copy and paste areas don't overlap
unless it is the same size and shape
in this line
-
-
Merged cells would cause the error.
-
hi, logit this no merged cells both two workbooks
-
Are both workbooks open when attempting to copy / paste ?
-
yes as Mumps said
-
Hmmm ...
I just ran the macro on your workbook attached in your first post.
No error here.
Have you changed anything in either workbook ?
-
-
what i attached i applied it's strange
-
.
I agree.
Begin with a brand new workbook and see if the code works there.
Attached are the two workbooks I used here. See if they run there.
-
i think founding my mistake i was running the code from the destination file not source file as your two files now it works thank you and mr Mumps for the code but i have a question is there way to run the code from destination file without open source file
-
-
Place this macro in the destination file. Review the comments I have added to make any necessary changes in the code. This macro will actually open the source file, copy the columns and then close the source file. However, the process will not be visible to the user. You will only see the final result.
Code
Display MoreSub CopyColumns() Application.ScreenUpdating = False Dim desWS As Worksheet, srcWB As Workbook Set desWS = ThisWorkbook.Sheets("Sheet1") 'change the destination sheet name to suit your needs Set srcWB = Workbooks.Open("C:\Test\zz.xlsx") 'change the source workbook path and name to suit your needs With Sheets("Sheet1") 'change the source sheet name to suit your needs Union(.Columns("A"), .Columns("C"), .Columns("E")).Copy desWS.Range("A1") End With srcWB.Close False Application.ScreenUpdating = True End Sub
-
-
great code just curiosity if i would copy to non adjacent columns to destination file like copy to d,f,h how is it ?
-
Try:
Code
Display MoreSub CopyColumns() Application.ScreenUpdating = False Dim desWS As Worksheet, srcWB As Workbook Set desWS = ThisWorkbook.Sheets("Sheet1") 'change the destination sheet name to suit your needs Set srcWB = Workbooks.Open("C:\Test\zz.xlsx") 'change the source workbook path and name to suit your needs With Sheets("Sheet1") 'change the source sheet name to suit your needs .Columns("A").Copy desWS.Range("D1") .Columns("C").Copy desWS.Range("F1") .Columns("E").Copy desWS.Range("H1") End With srcWB.Close False Application.ScreenUpdating = True End Sub
-
awesome code thanks for your assistance and your time
-
You are very welcome.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!