Changing Source Material From Sheet To Sheet?

May 12, 2014

I have been working on a workbook that uses as a source information on Sheet 1.

Sheet 1

Each item reports a number of different details from column A to column BY.

Each row is for a different item.

25 rows of information.

Sheet 2

This is my master formula sheet, the one I've been using to create the page that reports and formulates information on a single item. The source for this sheet is taken from the cells in Sheet1!Row2.

Now that I have the sheet pretty much finished, I want to paste the same report on each succeeding page for a total of 25 sheets. I can copy Sheet2 and paste into each successive sheet, but then I need to change every single formula so that where it said AT2 it changes to AT3, where it was H2, H3, etc., from A to BY.

Problem: Can I use something that will automatically take the information for succeeding sheets and take as its source the succeeding row from the source page?

I don't know that I'm describing that sufficiently, so:

Sheet 3 would take as its source Sheet1, row 3.
Sheet 4 would take as its source Sheet1, row 4.
Sheet 5 would take as its source Sheet1, row 5.
Sheet 6 would take as its source Sheet1, row 6.

Etc., etc.

In the past I used the find/replace function, but this sheet seems way too complicated for that. As an example, one cell alone on my master sheet is


so I would need to change S2 to S3 everywhere it appears, but there are probably more than a hundred places on the sheet that I'd have to manually change 2s to 3s, etc., for 25 sheets, which is why I'm wondering if there isn't a better way than going cell by cell, sheet by sheet.

View 1 Replies


Changing A Chart Source Sheet

Feb 9, 2007

I have a sheet ( Graphs A) with 50+ graphs on all linked to a datasheet (Data A).

I now want to copy these graphs to another sheet (Graphs B) which links to a different data sheet (Data B). The data is in the same layout format etc.

The problem is that the find-replace function will not replace the sheet name in the charts series formula from Data A to Data B

Is there an easy way to change the source sheet for the new graphs?

View 3 Replies View Related

Stop Macro From Changing Onto Sheet Top Sheet And Flickering

Dec 24, 2008

How can I stop my macro from changing onto sheet top sheet and flickering away like mad??

View 2 Replies View Related

Another Sheet As A Data Source

Jun 15, 2009

I want to design a spreadsheet report, but instead of importing the data from an external source, i would like the data to come from another sheet within the same workbook. The goal is that the data sheet will contain only raw data with no formatting, and the report sheet will contain all the formatting.

View 9 Replies View Related

VBA To Create New Workbook For Each Row On Source Sheet

Feb 23, 2009

I have one excel file with several columns and rows of data.

Then I have a template excel with titles and some tables (no formulas, no macros, etc).

I need to generate a new workbook (based on the template) for each row of data from the source excel.

View 9 Replies View Related

Creating A Source Sheet To Pull Information From.

Aug 17, 2007

I'm trying to do is create a macro that will autopopulate information from a source sheet. What I have is a different route sheet for 25 or so technicians within my company on one worksheet.

I want to be able to have a source sheet that I can change daily and have it autopopulate the information needed into the appropriate cells/sheets from this one source sheet.

I am pretty sure there is a way to do this, but I'm not familiar enough with creating macros to do this myself, although if I had an example of one cell and a simple explanation of how to fit this to the information needed I think I could do this on my own.

View 9 Replies View Related

Save Sheet With Same Path As Source Workbook

Dec 28, 2006

I've go a shared Workbook, which will be distributed among several users and stored in different places. The workbook uses the following

Sub savemeas()
Worksheets("data").Visible = True
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:="d:" & Range("a1").Value, _
FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=True
End Sub

to save the sheet "data" as a text file with a name based on the value of cell a1. All I need is to modify the code so that the target path would not be

ActiveWorkbook.SaveAs Filename:="d:" & Range("a1").Value

but be the same as the source Workbook's - so that I wouldn't have to modify the code for each user separatly, because the sheet would alwayas be saved in the same folder as the current path of the source workbook.

