Report

Use a macro to copy a value from one sheet to another

Ask a question azibuck 2Posts Monday November 6, 2017Registration date November 7, 2017 Last seen
Greetings, I'm looking for help modifying or creating a macro to copy the value of a specific cell on one sheet to a specific cell on another sheet.

I start with a sheet called "Job". I have a macro that successfully takes a value from Job and copies another worksheet, "Entry", and names the sheet for the value in a cell in Job. Here's what Job looks like:


Here's my macro, where I take the value in cell A14 and create a new sheet named 8, then the value in A15 to created a new sheet named 40, etc.

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Job").Range("A14")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets("Entry").Select
Sheets("Entry").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value
Next MyCell
End Sub


Here's what it creates:


Each sheet (in this case 8, 40, and 99), has some info and a cell to SUM values. The SUM is in cell A2. So I want to report that value back to the Job sheet, in the proper spot. So in cell B14, I want to pull the value from cell A2 in sheet 8, in cell B15, the value from A2 in sheet 15, and in cell B16, the value from A2 in sheet 99.

I hope that's clear, if not, please let me know and I'll clarify.

The chapter numbers will always vary, so I can't just hard-code in those numbers. I need to be able to read the value in Column A of Job and find the sheet named with that value.

Any help or direction would be greatly appreciated. Thanks!

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!