PDA

View Full Version : Macro to open workbook copy data from workbook 1 and paste in workbook 2 and repeat.



j.stitt1
03-09-2018, 02:34 PM
Hello,

I am looking for some help to make a macro that will look at cell B1 in workbook1 which will contain a 5 digit number (Job Number) and then go to the same file directory ( 'S:\Job Files\18594.xlsm ) and open the file then from

workbook1 copy data from cells sheets ("Sheet1").Range ("B2") and cells ("Sheet1").Range ("C7:D63") then paste that data in workbook2 in sheets ("Hours & Cost Input").Range("E6:F62") then close and save workbook2.

After completing the 1st Job Number repeat the same process on the next Job Number in cell B65 and offset for the copy cells will always be the same and the paste location will always be the same.

This repeat could happen up to 24 time and I will set up the so the job number has the same offset from the last number.

The data in Cell B2 is a date and Cells C7:D63 are numbers.

Any help would be appriciated.

Thanks
Jeffrey

werafa
03-09-2018, 02:39 PM
Hi Jeffrey

this post has also just been listed with almost the same question.
http://www.vbaexpress.com/forum/showthread.php?62205-VBA-Combining-Data-from-Multiple-Workbook-into-One-Need-help-ASAP

I've shared the code I use to locate and open source data wbs here

j.stitt1
03-09-2018, 03:32 PM
Werafa,

Thank you for replying so quick.
i looked at the link and i am totally confused. this is still new to me and any extra help would be great.

Jeffrey

werafa
03-09-2018, 07:27 PM
start by doing a google search for 'excel vba workbooks.open' and see what you find. this should explain how this command works.

step 1 for you to implement this bit is to create the 'myString' value - which should contain the file path and name.
test whether this book is open by using the 'bookopen' function - ive elected to close and reopen it rather than if closed then open.
then open your workbook, using the set myWB = workbooks.open command. this turns your newly opened workbook into a named object in your excel code that you can call by name instead of reference (this makes coding easier, and code runs faster).

once you have got this far, go to step 2, which is to get at the data that you want to copy.

werafa
03-09-2018, 07:56 PM
once you have your workbook, ID your data range and set this as a range object

eg, set mySheet = myWB.worksheets('Data')
and myRange = mySheet.range("A2:H" & lastRow)
to get your lastrow, use lastRow = lastRow = mySheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

once you have this range defined, you can treat it as a variable, and ignore the fact that it is in another workbook.

there are many resources on the web that explain different ways to copy data from one range to another. 5 minutes on google will give you a good background, and you can pick your favorite method. My only suggestion is to avoid using loops to write data into the new sheet where you can.

werafa
03-09-2018, 08:04 PM
finally, to actually implement the code, write a separate code sub/function for each task, and use a 'master' sub to call each in turn.

you can use a function to open a workbook and return myWB as a workbook object
you can send myWB to another function and return a range object
you can send a range object to another sub and write the range contents to another sheet.

this makes each segment of code relate to a single task, and makes code much easier to debug.
a good rule of thumb is to try to make each sub or function fit in the screen - and certainly no longer than two screens long. this also helps greatly with code readability and debuging