View 4 Replies View Related

Match Data On Source And Destination Sheet And Change Value?

Jan 17, 2014

I have extracted data from multiple sheets and now want to mark this data as "Paid" and for the macro to go back and change the value in the original sheet on a row in column "AR" to "PAID.
All the columns have the same data type in.

The extracted data contains the original sheet name and each row has a unique ID No to it.

I know I probably need to use INDEX and MATCH and then Offset to post value.

Attached worksheet shows the data and columns.

View 14 Replies View Related

Concat Two Cells In The Source Worksheet And Vlookup Into The Defs Sheet

Aug 26, 2009

I've used vlookup before but his #REF problem has me stumped.

I want to concat two cells in the Source worksheet and vlookup into the Defs sheet, but I keep getting #REF(G4:G6) when I want to return the 2nd column in the Defs sheet. What's curious is when I modify the vlookup to return the 1st column I correctly get the lookup cells(G6:G9).

What's wrong with the formula in G4.

My requirement is I can't change the format of the Source worksheet because it contains macros that work with ERwin. And it is MUCH bigger than the attached sample so I will copy/paste the formula down 6000+ cells.

View 2 Replies View Related

Copy Determined Columns From Source To Destination Sheet In Particular Order

Oct 27, 2011

I have a source data sheet that has around 50 columns. I want to copy and past selected columns to a destination sheet.

View 1 Replies View Related

Copy Data On New Lines From A Source Sheet Based On Criteria

Oct 2, 2006

I have a source sheet which contains data. Within this data there is a column (column 3) called Name and I would like to take the data from this sheet and paste it onto a new sheet but in grouped format. So take all instances where it finds criteria 1, then 2 and so on and just paste it on a new line each time it finds a criteria. So far I have the following code to search

Sub loopy()
Dim rngTemp As Range ' the range variable declaration
Dim intCounter As Integer 'counter as integer
' the following loop goes through all the cells in column a up until the last used cell in the column
For Each rngTemp In Range("a1", Range("a" & Rows.Count).End(xlUp))
'increment the counter if the cell has value fridge
If rngTemp = "Fridge" Then intCounter = intCounter + 1
Next rngTemp
MsgBox "There were " & intCounter & " cells equal to Fridge"
End Sub

View 2 Replies View Related

Copy Chart To New Sheet & Have Charts Source Data Reference It

Jan 24, 2008

Sheet1 contains a chart using a dynamic name in Source Data: Values. The dynamic name data is controlled by another cell. When I copy the sheet - i.e. Sheet1 (2) - and change the control cell, the Source Data: Values in the copied sheet still refers to the original Sheet1.

View 3 Replies View Related

Excel 2007 :: VBA For Print Header For Multiple Sheets From One Source Sheet (but Not All)

Apr 18, 2013

I am working on a "3 worksheet" excel workbook. The first worksheet does not require any header.

I'd like to enter data into the second sheet (say cells A1 and B1), and use VBA to pull from those cells to generate the same custom header for both the second and third worksheets.

For example, I'd like the header to pull "# 123456" from cell A1, and "789" from B1 in sheet two, putting them in a centered header for both sheets two and three (same reference cells from sheet two for both, not new values of A1 and B1 from sheet 3 for sheet 3 header). I'd like to format in a way that looks something like this:



I'm currently running Excel 07, and was able to pull from a cell on one worksheet into that sheet's header but couldn't get it to span multiple sheets.

View 3 Replies View Related

Auto-Populate Column Data From Source Sheet After Selecting From Dropdown List

Jan 11, 2013

I'm trying to make a spreadsheet that can be used to easily build a collective list of steps, for a user to read and follow line-by-line.

I want a source sheet of "steps" that I can change over time, and the resulting tabs that reference the source sheet get updated/populated automatically.

