Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Connecting Two Sheets

If data in A1, Sheet 2 = to A1, Sheet 1, then B1, Sheet 2= to B1 Sheet 1, otherwise Nil.

This will be put in Column B1 of Sheet 2 ....

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Connecting Two Sheets ..
I need to connect my two sheets using the value in a column...

Is it possible in Excel?

Actually in one sheet i have only the empno number and his performance report.

(in tabular form)

but in other sheet i have full information about that employee, So I wanna click on any empno and jump to other sheet to see details about that employee...

I cannot use hyper link as i have 50,000 employees and it will be very tedious to hyperlink all of them one by one...

View Replies!   View Related
Connecting The Dots
I have 1 row of user entry cells (A1:Z1). To keep it simple, let’s say A1 is always 0 and Z1 is always 100.

The user can enter any positive number in any of the cells. For Case 1, let’s say ‘20’ in M1.
I want a set of formulas in A2:Z2 that fills in the values with straightlined values from 0 to 20 from A2:L2 and 20 to 100 in N2:Z2.

For Case 2, the user enters ‘10’ in M1 and ‘60’ in T1 and the formulas in A2:Z2 fill in the appropriate values that connect the empty cells (i.e., 0 to 10, then 10 to 60, then 60 to 100).

Basically, a user enters 1 or more numbers in 1 row, and the second row fills in all the non-entered cells with #s that are straightlined, thus ‘connecting the dots’.

I think a bunch of nested IF statements might work, but it’ll be hairy and nested IF statements are a resource hog. Any better ideas? I’m think maybe some INDEX and MATCH functions.

View Replies!   View Related
Connecting Macros..?
I have 14 ranges like this on the same spreadsheet and would like to print all of them based on the result in the target cells for the range of each to print. I currently have a macro button to print each range as you can see. Is it possible to connect these? What I have tried is cutting the End Sub and then the Dim r As Long but get errors.

View Replies!   View Related
Connecting Points In A Graph
I have a data and it has been plotted into a graph. Here's what i have: I have 2 points, point A and point B with caps, 1.00 and 2.00 respectively. So now i need to draw 3 parallel lines, i.e from the caps and the main point itself. How do i do that? Manually will be of course selecting a line from the shapes. Any suggestions?

View Replies!   View Related
Connecting Between VBA Excel And Web Site
I have a some problem with connecting between VBA Excel and web site.

I wrote a macro to insert the data from excel but there is one problem with displaying the correct view.

I use this site to get the price of the bonds:

it is the site of russian exchange

First of all I write the ISIN code, for example RU000A0E6X12, in the "Quote Search" field and then press Enter for getting the data. After it I can choose the History information or Online from the last field in the left margin. I want to chose "History" and after it the dates from and till are appearing automatically. The automatization of it is my task. And I have only one problem in the end.

The program:

View Replies!   View Related
Connecting A Date To A Range
I would like to do a sort of lookup function, but am having trouble.

What I would like to do is:

In one column, list all financial years (ie 2006-07, 2007-08 etc - listed in cells A2 - A10). In a particular cell, I will have a date, lets say cell B2. Then in cell B3, I would like to know which financial year that this date in B2 fits in. So for example, if the date was 10/10/2007, it would return the following "2007-08".

View Replies!   View Related
Count Function In VBA When Connecting To Access
I have an access database: 2007.mdb
I have an access table: All Data - Pre07

I have an excel worksheet which connects to 2007.mdb and selects data from table All Data - Pre07 using a 'user defined' filter via an input box. This filter sets the group which is one of the fields on the All Data - Pre07 table.

The code which performs the above function is shown below.

I wondered whether it was possible to perform a Count function in vba (rather than a Select Function) which will just count the data in access and then return the count rather than importing all the data.

For example, I would like to count the number of rows in the Access table - All Data - Pre07 which have a group = 'AA' and then return the count to WorkSheet 'AA'.

Is it also possible to perform multiple count functions in one connection or do I need to reconnect (i.e. write the code again).

Application.ScreenUpdating = False
Dim mygroup As String
Dim datinput As String
mygroup = InputBox("Enter Group", "Please Enter", "AA")
datinput = "'" & mygroup & "'"

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:2007.mdb;DefaultDir=C:;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _ ................

View Replies!   View Related
Connecting To A Database Without The Local Machines Odbc
I'm working on a spreadsheet that will connect to a firebird DB (Programmed using VBA and ADO intially).. The spreadsheet itself will reside on a server in a shared folder..

When a user opens the file up, I'd like for the spreadsheet to use the servers ODBC connection, and not the client machine.. This way we don't have to install the Firebird ODBC driver on each client workstation..

View Replies!   View Related
Line Connecting A Cell To A Table....and Locked On The Cell
I have multiple data tables in one sheet...and each one has a that sheet. To better allow the viewer to coordinate quickly which data table corresponds to which cart...i want a line between them. Excel already has the draw line function, but if you move the chart around, the line doesn't move.

View Replies!   View Related
Copy Data From Sheets In Workbooks In Folder To Main File Sheets Of Same Name
I would like to use VBA to search a folder and copy data from tabs within the excel files there. The data will be pasted to a tab of same name in the the main file. All the files are in the same format.

So far I have only managed to list the files in the folder using code I found on your site!

View Replies!   View Related
Run-time Error '1004' Method 'Add' Of Object ' Sheets' Failed Adding Multiple Sheets
I have been running a simulation for about 18 hours now and just received:

Run-time error '1004':
Method 'Add' of object ' Sheets' failed

I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:

ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)

The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...

View Replies!   View Related
Hiding Sheets But Still Letting Macros Run When Printing Hidden Sheets
i have a workbook that has the following sheets

working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)

i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro

my macros involve printing certain pages dependng on what button is pressed

i get an error whatever
how do i stop this

View Replies!   View Related
Delete Chart Series Across Sheets But Skip Protected Sheets
I have received following macro from someone to delete series but the problem is that it gives error when some sheets are protected, and I want those sheets to protected. When run it will ignore/leave protected sheets but delete series only from unprotected sheets

Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
Dim dr As Long
Dim dc As Long
dc = Sheets("Deleted Numbers").UsedRange.Columns.Count - 1
dr = Cells(Rows.Count, Sheets("Deleted Numbers").UsedRange.Columns.Count - 1).End(xlUp).Row + 1
If dr = 60001 Then ................

View Replies!   View Related
Copy From Multiple Sheets (26), PASTE To 1 Sheet From 26 Sheets
I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.

I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.

View Replies!   View Related
Re-Naming Sheets Per Cell Data & Hiding Sheets
on sheet1 I have a button I need to do the following when clicked:
(1) name the next 30 sheets based on cell values in sheet1
(2) for those 30 sheets, hide some of them based on a y/n input in sheet 1

To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.

View Replies!   View Related
Copy 2 Linked Sheets Without Links To Original Sheets
I have copied and then pasted & linked numerous cells from one sheet to another within the same workbook. When I copy the 2 sheets (Edit>Move or Copy Sheet>Create Copy) the linked cells on the duplicates remain linked to the original sheet. How can I copy the 2 sheets and have the cells on the copied sheet be linked to the copied sheets and not the original?

View Replies!   View Related
Move Specific Data From Sheets To Other Sheets
Need to move data from 14 sheets (1 pay period=14 days)(2 showing on attachment for example purposes) to time cards for each person (number of employees will vary).

Key data to move to the time card is the date of the hours, #Reg Hours, #OT hours, the ticket# and job# for those hours.(ie Chris Adams Aug 15, need Chris's Reg and OT hours (8 Reg and 2 OT) on his time sheet, and the corresponding ticket number and job number (in this case from I1, I2).

Unknown number of ticket/job numbers, but the pattern of cell entries will remain the same along the top of the date sheets.(starting at I1, then L1, O1, the next would be R1)

Unknown number of employees but they will continue filling down where the names are on the date sheets (column A). Each employee would have a time card sheet as well.

View Replies!   View Related
Count Sheets To Make Sheets Statistics
how to count sheets to make sheets statistics. (see code)

View Replies!   View Related
How Increase Sheets Number+1 For All Sheets With Vba?
How increase Sheets number+1 for all Sheets with Vba?

View Replies!   View Related
Sheets Array To Include All Sheets Between First And Last
how do i create a sheetsarray to include all sheets between First and Last? I plan to loop through each sheet in this array to copy data to a summary page, as per [url]

View Replies!   View Related
Combine To Sheets In One Sheets Using Macro
Combine to sheets in one sheets using macro ...

View Replies!   View Related
Adding & Naming Sheets "X" No Of Sheets Dynamically
I have an Index Sheet where I would be typing the name of the Sheet and a Command button to execute the operation of Adding the Sheet with the name mentioned in the Column C..

View Replies!   View Related
Lock Sheets With Formulas And Lock Sheets With Values
do you know if there is vba code to lock formulas so someone sees but does not touch and the same for values see but cant change??

my sheets with formulas are SUMMARY and Sheet2

sheet with value is TABLE

View Replies!   View Related
Add Sheets
i want to add sheets to my workbook
and i want evey sheet to get it name form range("b5:b262")

View Replies!   View Related
Sum Across Many Sheets Using VBA
I have 2 columns, the first with codes, the second column I want to add up.

Although, the codes in the first column maybe repeated across sheets and I would like to sum the second colum with the codes in the first column.

I would like this on a separate "Totals" sheet using VBA. example:

On sheet 1

col1 col2
1234 1
1324 9
1245 4

On sheet 2

col1 col2
1233 2
1324 9
1244 4

On the "Totals" sheet:

col1 col2
1233 2
1234 1
1324 18
1244 4
1245 4

View Replies!   View Related
Vba To Copy Sheets And Name Them
I'm very new to vba and I'm trying to copy a sheet called "Data" several times and I want each new copy to take it's name from a list of names which I have in a sheet called "Names". The code below seems to work fine, but the problem that I’m facing is that if I run the macro again and a sheet name already exists then I get an error massage. So I want to modify the code below, so that before creating each new copy, it checks for the name to be given to that new copy, and if the sheet exists then skip creating that copy and goes to the next one.

(The "Names" sheet contains the list of names, with the first name is in cell A1, second name in A2, A3,....)

View Replies!   View Related
Copy Between Sheets
I have data in Sheet1 of the Excel sheet. I want to copy the content of Column C & D of Sheet1 into Sheet2 using a macro.

I am able to do using below logic (But i think there is a simple logic):
Get the Last record count of Col C
For i=1 to Last
Next i

Since I am using for loop, it takes long time if the data is huge.
Could you please let me know whether my approach is good or there exists a simpler method.

View Replies!   View Related
VBA Comparing Two Sheets
I have 4000 activities in the rows; I want to know if there are changes occur (e.g. duration, dates, quantities & etc.).

I would like to compare the two sheets with the same Columns and Identifiers, Say sheet1 and Sheet2 and I want the result to be in Sheet3. Sheet1 is the target & Sheet2 is the current changes.

Is it possible? What will be my code?

View Replies!   View Related
Delete (2) From Sheets Name
How sould I remove (2) from sheets names

Like sheet1(2) change into sheet1

View Replies!   View Related
Comparing Two Different Sheets
I’m trying to figure out how to compare two different versions of a sheet with each other and to identify which lines has changed. Here is a simplified example:

>Microsoft Excel - post data.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE4J4E5J5E6J6E7J7=BCDEFGHIJ3V1CONCATENATE V2Change check4123123 1012 No Change5456456 465Changed6789789 8 9Changed710 121012 123No ChangeSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

When using this formula I get an error in cell J12 that reports a no change even though the values has changed. The error occurs due to the blank cell found in cell C7 but I can’t seem to find a way around the problem? (except for adding an if(cell=””;0;cell) before every cell which isn’t really practical in my actual data)

Furthermore my actual data has more the 10.000 lines and +30 columns so if the formula I use for change tracking can be done easier in any other way I would be happy to get some help? Maybe I should even consider a macro?

View Replies!   View Related
Deleting Sheets If It's Name
Checks if there is a sheet name and if there is a sheet of that name then it is deleted.

the sheet name is a number entered through an inputbox and "Average"

View Replies!   View Related
Navigate Through Sheets
i have an excel file with over 30 sheets on it. Contained in each sheet is a seperate pivot chart. i am looking for away to navigate around these sheets from the main screen. does anyone have any suggestions or opinions on the best way i can do this? here are my ideas but not sure which would work best

1. Create main sheet at the start and have hyperlinks to all sheets (by doing this how could i navigate back to home page?)

2. Create userform with drop down list of sheet names or search function to search for sheet names and then click on a button to go to sheet? dont know would i be able to do this? will the userform follow to the sheet with the pivot chart so i can select a different one from the same page? how could i hide the userform to view the chart and then bring it back to navigate to another?

View Replies!   View Related
Agents Sheets
attached file which contains about (150) agents sheets,i need a way which will move me to the desired sheet by pressing its name from the list in the main one , i need this because i shall hide all the sheets.

View Replies!   View Related
Hiding Sheets
A workbook with 7 sheets in it. The first sheet contains buttons with macros that when clicked will navigate you to different sheets in the workbook based on what button you click. Here's what I want to do. When the workbook is opened, I want it open to that first sheet with the buttons, but show no other tabs for the other sheets at the bottom. And then when that button is clicked on the first sheet to go to a different page, I want that page to be the only visible tab in the workbook with the rest still hidden. Is that doable? I already have the buttons and codes and everything for the navigation part down, I just need to have the other hidden when I'm on a different sheet.

Sub Button10_click()
Worksheets("Account Type").Activate
End Sub
Sub Button11_Click()
Worksheets("Name Change").Activate
End Sub
Sub Button12_Click()
End Sub
Sub Button13_Click()
Worksheets("Main Menu").Activate
End Sub
Sub Button15_Click()
End Sub
Sub Button16_Click()
End Sub

View Replies!   View Related
Combine Two Sheets Using Vba
How to combine two worksheets (sheet 1 and sheet2) to a sheet named “combined”?
Sheet 1 and sheet 2 contain data wich is filled up by using formulas. The data range is from col A to F with headers in the first row, the length is variable.

Here’s an example:

ColA ColB ColC ColD ColE ColF

33 44 46 84 36 567
10 12 56 98 100 64
0 0 0 0 0 0
0 0 0 0 0 0

ColA ColB ColC ColD ColE ColF........

View Replies!   View Related
Creating New Sheets And Name Itself With Vba
i have a named range that is one column wide and changing in lenght, rows. I want a sheet to create and name itself the same as the value in the cell for each value in the named range. if a name in the range should be removed then so should the sheet.

View Replies!   View Related
Compare Two Sheets
I have two spreadsheets each with "Vendor names" on column A. sheet 1 is my master sheet and sheet 2 is the sheet that has the information i like to carry over to sheet 1 when the conditions are true. Hence when colum A on sheet 1 equals column A on sheet 2 copy the range of cells b-h in sheet2 to match to sheet1.

here is my code i figuere that if i stared with two columns it would be rather simple however i cant even get my own code to work. Any help or suggestions would be greatly appreciated. By the way my spreadsheet is 6,000+ lines long

Here is the code

Sub compares()
Dim rng1 As Range
Dim rng2 As Range
Dim RowNo As Long

Set rng1 = Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
Set rng2 = Worksheets("Sheet2").Range("A1", Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp))
For Each c In rng1
If Application.WorksheetFunction. CountIf(rng2, c) > 0 Then
RowNo = Application.WorksheetFunction.Match(c, rng2)
c.Offset(, 1).Resize(1, 2).Value = Worksheets("Sheet2").Range("B" & RowNo, "C" & RowNo).Value
End If
Next c
End Sub

View Replies!   View Related
Lookups Between Sheets
I have a player list, from which i have put a sample below:

Garry Adamson ar 23 2 45 12
Charlie Baxter ar 10 0 23 14
Iqtiwar Chaudray ar 0 0 1 2
Hugh* Doulton ar 0 5 12 10

On the left is the player name and then to the right are their scores for the first 4 weeks. Each team that has entered the league has a tab of its own with the 11 players they have selected on. I would like to just have to update the main sheet and then have a look up in the other tabs on an if the player is ion this squad basis... I have attached an example of the whole player list and an example of a team on the 2nd tab.

View Replies!   View Related
Subtotal All Sheets ..
What i would like to be able to do is subtotal all the sheets (Approx 190 Sheets) with subtotals in Column I and (K to AA) with each change in Column D Period reference D3 till the bottom the data is entered. As an example i have attached a sample with one sheet 77001 showing what result i would like.

View Replies!   View Related
Sumproduct Between More Than Two Sheets
How can I use SUMPRODUCT between more than two sheet (Sheet1 to sheet4)

I can use this formula for one sheet it gives me the correct result. But I have to give sheet range form 1 to 4 How?


View Replies!   View Related
Protecting All Sheets
I am trying to protect all sheets on a Workbook Close event.

I don't want the user to even be able to Select the Loked cells so I set it up that way through formating of cells.

This is what I have tried:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Protect Password:="P@ssword", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
' sh.Protect Password:="P@ssword"
Next sh
End Sub
I even tried the commented out line in that postion instead of where it is now.

It does protect but still allows selection of locked cells.

View Replies!   View Related
Protection Of Sheets
I have at average 10 sheets in one workbook. I have to protect 9 workbooks.

Can I protect one workbook in one go rather than protecting each sheet in every workbook?

