I am looking for assistance in writing code to copy the values in one workbook to another workbook. There are a some variables that are complicating it for me to put together.
I am looking for a macro to reside in wkbk 1; to copy/paste data from each sheet of wkbk 2 into active sheet of wkbk 1.
The working cells in wkbk 1.sheet("KEY DATA") are Range("J3:U13")
There are approximately 9 worksheets in the second workbook, depending on user.
The 9 worksheet names correspond to column "A" matches in active worksheet.
There are 12 cell values in each worksheet to copy to active worksheet. It is always this 12.
In the attached workbooks - there would be 108 cell values copied and pasted
I am attaching the 2 workbooks to help understand my request.....
What is making it difficult for me is trying to do a function.match for row #'s.
I need to match each value in wkbk 1 Range("J1:U1") to wkbk 2 Range("K9:K88") to identify correct row number. Then copy that cell data that is offset. The row # will be different for different users.
...And I maybe....
I need to match each value in wkbk 1 Range("A1:A13") to wkbk 2 'sheet names' to identify correct row number that is my active cell. But I am not sure I need to do it this way.
First cell would be wkbk1.sheet("KEYDATA").ran
ge("J3") = wkbk2.sheet("0004").range(
then range("K3") is pasted, then the next column... until column "U" is reached
The next worksheet in the workbook is activated and the same process is duplicated.
My source data workbook is:
source wkbk = Workbooks(i).Name Like "CRG OPERATING PARTNER*"
I have different users, but all my source workbooks will start with this name.
In the source wkbk, starting with the second worksheet to the last it should be pulling the offset of column "K"
"Food Costs" .offset(0,-1)
"Total Labor" .offset(0,-1)
"Paper Costs" .offset(0,2)
"Gross Profit" .offset(0,-1)
"Cash & Credit Card Over/Short" .offset(0,-1)
"Cleaning Supplies" .offset(0,-2)
"Equipment Maintenance" .offset(0,-2)
"Technology R&M" .offset(0,-2)
"Building Maintenance" .offset(0,-2)
"Store Operating Income (Loss)" .offset(0,-2)
"Store Operating Income (Loss)" .offset(0,-8)
I tried different ways but I am hitting a wall. Hence I am asking for assistance.
I deleted all my code since it was pretty botched up. But I can retype & resend.
I thank you kindly for any help you can give me.