I've pieced together some VBA code from other sources, which kind of does what I want it to:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 And Target.Column = 1 Then
Dim SourceSheet As Worksheet
Set SourceSheet = ActiveWorkbook.Sheets("Steps")
Dim TargetSheet As Worksheet
Set TargetSheet = Target.Worksheet
Dim c As Integer
Dim Source As Range


But there are some problems:

1) The data auto-populates into a row. It would read better if each step was in the same column, meaning rows would need to be automatically added upon selecting something from the drop-down list. The number of rows that need to be added vary based on the number of steps in the source sheet, for the selection made from the drop-down list.

2) If you make a change to the source sheet, my goal is to have the other sheets referencing the source sheet's lists of steps update automatically, so you only need to update the steps in one spot and everything you've built from them gets updated instantly. Currently, you must select a different choice from the drop-down list, and then change it back, before it populates the "new" steps from the source sheet.

This is my first time using VBA.

What I have so far is attached: testAutoPopulate.xlsm

View 2 Replies View Related

Worksheet Protection From Editing And At The Same Time Allow All Data Source Links Within The Sheet To Be Refreshed

Mar 16, 2007

how to protect a worksheet from editing and at the same time allow all data source links within the sheet to be refreshed? I need my users to be able to refresh all data links as required but not be able to change the worksheet in any other way.

View 2 Replies View Related

Changing Name Of Sheet Using VBA

Apr 7, 2013

I have a workbook with multiple sheets. Each sheet has a title (just words in a cell). Is there VBA formula that will have the sheet take the name of the title and also chnage when the title name changes?

View 9 Replies View Related

Pivot Table To Refresh And Source Data To Change Using Visible Rows On Filtered Sheet

Feb 26, 2014

I have 5 pivot tables on 5sheets, all looking at the same source data in sheet 6

On the source data there is a filter on the headers, if you change the filter, is it possible for all 5 pivot tables to update according to the filter?

My starting point is the below, but there probably is a better way but i would want the sourcedata to equal visible rows on the source data sheet headers run A:K and up to row 10000 .

View 2 Replies View Related

Changing Value In One Sheet Based On Value In Another?

Apr 22, 2013

A workbook that has a main sheet, and a few other sheets that main data is copied to. The main sheet is a complete list of work, the other sheets are split down to each machine we use here, to keep track of machine capacity.

What I want Excel to do:

Each works order has a number of hours attached to it (Which is manually entered on each sheet). Once a week a button will be clicked and the VB code needs to look at the different machine sheets, and do some kind of lookup between the works order number on the main sheet. If it says "Complete" in column H on the main sheet then it needs to put the number of hours to zero on the machine's sheet.

View 1 Replies View Related

Changing Value - New Value Goes Into The Next Available Cell On Another Sheet?

Jul 31, 2014

I have a cell that is being populated by a formula. Is it possible that every time the value of that cell changes, the new value goes into the next available cell on another sheet?

To simplify things:
Sheet1 A1=5 Sheet2 A1=5
Sheet1 A1=6 Sheet2 A1=5 A2=6
Sheet1 A1=9 Sheet2 A1=5 A2=6 A3=9

And so on.

View 2 Replies View Related

Prevent Changing Of Sheet Name

Mar 16, 2007

I have been searching for a way to prevent a user from changing a worksheet name. I ran into some code from the post below (see link) but I find that the code lets the user change the name and save the changed name of a sheet; only when the user selects a different sheet does it change the name back to the set name (sname) from the code.

I'm sure there must be a way to make this happen without deactivating the sheet, but I can't figure it out. Is there some other sort of event that could cause this to happen.

View 9 Replies View Related

Changing Sheet Name When Cell Changes

Nov 26, 2007

Is there a way of changing the Sheet name when a value in a Cell changes. i.e the sheet name is the same as the cell value

View 2 Replies View Related

Changing Size Of Excel Sheet

Jan 1, 2008

