+ Reply to Thread
Results 1 to 7 of 7

Copy columns from one workbook to another using VBA

  1. #1
    Registered User
    Join Date
    11-05-2019
    Location
    Derby, England
    MS-Off Ver
    Office 365
    Posts
    10

    Question Copy columns from one workbook to another using VBA

    Hi All, Newbie here I'm afraid....

    I have a daily task that I want to automate via VBA if possible. I get a daily update to my source file (call it 'Workflow' for now) and the number of records is different every day. I need to copy various columns from my source file to a target (destination) file (Call that 'Capital Review' for now). The source workbook has more columns than the target workbook. They are not in the same format so I have to do it column by column which is pretty time consuming, so was thinking a VBA scripy could possibly do this, but as I don't know how many records I'll get on any given day I don't know how to approach it. I am not an experienced VBA user. Basic issue is as follows:

    Source workbook column to Target Workflow column: ALL PASTE VALUES ARE PASTE SPECIAL = VALUES

    Col A to Col D (and reformat date to 'short date' format)
    Col B to Col E
    Col J to Col AA
    Col O to Col M
    Col P to Col Y
    Col R to Col P
    Col W to Col K
    Col AA to Col Z
    Col AB to Col C
    Col AC to Col B
    Col AP to Col J
    Col AT to Col O
    Col AU to Col I
    Col AV to Col S

    I am sure this is possible but don't know where to start. Any tips? Is this possible? Any help gratefully received.

    Both Workbooks are usually open, but don't have to be.

    Thanks,

    Andy

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Copy columns from one workbook to another using VBA

    Hi Andy,

    Welcome to the forum!!

    Let us know how this goes:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Registered User
    Join Date
    11-05-2019
    Location
    Derby, England
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Copy columns from one workbook to another using VBA

    Hi Robert, that does not seem to work as intended. I only need it to copy the columns from the source file that aren't in the destination file. What the code seems to have done is do the whole file (destination) and although I can see the actual data in the destination file it isn't where it should be. For example, the destination file should receive the new columns starting from row 17044 and end up at row 17128, but it does not do that. It also appears to have wiped out some of the data entirely. Not sure if you need visibility of a before and after? If so, I'll just need to quickly edit them as there are some GDPR issues about a couple of fields. I'm not sure how the macro knows where to start the copy from? Thanks for trying, it's kind of close, but the VBA code must need tweaking somewhere. Andy

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Copy columns from one workbook to another using VBA

    The following will append the data from each required column of the source workbook to the next available row of the required column (as values) of the destination workbook (I think this is what you're after):

    Please Login or Register  to view this content.
    Regards,

    Robert
    Last edited by Trebor76; 11-06-2019 at 06:34 PM.

  5. #5
    Registered User
    Join Date
    11-05-2019
    Location
    Derby, England
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Copy columns from one workbook to another using VBA

    Robert, That works fine thank you very much. WHat I did to make it easier was to add another sheet to the Source file. I copy to the main sheet first, then just copy that days data to the second sheet. I amended you code to look at the second sheet, so I don't need to pinpoint the start of relevant data in the main source sheet. This happily goes off and correctly updates the destination file. As a slightly supplementary question, once the data is in the destination workbook I need to reformat a few columns and copy some formulas down. I tried to record a macro to do this, but have a few issues:

    Sub Macro3()
    '
    ' Macro3 Macro
    ' V3
    '
    ' Keyboard Shortcut: Ctrl+Shift+R
    '
    Range("B17171:B17212").Select
    Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    ActiveWindow.SmallScroll Down:=-6
    Range("D17171:D17212").Select
    Selection.NumberFormat = "m/d/yyyy"
    ActiveWindow.SmallScroll Down:=-6
    Range("E17170").Select
    Selection.AutoFill Destination:=Range("E17170:E17212"), Type:=xlFillDefault
    Range("E17170:E17212").Select
    ActiveWindow.SmallScroll ToRight:=3
    ActiveWindow.SmallScroll Down:=-9
    Range("N17170").Select
    Selection.AutoFill Destination:=Range("N17170:N17212"), Type:=xlFillDefault
    Range("N17170:N17212").Select
    End Sub

    The fact that the ranges are fixed by me recording it means it will perform the actions on the specific rows - obviously these will change every day - can I have different code or a prompt box to specify to range of rows? Also the first date conversion (from, say 06.11.19 to 06/11/19) using Find/Replace results in me getting 11/06/19 returned by the Macro. It doesn't do that when I do it manually.

    Anyway your thoughts appreciated and thank you again for the main data Import code, that has broken the back of it, this is just follow-up tidying work.

    Cheers,

    Andy

  6. #6
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Copy columns from one workbook to another using VBA

    The fact that the ranges are fixed by me recording it means it will perform the actions on the specific rows - obviously these will change every day - can I have different code or a prompt box to specify to range of rows?
    Have a look at the "Select Case" section in the following code where I've formatted the new data in Col. D of the destination workbook (just add the other columns and formatting as desired in the same way):

    Please Login or Register  to view this content.
    Regards,

    Robert

  7. #7
    Registered User
    Join Date
    11-05-2019
    Location
    Derby, England
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Copy columns from one workbook to another using VBA

    Robert, thanks. My ImportData VBA works just fine now. I have one issue outstanding (and I think looking at Google a number of people hit this problem) in Col AC of my Source file I have a date which is in dd.mm.yyyy format. It needs to be in dd/mm/yyyy format. Using CTRL+F, replacing '.' with '/' works manually, but when I record it as a macro it returns mm/dd/yyyy (US format I guess). What is the best code (either within ImportData code or as a separate pice of code run on the source file before running ImportData macro)? I have tried a few variants but can't seem to get it to work properly. An example of the source file file is attached just as a way of demonstrating how the file looks.
    Attached Files Attached Files

+ 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. loop through files find columns and copy/paste them as columns in master workbook
    By Michal1111 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2018, 10:40 PM
  2. [SOLVED] Copy columns to new workbook with matched headers, then save new workbook as a text file.
    By haitran in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-17-2017, 05:53 PM
  3. Replies: 2
    Last Post: 03-18-2017, 02:05 AM
  4. [SOLVED] Copy Select Columns from closed workbook into active workbook
    By 3345james in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-09-2015, 02:04 PM
  5. Need to Copy Columns from Previous Workbook to Newly Opened Workbook
    By BYizz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-06-2012, 06:33 AM
  6. [SOLVED] COPY from an open workbook with particular sheet and columns to active workbook
    By purin0c0 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-12-2012, 03:57 PM
  7. Copy columns from closed workbook to different columns in open workbook
    By jGLZa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2012, 11:03 PM

Tags for this Thread

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