Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to copy 3 columns from one excel document to another excel document already created

    Hello,

    This is the help required:

    Ex:

    In the folder (work) located at D:\\work , I have 2 excel documents (doc1.xlsm and doc2.xlsx)

    1 - The doc1.xlsm has several columns and I need to copy 3 of these columns (D,H,L) to doc2.xlsx in a blank sheet

    2 - Please note that doc2.xlsx also has several columns and the columns to be transferred needs to go in a blank sheet.

    can I please get help.

    thanks in advance

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    You could use code like:
    Code:
    Sub Demo()
    Const StrNm1 As String = "D:\\work\doc1.xlsm", StrNm2 As String = "D:\\work\doc2.xlsm"
    Dim WkBk1 As Workbook, WkBk2 As Workbook, WkSht1 As Worksheet, WkSht2 As Worksheet
    Set WkBk1 = Workbooks.Open(StrNm1): Set WkBk2 = Workbooks.Open(StrNm2)
    Set WkSht1 = WkBk1.Sheets(1): Set WkSht2 = WkBk2.Sheets.Add
    WkSht1.Range("D:D").Copy: WkSht2.Paste Destination:=WkSht2.Range("A1")
    WkSht1.Range("H:H").Copy: WkSht2.Paste Destination:=WkSht2.Range("B1")
    WkSht1.Range("L:L").Copy: WkSht2.Paste Destination:=WkSht2.Range("C1")
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thx macropod but the response but there is something that is not working properly beside me, lol

    I pasted the code in to the doc (doc1.xlsm) and I changed the path to where the documents are located D:\\Test TSM\....

    the Doc1.xlsm it has several sheets, I want to copy the 3 columns from the sheet called Basic (I tried to rename it to (Sheet 1) to try this code and it didnt work. When I run it seems to be that the code runs fine but Im not able to see any information in the Doc2.xlsx in a new sheets

    Am I doing something wrong? Please see the code I updated

    Regards,

    Sub Demo()
    Const StrNm1 As String = "D:\\Test TSM\Doc1.xlsm", StrNm2 As String = "D:\\Test TSM\Doc2.xlsx"
    Dim WkBk1 As Workbook, WkBk2 As Workbook, WkSht1 As Worksheet, WkSht2 As Worksheet
    Set WkBk1 = Workbooks.Open(StrNm1): Set WkBk2 = Workbooks.Open(StrNm2)
    Set WkSht1 = WkBk1.Sheets(1): Set WkSht2 = WkBk2.Sheets.Add
    WkSht1.Range("D").Copy: WkSht2.Paste Destination:=WkSht2.Range("A1")
    WkSht1.Range("H:H").Copy: WkSht2.Paste Destination:=WkSht2.Range("B1")
    WkSht1.Range("L:L").Copy: WkSht2.Paste Destination:=WkSht2.Range("C1")
    End Sub

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Eddy,

    The path to your files should NOT have a D:\\ just D:\. The \\ backslash is used to denote network drives. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thx you RetiredGeek

    I fixed that but still nothing happen. It seems to run fine and it doesn't give me any error. when I check Doc2 there is nothing there

    let me explain just in case I'm not being clear as I'm not a VBA savvy

    in Doc1.xlsm in the sheet named BASIC in need to copy columns D,H,L and then paste them in Doc2.xlsx in a blank sheet

    Sub Demo()
    Const StrNm1 As String = "D:\Test TSM\Doc1.xlsm", StrNm2 As String = "D:\Test TSM\Doc2.xlsx"
    Dim WkBk1 As Workbook, WkBk2 As Workbook, WkSht1 As Worksheet, WkSht2 As Worksheet
    Set WkBk1 = Workbooks.Open(StrNm1): Set WkBk2 = Workbooks.Open(StrNm2)
    Set WkSht1 = WkBk1.Sheets(1): Set WkSht2 = WkBk2.Sheets.Add
    WkSht1.Range("D").Copy: WkSht2.Paste Destination:=WkSht2.Range("A1")
    WkSht1.Range("H:H").Copy: WkSht2.Paste Destination:=WkSht2.Range("B1")
    WkSht1.Range("L:L").Copy: WkSht2.Paste Destination:=WkSht2.Range("C1")
    End Sub

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Eddy,

    Give this a try:
    Code:
    Option Explicit
    
    Sub Demo()
    
       Const StrNm1 As String = "D:\Test TSM\Doc1.xlsm"
       Const StrNm2 As String = "D:\Test TSM\Doc2.xlsx"
    
       Dim WkBk1  As Workbook
       Dim WkBk2  As Workbook
       Dim WkSht1 As Worksheet
       Dim WkSht2 As Worksheet
       
       Set WkBk1 = Workbooks.Open(StrNm1): Set WkBk2 = Workbooks.Open(StrNm2)
       Set WkSht1 = WkBk1.Sheets(1): Set WkSht2 = WkBk2.Sheets.Add
       
       WkSht1.Range("D:D").Copy Destination:=WkSht2.Range("A1")
       WkSht1.Range("H:H").Copy Destination:=WkSht2.Range("B1")
       WkSht1.Range("L:L").Copy Destination:=WkSht2.Range("C1")
       
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi RetiredGeek,

    I tried that and its not working, I didn't change the code at all. It seems to run but nothing happen, it doesn't give me any error message. the Doc2 it doesn't open and even when I open it manually the information is not there in a blank sheet.

    the Doc1.xlsm it has several sheets. From the sheet called BASIC I need to copy columns D,H,L and then paste them in Doc2.xlsx in a blank sheet

    sorry about the time. I really appreciate the help

    Sub Demo()

    Const StrNm1 As String = "D:\Test TSM\Doc1.xlsm"
    Const StrNm2 As String = "D:\Test TSM\Doc2.xlsx"

    Dim WkBk1 As Workbook
    Dim WkBk2 As Workbook
    Dim WkSht1 As Worksheet
    Dim WkSht2 As Worksheet

    Set WkBk1 = Workbooks.Open(StrNm1): Set WkBk2 = Workbooks.Open(StrNm2)
    Set WkSht1 = WkBk1.Sheets(1): Set WkSht2 = WkBk2.Sheets.Add

    WkSht1.Range("D").Copy Destination:=WkSht2.Range("A1")
    WkSht1.Range("H:H").Copy Destination:=WkSht2.Range("B1")
    WkSht1.Range("L:L").Copy Destination:=WkSht2.Range("C1")

    End Sub

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Eddy,

    Ok, I took out the last multi statement line (I don't like these as it makes code harder to read).
    Code:
    Option Explicit
    
    Sub Demo()
    
    '   Const StrNm1 As String = "D:\Test TSM\Doc1.xlsm"
    '   Const StrNm2 As String = "D:\Test TSM\Doc2.xlsx"
       Const StrNm1 As String = "G:\Test\Test TSM\Doc1.xlsm"
       Const StrNm2 As String = "G:\Test\Test TSM\Doc2.xlsx"
    
       Dim WkBk1  As Workbook
       Dim WkBk2  As Workbook
       Dim WkSht1 As Worksheet
       Dim WkSht2 As Worksheet
       
       Set WkBk1 = Workbooks.Open(StrNm1)
       Set WkBk2 = Workbooks.Open(StrNm2)
       Set WkSht1 = WkBk1.Sheets(1)
       Set WkSht2 = WkBk2.Sheets.Add
       
       WkSht1.Range("D:D").Copy Destination:=WkSht2.Range("A1")
       WkSht1.Range("H:H").Copy Destination:=WkSht2.Range("B1")
       WkSht1.Range("L:L").Copy Destination:=WkSht2.Range("C1")
       
    End Sub
    I then created two files per your specs in my Test Directory and ran the code. It works just as it should.
    Eddy1.JPG

    Are sure your file names are correct?
    Are either of the sheets protected?

    I don't know what I can do other than this unless you post your two files for testing.

    Note: In the code above I left my statements with my drive path info. You can just uncomment yours and commend or delete mine.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi RetiredGeek,

    I noticed something. I ran the code from the file Doc1.xlsx and it worked, the Doc2.xlsx opens up with the 3 columns in a new sheets.

    I thought you were supposed to run this code from the file save with the extension xlsm (Doc1.xlsm)

    1 - Am I supposed to run this code from the Doc1.xlsx or .xlsm?
    2 - How can I make sure that when the 3 columns are being copied from Doc1 that they have to be from the sheet called BASIC, as Doc 1 it has several sheets with names

    Thank you a lot

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Eddy,

    You can run VBA code that has just been created from an .xlsx file but you can't save that file with the macros in tact unless you save it as a .xlsm or .xlsb or if it doesn't use any new features .xls.

    You'll notice in your code Doc1 is a .xlsm file!

    I created a new workbook and named it Eddy.xlsm this is where I put the code.
    I then created Doc1.xlsm (since that is what your code said) and put data in Cols D, H, & L saved & closed it.
    I then created Doc2.xlsx, Renamed Sheet1 to OrigSheet, just to verify that the data was added to a new sheet, saved & closed it.
    Then I ran the Macro from Eddy.xlsm.

    Of course you could run it from Doc1.xlsm if you choose but then you should change the code so it doesn't attempt to re-open that file which could cause problems.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi RetiredGeek,

    Ok, now I get it. One more thing

    How can the code be fixed in order for the 3 columns that are being copied from Doc1, that they have to be from the sheet called BASIC, as Doc 1 it has several sheets with names?

    Thx

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Eddy,

    Here's the change:
    Code:
    Option Explicit
    
    Sub Demo()
    
       Const StrNm1 As String = "D:\Test TSM\Doc1.xlsm"
       Const StrNm2 As String = "D:\Test TSM\Doc2.xlsx"
    
       Dim WkBk1  As Workbook
       Dim WkBk2  As Workbook
       Dim WkSht1 As Worksheet
       Dim WkSht2 As Worksheet
       
       Application.ScreenUpdating = False
       
       Set WkBk1 = Workbooks.Open(StrNm1)
       Set WkBk2 = Workbooks.Open(StrNm2)
       Set WkSht1 = WkBk1.Sheets("Basic")
       Set WkSht2 = WkBk2.Sheets.Add
       
       WkSht1.Range("D:D").Copy Destination:=WkSht2.Range("A1")
       WkSht1.Range("H:H").Copy Destination:=WkSht2.Range("B1")
       WkSht1.Range("L:L").Copy Destination:=WkSht2.Range("C1")
       
       Application.ScreenUpdating = True
       
    End Sub
    Note: I also turned off screen updating so the screen doesn't blink while the code is executing.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    New Lounger
    Join Date
    Jun 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Awesome RetiredGeek,

    Thank you so much, you are awesome. you ROCK. thank you for helping me

    Have a nice weekend

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •