0

Current Situation: I am consolidating a data package that receives multiple inputs from programs that roll into mine. Essentially, each program provides me their set of data and I am responsible for accurately consolidating data into one Excel workbook in a certain format. I have pushed out a template so that all the data comes back in the same format, but now I am having trouble quickly consolidating besides using "copy and paste".

Solutions I've looked into: I've looked into using connections to see if can pull in the data I need through a connected file however it imports the table in a different format and does not transfer correctly into the consolidation template that I have set up. I tried doing external source referencing to pull in data that way, but again the formatting is off. I know there are ways to just pull in the workbook, but I need data from specific points in these program workbooks that I can slot into my consolidated workbook. In most cases, copy and pasting would work, but I have over hundreds of programs that flow into this data sheet so any help would be appreciated!

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
toddexcel
  • 1
  • 1
  • 2
    Welcome to StackOverflow. Please note, that this is not a free code-writing service. Yet, we are eager to help fellow programmers (and aspirants) writing their own code. Please read the help topics on [How do I Ask a Good Question](http://stackoverflow.com/help/how-to-ask). You might also want to [take the tour](http://stackoverflow.com/tour) and earn a badge while doing so. Afterwards, please update your question with the VBA code you have written thus far in order to complete the task(s) you wish to achieve. Also, you might want to give us a specific question to which you expect an answer. – Ralph Aug 31 '16 at 17:46
  • First thought would be to break down your process into steps and try to get each step working. As you struggle through that, post questions here for help in getting things working. It may take some time to learn how to do all you need to do, but that is coding I guess. My thought is that you should loop through and open each workbook, input the necessary data (copy/paste has a lot of overhead, so recommend a different approach) then close it and move to the next. – Kyle Aug 31 '16 at 17:50
  • 1
    @Kyle At first I wanted to post a comment like yours. But then I figured that there are too many options (and routes) to go about this question. It is simply too broad to answer it as is: should it be done with formulas and simple `VLookUps` or with VBA? If VBA, should it be simple `Workbook.Open` and then `.Copy` and `.Paste`? Should it be imported using `ADO`? Is a central SQL an option? I believe that the OP needs to do some more research or (better yet) chat with someone to find out what he/she wants: http://chat.stackexchange.com/rooms/info/21/the-whiteboard – Ralph Aug 31 '16 at 18:30
  • 1
    @Ralph, that's a great point. The actual end goal of this (and resources available) would dictate a large portion of how this should be designed. – Kyle Aug 31 '16 at 18:35

1 Answers1

1

How about setting the destination range to the values of the source range? e.g.:

rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
EBH
  • 10,350
  • 3
  • 34
  • 59
Coolshaikh
  • 146
  • 4