Is it possible to make a worksheet smaller. What I want to do is to be able to tab from, a1 to b1 to c1 to d1 etc up to g1 and then if I tab again it goes to a2. Is this possible? Is there any way of making it skip the a coloumn when tabbing even if it has data in it?

View 10 Replies View Related

Changing Data On One Sheet To Reflect Changes On Another?

Jul 1, 2013

My goal is to have changes done to one sheet reflect on to another to make things much more easy to do. Below is a picture of what I am dealing with. ( I covered some personal information)

pic of excel.jpg I want to be able to plug in something or delete it and have it reflect to all other sheets. Also I would like to be able to plug in some information and have it export itself based on a specific information. For example, I would want all Newark,NJ contacts to be place in the Newark list automatically.

View 4 Replies View Related

Continual Changing Sheet Names

Nov 24, 2008

I want to capture data from another workbook each month where the sheet name that I need the data from changes each month. i.e. 0810, 0811, 0812, 0901. Are possible sheet names. I've tried to build this unsuccessfully into the my -

View 5 Replies View Related

Changing A Protected Sheet From A Form

Apr 23, 2009

I would like to be able to add data to a protected sheet, only from a form without unprotecting the sheet first. Below is the code.

View 5 Replies View Related

Event Macro Changing Sheet Name

May 23, 2009

I have a worksheet called "Roster" with a list of names from D7:D17 each person on the list has a separate sheet that is called by their name. the sheets are not linked up. the names were just manually typed into the list and into each sheet name.

If any name is changed on the list on "Roster!D7:D17" i would like the sheet that has that name to change as well.

This is the code that i was sent, but i do not know enough about VBA code to understand exactly what is happening, thus i cannot diagnose the problem. I placed the following code into the page "Roster" (by right-clicking and then pasting the code) then i closed, but nothing happens. I can mess with the code and get an error message. but that is about it. error messages usually say something about the debugger, or runtime error or something.

there is the
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldVal(0) = OldVal(1)
OldVal(1) = Target(1).Value
End Sub

View 9 Replies View Related

Inserting Row Without Changing Reference Cell On Another Sheet

Jul 3, 2014

I am trying to create a formula that references information on two different sheets, but i want it to show me the values that are newly created when a new row is inserted. The two current sheets are Master and Data.

I have a formula on Master that reads ='Data'!$K$3 to reference the value on Sheet data in cell K3. When a new row is inserted showing me the current readings, I want the Master sheet to show me the NEW value in cell K3 on the data sheet. The problem is, the insert of a row moves the formula on the Master sheet to now be ='Data'!$K$4. How can i STOP the insertion of a row from changing the formula so i continue to see the value i need?

View 3 Replies View Related

Changing Which Row Is A Table Header When Queiring A Sheet

Jan 7, 2010

I have a excel worksheet which i am exporting into a word document through a macro labeled "Go". Basically it will query the excel sheet and the word document i have, will take the values and put them in the correct placing.

My problem is...

I know that excel will use the first row as the table headers, but what if i want it to start at like row c? and use that row as table headers. How would i go about doing that?

View 5 Replies View Related

Adjust Formula To Allow For Changing Sheet Reference.

Nov 22, 2009

I was using the formula below which was working fine for copying across 20 columns and down however many rows, but now the requirements have changed to 90 columns. I have tested this out with the 90 columns but the Indirect function is bogging down the spreadsheet with the constant recalculating.


I rearranged the formula to the one below, but how can I have the reference to the sheet name changed as in the formula above without using the Indirect?


The sheet reference will change from '1' through '90'.

View 7 Replies View Related

Changing Sheet Number Automatically While Dragging

Mar 8, 2007

I have worksheets named 1 to 100. In a different worksheet "Summary" I have a summary of data of those worksheets. I want to change the worksheet number automatically in a row by dragging.
For example, in worksheet "Summary" ....

View 9 Replies View Related

Copyrights 2005-15, All rights reserved