+ Reply to Thread
Results 1 to 3 of 3

Want to automate excel content copy with vba code.

  1. 07-30-2016, 02:10 PM #1
    mohit999
    mohit999 is offline
    Registered User
    Join Date
    04-27-2015
    Location
    Perth
    MS-Off Ver
    office 2010
    Posts
    76

    Want to automate excel content copy with vba code.

    Hi,

    I copy some fixed data daily from around 41 excels daily to a single excel. Have a look at VBA code below. It simply copy a fixed range of cells (from workbooks named 1,2,3 etc.) to a single worksheet. The problem is, I have to open all source workbooks before running this VBA code.It really fills my taskbar. Can it be done without opening up source workbooks first. All source workbooks are in the same folder.




    Sub Masterdata()
    Workbooks("1").Sheets("Sheet1").Range("A1:AH1000").Copy Range("A1:AH1000")
    Workbooks("2").Sheets("Sheet1").Range("E1:AH1000").Copy Range("AI1:BL1000")
    Workbooks("3").Sheets("Sheet1").Range("E1:AH1000").Copy Range("BM1:CP1000")
    Workbooks("4").Sheets("Sheet1").Range("E1:AH1000").Copy Range("CQ1:DT1000")
    Workbooks("5").Sheets("Sheet1").Range("E1:S1000").Copy Range("DU1:EI1000")
    Workbooks("6").Sheets("Sheet1").Range("E1:AH1000").Copy Range("EJ1:FM1000")
    Workbooks("7").Sheets("Sheet1").Range("E1:AH1000").Copy Range("FN1:GQ1000")
    Workbooks("8").Sheets("Sheet1").Range("E1:AD1000").Copy Range("GR1:HQ1000")
    Workbooks("9").Sheets("Sheet1").Range("E1:AH1000").Copy Range("HR1:IU1000")
    Workbooks("10").Sheets("Sheet1").Range("E1:AH1000").Copy Range("IV1:JY1000")
    Workbooks("11").Sheets("Sheet1").Range("E1:AH1000").Copy Range("JZ1:LC1000")
    Workbooks("12").Sheets("Sheet1").Range("E1:T1000").Copy Range("LD1:LS1000")
    Workbooks("111").Sheets("Sheet1").Range("E1:AH1000").Copy Range("YW1:ZZ1000")
    Workbooks("122").Sheets("Sheet1").Range("E1:T1000").Copy Range("AAA1:AAP1000")
    Workbooks("result").Sheets("Sheet1").Range("A1:O1000").Copy Range("LT1:MH1000")
    Workbooks("odd1").Sheets("Sheet1").Range("E1:CC1000").Copy Range("MI1:PG1000")
    Workbooks("odd2").Sheets("Sheet1").Range("E1:BW1000").Copy Range("PH1:RZ1000")
    Workbooks("odd3").Sheets("Sheet1").Range("E1:BI1000").Copy Range("SA1:UE1000")
    Workbooks("odd4").Sheets("Sheet1").Range("E1:Z1000").Copy Range("UF1:VA1000")
    Workbooks("odd5").Sheets("Sheet1").Range("E1:X1000").Copy Range("VB1:VU1000")
    Workbooks("odd6").Sheets("Sheet1").Range("E1:AC1000").Copy Range("XX1:YV1000")
    Workbooks("13").Sheets("Sheet1").Range("E1:AH1000").Copy Range("AAZ1:ACC1000")
    Workbooks("14").Sheets("Sheet1").Range("E1:AH1000").Copy Range("ACD1:ADG1000")
    Workbooks("15").Sheets("Sheet1").Range("E1:AH1000").Copy Range("ADH1:AEK1000")
    Workbooks("16").Sheets("Sheet1").Range("E1:AH1000").Copy Range("AEL1:AFO1000")
    Workbooks("17").Sheets("Sheet1").Range("E1:AH1000").Copy Range("AFP1:AGS1000")
    Workbooks("18").Sheets("Sheet1").Range("E1:AH1000").Copy Range("AGT1:AHW1000")
    Workbooks("19").Sheets("Sheet1").Range("E1:AH1000").Copy Range("AHX1:AJA1000")
    Workbooks("20").Sheets("Sheet1").Range("E1:AH1000").Copy Range("AJB1:AKE1000")
    Workbooks("21").Sheets("Sheet1").Range("E1:AH1000").Copy Range("AKF1:ALI1000")
    Workbooks("22").Sheets("Sheet1").Range("E1:AH1000").Copy Range("ALJ1:AMM1000")
    Workbooks("23").Sheets("Sheet1").Range("E1:AH1000").Copy Range("AMN1:ANQ1000")
    Workbooks("24").Sheets("Sheet1").Range("E1:AH1000").Copy Range("ANR1:AOU1000")
    Workbooks("25").Sheets("Sheet1").Range("E1:AH1000").Copy Range("AOV1:APY1000")
    Workbooks("26").Sheets("Sheet1").Range("E1:AH1000").Copy Range("APZ1:ARC1000")
    Workbooks("27").Sheets("Sheet1").Range("E1:AH1000").Copy Range("ARD1:ASG1000")
    Workbooks("28").Sheets("Sheet1").Range("E1:AH1000").Copy Range("ASH1:ASU1000")
    Workbooks("255").Sheets("Sheet1").Range("E1:AH1000").Copy Range("ASV1:ATY1000")
    Workbooks("266").Sheets("Sheet1").Range("E1:AH1000").Copy Range("ATZ1:AVC1000")
    Workbooks("277").Sheets("Sheet1").Range("E1:AH1000").Copy Range("AVD1:AWG1000")
    Workbooks("288").Sheets("Sheet1").Range("E1:AH1000").Copy Range("AWH1:AWU1000")

    End Sub
    Reply With QuoteRegister To Reply
  2. 07-30-2016, 02:32 PM #2
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Want to automate excel content copy with vba code.

    Hi mohit,

    In 2010 Excel came out with an Add-In called Power Query. You can download it and add it to your toolbar. It has the feature to pull in multiple files from the same folder. It is easier to learn and use than VBA.

    https://www.microsoft.com/en-us/down...c-3f1461c16d68
    http://datapigtechnologies.com/blog/...nto-one-table/

    I've been suggesting Power Query (now called Get & Transform in 2016) for a few months now. You have the perfect reason to learn it. Let me try to explain what it does.

    When you "Get" data you can get it from a lot of different places. Then you pull it into a "pre processing" place that looks a little like Excel but is really the "transform" screen. You can then filter out rows or columns or change data types to make the data look like stuff Excel and use. The last step is to take the "transformed" data and load it into excel. It is a Top Down process where the steps you do (like record) will do the same things the next time you open/refresh the Query.

    In your case if you have all these workbooks in the same folder, your problem was made for Power Query. Get and spend a few days playing with it and I'm sure it will save you LOTS of hours of work.
    Last edited by MarvinP; 07-31-2016 at 03:17 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  3. 07-31-2016, 02:46 PM #3
    mohit999
    mohit999 is offline
    Registered User
    Join Date
    04-27-2015
    Location
    Perth
    MS-Off Ver
    office 2010
    Posts
    76

    Re: Want to automate excel content copy with vba code.

    Thank you marwin for the tip. I am gonna try it.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VB Code to copy content of cell into Text file
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2016, 10:16 PM
  2. VBA code to copy user form textbox content to clipboard
    By Sendilo in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-17-2015, 10:19 PM
  3. code to copy content to adjacent cells based on a cell value
    By anbarasi_r in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-28-2015, 12:42 PM
  4. Macro code to copy excel sheet content to outlook mail
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-05-2013, 07:18 AM
  5. Code to Copy, Divide Cell Content and Paste
    By AntiPivotTable in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-30-2013, 07:59 PM
  6. What code for macro to copy selected cell content
    By BlastRanger in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-23-2010, 01:29 AM
  7. [SOLVED] Automate Cut/Copy/Paste from Excel Cells
    By Do it the Easy Way in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-21-2006, 12:20 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1