If sheet name equals range

[vba_rookie91]

Hi All,

I need help creating a macro and have not been able to find the solution I'm looking for in this thread so figured I would make my first post. I need help creating a macro that copies a cell from one sheet into another sheet based on the name of that sheet.

I have included dummy data for an example below. In this example, my workbook has 3 blank sheets named Example 1, Example 2, Example 3, and fourth sheet called Template, which includes the data below. I would like the macro to take the numbers in the input column and put them into their corresponding sheet in cell A1 of that sheet. For example, sheet "Example 1" will include "10" in cell A1 and sheet "Example 2" will include "20" in cell A1.

Sheet Name Input
Example 1 10
Example 2 20
Example 3 30

I recognize this is very simplistic, but I will be repeating this processes for over 20 inputs and the inputs / sheet names will update every quarter/year. The data will always be in the same spot in the "Template" tab and the data will always be input into the same cells in the various sheets, but the number of sheets and inputs themselves will change each time this is ran.

Appreciate any help with this!

[bakerman2]

For starters.

Please Login or Register  to view this content.

[MarvinP]

Hi rook and welcome to the forum,

See if this code will start you in the correct direction...

Please Login or Register  to view this content.

WithWorksheetPutValue.xlsm

[vba_rookie91]

Hi Marvin,

Really appreciate the help with this! It was able to work and I have been able to replicate for other instances that I need.

One follow up question for you. Is there a simple tweak I could make to the code you previously provided that would perform this function for a few cells at a time? So Assume I now have columns Input 1 - Input 3 and I want to put them in each sheet based on the sheets name in columns A - C (i.e. A1, B1, C1 cells would all have a value now). I tried updating the ["Range("A1") = Cells(RowCtr, "B")] section of the code but was not getting it to work.

Thanks in advance!

[MarvinP]

Hey Rook,

It would a lot better if you could show what you tried and even give us a sample workbook. Did you have exactly 3 input columns? Did you want the number of columns to be variable? My best guess with your updating code is you didn't put the period before the word "Range". Show me what you tried and give me a sample workbook with the harder problem and I'll see what I can do.

[vba_rookie91]

Hi Marvin,

I attached an example workbook with the desired output. It will always be the same number of columns that will be required to be copied into each sheet and they will be copied in the same location on each sheet (i.e. cells B2:H2 from the template sheet will be copied to cells B10:H10 in each sheet). The only variable in my example is the number of tabs, which is based on the number of sheet names in column A from the template tab.

[MarvinP]

OK Rook,

The With and Period before the Cells is of huge importance. When you use the Dot or Period in front of a Range or Cells it means to go to the "With" sheet. See if this code does what you need. You can use any number of columns of data now. Hope this helps...

Please Login or Register  to view this content.

VBA With SheetName Columns.xlsm

[vba_rookie91]

Thank you Marvin! This worked perfectly.