Home » Forums » Open Source Learning Center » Question and Answer

Copy a range of cells from worksheet in workbook a to worksheet in workbook b

bill5174's picture
Submitted by bill5174 on 7 October, 2010 - 17:30

I have worksheet (part a) in workbook(quote) with 30 rows of data which change with macro execution. I need to copy a range on worksheet (bom-part M) in workbook(master) to last row on worksheet (part a) in workbook(quote) when a Cell value in Q17 to Q40 of worksheet (part a) in workbook(quote) is the name of a worksheet in workbook(master).  The variable values in cells Q17 to Q40 may contain one to five names of different worksheet names in workbook(master) from which I need to copy the range  of each from onto worksheet (part a).

Example: Q23 of worksheet (part a) in workbook(quote) = to "bom-part M", a sheet name in workbook(master), I want to copy range A16:L40 of worksheet (bom-part M)  to last row of worksheet (part a) in workbook(quote). Then

repeat for next matching value, say in Q29 in worksheet (part a) in workbook(quote) = to "bom-part Z", another sheet  in workbook(master), I want to copy range A16:L40 of worksheet (bom-part Z) to last row of worksheet (part a) in workbook(quote). Then repeat till no more cells meet matching value.

Would this be a macro, a script or can a formula in worksheet (part a) accomplish this?

Thanks Bill

. 

‹ Operating a dos like window(screen) with the help of excel Help with a Formula ›
»
Nick's picture

copy cells from one workbook to another

Submitted by Nick on 8 October, 2010 - 09:24.

 

Hi Bill

There are 2 aspects to your question...

  1. Copying data from one place to another.
  2. Finding the next free cell.

I have a few articles for you to read:

http://excelexperts.com/copy-values-vba

and:

http://excelexperts.com/VBA-Tips-Find-The-Last-Cell

Nick

 

»
bill5174's picture

Thanks for reply, but let me refine my question

Submitted by bill5174 on 8 October, 2010 - 15:51.

Hi Nick,

 

I reviewed your links, and the "last cell" I plan to incorporate in script. Here is my stumbling point: When the value changes in cells Q17 to Q40 and if they contain a worksheet name in workbook(master) that needs to be copied, is there a formula to 1) modify worksheet name in the script, then 2) run the modified macro script to perform the copy function?

Hope this clarifies, and I look forward to your reply.

Thanks,

Bill 

»

pick up worksheet name

Submitted by Anonymous on 11 October, 2010 - 14:18.

if you have a worksheet name in a cell, and you want to select that worksheet (for example)

Worksheets(Range("Q17")).Select
so if Range("Q17") = "ABC"
sheet "ABC" will be selected

To rename a worksheet:
Worksheets("A").name = "B"
- renames worksheet A to B.. (provided B does not exist already)

»
bill5174's picture

worksheet name

Submitted by bill5174 on 11 October, 2010 - 15:08.

What if the worksheet is in a different workbook?

Thanks,

Bill

»
Nick's picture

worksheets in different workbook

Submitted by Nick on 21 October, 2010 - 08:23.

workbooks(Range("xxxx")).activate

Worksheets(Range("Q17")).Select

 

 

»