View Replies!   View Related
Print 2 Sheets
I have 2 sheets that I want to print off, these are called Questions & Answers.
I have the following code to print off Answers, how do I amend this to print off Questions & Answers?

Selection.Font.ColorIndex = 1
.Visible = True
End With
End Sub

View Replies!   View Related
Integrate Two Sheets
In Sheet1 I have the following horizontal fields:

Order Qty#

In sheet2 I have the following horizontal fields:

How can I automatically add the address fields from sheet2 to the appropriate line in sheet one with the matching cust#?

View Replies!   View Related
Same Cells - Different Sheets
I have 20 sheets in my workbook, I know if i use the following code, that it sums all of the values AC10 across my 20 sheets.

However, is there a way that I can show the value of AC10 in each sheet without having to manually select or edit the cell in the function/formula?

View Replies!   View Related
Update Sheets
Could you have a look at my attached sheets?

2 sheets here, one is "TEMP" the other is " SUM".I want to have a command button in the "SUM", in order to do the below actions the right now A columns between "SUM" and "TEMP";

2.If it does not exist in "SUM",i.e. it is new,then move the row to the last row of "TEMP";

3.If it exists already in "SUM",then update the row.

View Replies!   View Related
Dim Array As Sheets
I'm trying to create an array of Sheets objects and use them. This gives me a type mismatch on the marked line below. I could just save the sheet names as strings in the array.

Dim SheetList() As Sheets, A As String

Set ORng = Sheets("Options").Range("ReachPagesHeader").Offset(1, 0)
PageCount = Range(ORng, ORng.Offset(1000, 0).End(xlUp)).Count

ReDim SheetList(1 To PageCount)
For X = 1 To PageCount
A = ORng.Offset(X - 1, 0).Text
Set SheetList(X) = Sheets(A) '

View Replies!   View Related
Run 13 Different Sheets Each With 50k Rows
It has to run on 13 different sheets each with 50k rows. Right now its taking nearly 35 minutes a sheet.

For i = 12 To 100000
line_count = line_count + 1
scanning = scanner(scanning)
current_time = timer(start_time, current_time)

x = x + 1
lngRows = ActiveSheet.UsedRange.Rows.Count

If Cells(i, 1) = "" And Cells(i, 2) = "" Then Exit For
If Not (Cells(i, 3) Like "*loop*") And Not (Cells(i, 5) Like "*Fail*") Then
i = i - 1
End If
If Cells(i, 3) Like "*loop*" And Cells(i + 1, 3) Like "*loop*" Then
i = i - 1
End If
Application.StatusBar = "Sheet: " & ActiveSheet.Name & " - " & current_sheet_number & "/" & sheet_count & _
" | Time: " & current_time & " | Current Row: " & i & " - " & "End Row: " & lngRows & " / " & x & " | " & _
line_count & "/" & total_lines & " | " & scanning
Next i

View Replies!   View Related
Delete All (except One) Sheets
I would like to delete all except one sheets in the workbook by pressing onto a button. So I wrote the folllowing code (its obviously only a part)

Application.DisplayAlerts = False
For Each Worksheet In Worksheets
If (Worksheet.Name "Instructions") Then
End If
Application.DisplayAlerts = True

and put it into a Module in the same workbook. The button itself is in one of the sheets to be deleted, so I assign a simple macro to it which is just calling the code above.

Well, sounds simple, but it does not work, with "Subscript out of range" error at the attempt to delete one (not the first one) of the sheets.

Searching around suggested that the problem appears when the worksheet I try to access does not exist (missspelling of a name or so). but in my code I dont really use it.

View Replies!   View Related
Consolidating Sheets One Below The Other
I have written a macro to consolidate multiple sheets into one sheet. I have two sheets which are to be copied one below the other.I am attaching the code for this here.

ub Bingo()
Dim ws As Worksheet, n As Long, flg As Boolean, last As Long
On Error Resume Next
Application.DisplayAlerts = False
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add(before:=Sheets(1)).Name = "Consolidated-Input"
n = 3
For Each ws In Worksheets
If ws.Name "Consolidated-Input" And ws.Visible And ws.Name "DCS-User" And ws.Name "Cal" And ws.Name "DCS" = True Then
If Not flg Then
Sheets("Consolidated-Input").Range("a1").PasteSpecial xlPasteValues
Sheets("Consolidated-Input").Range("a1").PasteSpecial xlPasteFormats
flg = True...................................

View Replies!   View Related
Copyright © 2005-08, All rights reserved