+ Reply to Thread
Results 1 to 6 of 6

Import specific columns from workbook A sheet1 to workbook B Sheet 2 to specific columns

  1. #1
    Registered User
    Join Date
    11-10-2014
    Location
    Mauritius
    MS-Off Ver
    2013
    Posts
    50

    Import specific columns from workbook A sheet1 to workbook B Sheet 2 to specific columns

    Hi,
    I was using below codes to import data from same workbook from ws1 to ws2, can someone help to update the code for me to do same but each sheet is in a different workbook
    Thanks for your help
    Code:

    Sub ImportBEMicro()
    Dim ws1 As Worksheet, ws2 As Worksheet, lr1 As Long, nr2 As Long, r As Range
    Set ws1 = Worksheets("NLM_CDVente"): Set ws2 = Worksheets("NLM_BE_MICRO")
    lr1 = ws1.Cells(Rows.Count, "AN").End(xlUp).Row
    nr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    For Each r In ws1.Range("AN3:AN" & lr1).Cells
    If r.Interior.ColorIndex <> 3 And r.Value <> "" Then
    ws2.Range("A" & nr2) = r.Offset(, -39): ws2.Range("B" & nr2) = r.Offset(, -38): ws2.Range("C" & nr2) = r.Offset(, -32): ws2.Range("D" & nr2) = r.Offset(, -24): ws2.Range("E" & nr2) = r.Offset(, -23): ws2.Range("F" & nr2) = r.Offset(, -22): ws2.Range("G" & nr2) = r.Offset(, -21): ws2.Range("H" & nr2) = r.Offset(, -20): ws2.Range("I" & nr2) = r.Offset(, -19): ws2.Range("J" & nr2) = r.Offset(, 17): ws2.Range("K" & nr2) = r.Offset(, 19): ws2.Range("L" & nr2) = r.Offset(, 38): ws2.Range("M" & nr2) = r.Offset(, 39): ws2.Range("N" & nr2) = r.Offset(, -31): ws2.Range("O" & nr2) = r.Offset(, -16): ws2.Range("P" & nr2) = r.Offset(, -15): ws2.Range("Q" & nr2) = r.Offset(, -14): ws2.Range("R" & nr2) = r.Offset(, -12): ws2.Range("S" & nr2) = r.Offset(, -10) 'the letter defines column on the target sheet and the figures defines the position of the columns to be copied compared to column where the x is found'
    nr2 = nr2 + 1: r.Value = "": r.Interior.ColorIndex = 3
    End If
    Next r
    End Sub

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Import specific columns from workbook A sheet1 to workbook B Sheet 2 to specific colum

    Edit: Code tags used in next post...
    Last edited by Olly; 07-14-2016 at 06:16 AM.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    11-10-2014
    Location
    Mauritius
    MS-Off Ver
    2013
    Posts
    50

    Re: Import specific columns from workbook A sheet1 to workbook B Sheet 2 to specific colum

    Dear Olly,
    Sorry as I did not know the real format to sent the code.Here it is(Hope it is ok now.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Import specific columns from workbook A sheet1 to workbook B Sheet 2 to specific colum

    Try this:
    Please Login or Register  to view this content.
    (why is your code on so few lines, with statements colon separated? It's much easier to read and edit, if statements are on separate lines...)

  5. #5
    Registered User
    Join Date
    11-10-2014
    Location
    Mauritius
    MS-Off Ver
    2013
    Posts
    50

    Re: Import specific columns from workbook A sheet1 to workbook B Sheet 2 to specific colum

    Hi Olly,
    Many thanks, the way you presented the codes really professional, orderly and easy to understand. Again thanks

  6. #6
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Import specific columns from workbook A sheet1 to workbook B Sheet 2 to specific colum

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You may also add reputation to users who helped you. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Import graph from a specific workbook if a cell matches the workbook name
    By hassan88 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2015, 05:11 AM
  2. [SOLVED] Import Specific Sheet into Workbook with VBA
    By chicagoland8 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-18-2014, 04:10 PM
  3. how to copy specific columns form one workbook to another
    By amethystfeb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2014, 11:53 PM
  4. how to copy specific columns form one workbook to another
    By amethystfeb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2014, 10:49 PM
  5. Import specific sheet with name and Date mm/dd/yyyy from closed workbook
    By frank35 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-22-2013, 11:21 AM
  6. Copy specific columns from one workbook to another workbook
    By Tegpreet in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-09-2013, 07:55 AM
  7. Replies: 8
    Last Post: 04-04-2013, 08:02 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1