Macro to copy/paste data from one worksheet to another

[Lurkey]

HELLO~

WorkbookA: one workbook with 500 rows, 15 columns
i.e. Product Name, Product Code, Volume,...
WorkbookB: 3 worksheets with tables and more or less of WorkbookA column headings all in a table format, where the user types in the product name, product code in various cells. (i want to run the macro from this workbook on sheet 1).

My goal: I have 500 rows of data to enter in 3 different worksheets, which will result in a lot of human error, I'm sure.

1. I want to copy data from WorkbookA and dump it in the appropriate cells for the 3 worksheets in WorkbookB.

2. For example, if I had "apple" under the column heading Product Name in WorkbookA and I run the macro, "apple" should automatically insert into WorkbookB!Sheet1, WorkbookB!Sheet2 and WorkbookB!Sheet3 along with all the other info associated with "apple" like product code, volume,...

Here is what I have come up with so far. Case 1 is copies/pastes "Product Name" (Column A); Case 2 copies/pastes "Product Code" (Column B); Case 3 copies/pastes "Volume" (Column C). Obviously, I am new at this and would appreciate any help!


Sub CopyDataFromBboxes()

'
'CopyDataFromBboxes Macro
'
'SHEET 1 WORKSHEET ENTRY
'

Select Case (PRODUCTNAME)
Case 1
Windows("WorkbookA.xls").Activate
Range("A2:A506").Copy
Windows("WorkbookB.xls").Activate
Sheets("Sheet1").Select
Range("C3").PasteSpecial (x1Values)
Sheets("Sheet2").Select
Range("H8").PasteSpecial (x1Values)
Sheets("Sheet3").Select
Range("B5").PasteSpecial (x1Values)
Case 2
Windows("WorkbookA").Activate
Range("B2:B506").Copy
Windows("WorkbookB").Activate
Sheets("Sheet1").Select
Range("C4").PasteSpecial (x1Values)
Sheets("Sheet3").Select
Range("H5").PasteSpecial (x1Values)
Case 3
Windows("WorkbookA").Activate
Range("C2:C506").Copy
Windows("WorkbookB").Activate
Sheets("Sheet1").Select
Range("C7").PasteSpecial (x1Values)
Sheets("Sheet2").Select
Range("H10").PasteSpecial (x1Values)
Sheets("Sheet3").Select
Range("C6").PasteSpecial (x1Values)
.
.
.

 

[Lurkey]

With the 2 test files, the error message did NOT say "5"...only "i - 4."

 

[just_jon]

I can't imagine it would make a difference, but try changing this line --

MsgBox "Number records/files processed: " & i - 4

to this line --

MsgBox ("Number records/files processed: " & i - 4)

in other words, surround the message box items in parens. This one's driving me nuts! Works great w/ Office2000.

 

[Lurkey]

1. Error message now says "Number records/files processed -4"

2. I am also using a 2000 version of Excel (SR-1 Premium)

3. Yes, this is driving me mad too.

 

[just_jon]

OK, now we know it's crapping out prior to the For i=4 to LastRow loop; Let's try this.

1] Hit ALT+F11 to bring up the VB Editor;
2] If the macro isn't visible, double click on Sheet1 (WorkbookA) to pull it up;
3] Click on Debug up on the toolbar at the top, then select Step Into
4] Each time you hit F8, the macro will execute one more line of code. Do that, slowly, and see on which line it terminates its run.
5] At any time during the Step Into process, you can hold your cursor over variable names - such as NewBook, and the underlying value should appear; might want to check that one to make sure it is a good reference.

Post back w/ results.

 

[just_jon]

Next we're going to comment out the statement - by putting a single quote in front of the entire line - On Error GoTo AbEnd.

 

[Lurkey]

it's workin..........

You will not believe this (or maybe you will...), it actually ran and saved the files. I apologize if this was the problem all along, but I used the comments version of the macro (as stated before) versus the code version and for some reason, it started running.

In any case, if I want to repeat the following portion of the macro for the other columns of data (not only product name), where do I need to insert the additional data---and is there a "short-cut" way?

For i = 2 To LastRow
'Assign the variable NewBook to be the quoted string + the current value in column A (the
' product name) + the quoted string -- making each of 500 filenames here, 1 at a time.
NewBook = "H:\My Documents\TEST\" & ThisWorkbook.Worksheets("WorkbookA").Cells(i, 1) & ".xls"
' Assign the product name to the correct cells in each of the 3 template pages, cells B5/M4/C7.
' If these sheet names change in the template file, make sure you change them here as well.
.Worksheets("WorkbookBsheet1").Cells(5, 2) = ThisWorkbook.Worksheets("WorkbookA").Cells(i, 1)
.Worksheets("WorkbookBsheet2").Cells(4, 13) = ThisWorkbook.Worksheets("WorkbookA").Cells(i, 1)
.Worksheets("WorkbookBsheet3").Cells(7, 3) =
ThisWorkbook.Worksheets("WorkbookA").Cells(i, 1)
DO I INSERT HERE for say the next column of data which is description in col. 2 of the sheet WorkbookA and row 6, column C of WorkbookBsheet1? Would I start with: Worksheets(WorkbookBsheet1").Cells(6,3)=ThisWorkbook.Worksheets("WorkbookA").cells(i,2) and than copy this down for the rest of the 10 columns???
' Make a copy of the template workbook and save it as the name help by the variable NewBook.
ActiveWorkbook.SaveCopyAs NewBook
Next i

 

[Lurkey]

Note: I just posted my comment before reading your note...do you think I should still follow what you said?

 

[Lurkey]

Sorry, one more note...

OK, one more note...I noticed on the template that you had inserted VLOOKUP formulas. Is this necessary for me to input this formula 500X or can I modify the macro to do that like it did for the product name?

 

[just_jon]

Dana --

If you'll look at the template file, you'll see that all of the other fields are filled out with VLOOKUP formulas that tie back to your master file, or other formulas ( the =TODAY() for the date, the formula to compute the sq ft, for example) so once you've seeded the Product Name in the file, all other relevant data will key on that cell and import the rest of the fields automatically, just via formula instead of VBA. I did it this way so that if, say a price change or vendor name was altered in the master file, the change would automatically be updated in each of the product files the next time they were opened and the links refreshed. If all the fields were filled via VBA, then the macro would have to be rerun to force these changes to be reflected.

You could, of course, get the macro to import all the fields, but would lose the above-mentioned flexibility. If you really want this to happen, then this statement --

.Worksheets("WorkbookBsheet1").Cells(6, 2) = ThisWorkbook.Worksheets("WorkbookA").Cells(i, 2)

would place the description from the master sheet into the correct cell on the 1st sheet of your individual file. Note: There is a period at the beginning of that statement, and it does need to be there.

If you really want a total macro solution, let me know and we can fix the macro to do that, but again, you might lose the ability to capture small numbers of changes without rerunning the entire macro.

 

[just_jon]

We seem to be just missing each other's posts! Those VLOOKUPs from the template get saved, along w/ the Product Names, in each of the 500+ files, so in fact the macro has already copied them for you as it ran and saved each successive file.