You are here:

Excel/Copy data from one worksheet and paste them to another worksheet

Advertisement


Question
QUESTION: Hi Tom,

I have a question about copying and pasting data from one worksheet to other worksheets. I am using Excel 2007.

Let's say I have a worksheet "Product", which has two columns:
column A: Product ID - Three rows:
cell A2: 1234
cell A3: 3456
cell A4: 5678
Column B: Product Name - Three rows as well:
cell B2: Window
cell B3: Door
cell B4: Key

"Product" worksheet looks like this:
Product ID   Product Name
1234          Window
3456          Door
5678          Key

And then I have three other worksheets called 1234, 3456, and 5678, which the worksheet name matches the Product ID in the "Product" worksheet. Also, in worksheet 1234, 3456, and 5678, the first column is called "Product ID" as well. The value in the Product ID field matches the worksheet name.

"1234" worksheet looks like this:
Product ID   Unit Price   Count   Total Price
1234          $100          500   $50,000

"3456" worksheet looks like this:
Product ID   Unit Price   Count   Total Price
3456          $200          100   $20,000

"5678" worksheet looks like this:
Product ID   Unit Price   Count   Total Price
5678          $50          100   $5,000

I want to write a VBA code to copy data from "Product" worksheet and paste it to a specific row (let's say row 10) in 1234, 3456, and 5678 worksheets, where the "Product ID" from "Product" worksheet matches the "Product ID" from 1234, 3456, and 5678 worksheet respectively.

These are the final results I would like to see:
Worksheet 1234:
Product ID   Unit Price   Count   Total Price
1234          $100          500   $50,000

Product ID   Product Name      
1234          Window      


Worksheet 3456:
Product ID   Unit Price   Count   Total Price
3456          $200          100   $20,000

Product ID   Product Name      
3456          Door      


Worksheet 5678:
Product ID   Unit Price   Count   Total Price
5678          $50          100   $5,000

Product ID   Product Name      
5678          Key      


I am fairly new to VBA. Thanks in advance for your help.

Tim

ANSWER: Tim,

This should do what you describe.

sub ABC()
Dim sh as worksheet, sh1 as worksheet
Dim r as Range, cell as Range
set sh = worksheets("Product")
set r = sh.Range("A2:A4")
for each cell in r
 set sh1 = nothing
 on error resume next
   set sh1 = worksheets(cell.text)
 on error goto 0
 if not sh1 is nothing then
   cell.Resize(1, 2).copy sh1.cells(10, "A")
 end if
Next
End sub

I assume the headers:  Product ID         Product Name
are already in A9 and B9 on each of the individual product sheets.

--
Regards,
Tom Ogilvy




---------- FOLLOW-UP ----------

QUESTION: Thanks Tom for your quick response. The headers: Product ID  Product Name are not already in A9 and B9 on each of the individual product sheets. I will need to copy them over as well along with the data.

For example: This is the final results that I would like to see in "1234" worksheet.

Product ID   Unit Price   Count   Total Price
1234          $100          500   $50,000
        
Product _ID (cell A10)   Product_Name(cell B10)   
1234 (cell A11)          Window (cell B11)

Thanks again for your help!

Tim

Answer
Tim,

perhaps this

sub ABC()
Dim sh as worksheet, sh1 as worksheet
Dim r as Range, cell as Range
set sh = worksheets("Product")
set r = sh.Range("A2:A4")
for each cell in r
set sh1 = nothing
on error resume next
  set sh1 = worksheets(cell.text)
on error goto 0
if not sh1 is nothing then
  cell.Resize(1, 2).copy sh1.cells(11, "A")
  sh1.cells(10,"A").value = "Product_ID"
  sh1.cells(10,"B").value = "Product_Name"
end if
Next
End sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.