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)
.
.
.

 

[just_jon]

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,...

It seems that you want 3 identical sheets in the macro-workbook?

1] What records from book1 goto book2 -- All, some; if some, what determines which ones.
2] Do all the copied records go onto each of the 3 sheets? If not, what is the discriminator?
3] Do all fields from book1 goto book2? If not, which ones do go? In either case, do they go to the same columns? If not, how are the correct columns determined?

I think if you can provide some details regarding the above, or use Colo's utility below to put up a screenshot of what you have in book1 and what you want to have in book2 you'll probably get a solution. Right now, not enough to go on.

 

[Lurkey]

ARG!!

OK, I have really tried figuring out Colo's Utility. I have the html downloaded and have gotten all the way to the VBA Editor Page. I continue to get the error message that it cannot find a reference or library. For some reason, it won't allow me to choose Tools>References so I can find the missing link and disable it (per instructions given on this board). What am I doing wrong? Or, can I just email the spreadsheet to you Just_Jon? I would like to figure out the problem though...

Thank you!

 

[just_jon]

Check your Private Messages for email info.

 

[Lurkey]

Answering your questions (unfortunately, this may be difficult without an example)

1] What records from book1 goto book2 -- All, some; if some, what determines which ones. Almost all records go from book 1 to book 2. For example, Columns A-R but not S and T.

2] Do all the copied records go onto each of the 3 sheets? If not, what is the discriminator?
Yes, for each product name--data should fill out information on 3 worksheets. There are about 500 product names so at the end of this horrendous exercise, I should have 500 files (I also have another macro that automatically saves files under specific names after I click the macro box...hope this doesn't cause a problem in writing this data-filling macro..)

3] Do all fields from book1 goto book2? If not, which ones do go? In either case, do they go to the same columns? If not, how are the correct columns determined? No, not all fields go to each sheets. The title of the columns in book 1 should match cell names in each of the worksheets in book 2 (not necessarily in columns though). Obviously, if worksheet 1 doesn't have the words "product name" in a cell, than data cannot go in there. The worksheets look like tables, kind of like this:

Product Name xxx___________________L W H
Product Description xxxxxxx__________X_X_X
Pack Code xxxx
Std Cost xx

Unfortunately, I cannot change these worksheets because they're a template provided by the customer. Anyway, if anyone can take a crack at this, that'll be so awesome. In the meantime, Just_Jon has my spreadsheet so he definitely has an advantage (if you want to call it that ).

 

[Lurkey]

Below is a comments version of the macro Just_Jon developed. However, please note that although it runs on his computer, I keep getting an error message. He is still working on it!

(Note: I have bolded the path file names and file names that will need to be changed to fit individual spreadsheets)

***CODE***

Sub BustEmUp()

' Declare data types
Dim i As Integer, LastRow As Integer, NewBook As String
' Find the last used row by looking upwards in column A of the master file --
' Ifthe sheetname WorkbookA is changed in your workbook, change it below as well.
LastRow = ThisWorkbook.Worksheets("WorkbookA").Range("A65536").End(xlUp).Row
' Disable screen updating, so you don't see 500 files flipping by the screen; we'll
' restore it upon exit from he macro.
Application.ScreenUpdating = False
' If there are any errors, goto statement label AbEnd (Abnormal End) and resume
' execution there instead of topping here.
On Error GoTo AbEnd

' Open the template workbook and (the , 0 part) do not prompt regarding the updating
' of links.
Workbooks.Open "H:\My Documents\TEST\MrExcelExample-template.xls", 0

' Make the template the default referenced workbook
With Workbooks("MrExcelExample-template")


' For each row, begining with row #4 and going to the last used row, do the following --

For i = 4 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)
' Make a copy of the template workbook and save it as the name help by the variable NewBook.
ActiveWorkbook.SaveCopyAs NewBook
Next i

End With

AbEnd:
' Alert as to the number of records processed,
MsgBox "Number records/files processed: " & i - 4
' Close out the template file -- change the name below as needed.
Workbooks("MrExcelExample-template.xls").Close SaveChanges:=False
' Restore screen updating.
Application.ScreenUpdating = True

End Sub

 

[just_jon]

What error message is it that you get? Been meaning to ask that...

 

[Lurkey]

I am receiving the error message from the macro:
"Number records/files processed: "& i - 4"

 

[Lurkey]

One more note: if the path file names were incorrect, it seems like the error message would be:
"Subscript out of range"

I hope that helps.

 

[just_jon]

That's not an error, it's part of the exit routine; although, you will get that message if the macro errors-out as well. But instead of i-4, you should be getting some integer, in the test case, 5. With the 2 test files I sent you, did it come back saying 5 the 1st time you ran it?