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
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...
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.
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.
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?
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.
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".
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;" _
.CommandText = Array( _ ................
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..
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:
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...
Hiding Sheets But Still Letting Macros Run When Printing Hidden Sheets
i have a workbook that has the following sheets
receipt of deposit letter
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
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 ................
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.
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?
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.
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.
On sheet 1
On sheet 2
On the "Totals" sheet:
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,....)
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
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.
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?
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:
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?
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?
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.
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.
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........
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
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
Lookups Between Sheets
I have a player list, from which i have put a sample below:
ALL ROUNDERS W1 W2 W3 W4
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.
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.
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?
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"
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.
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
Integrate Two Sheets
In Sheet1 I have the following horizontal fields:
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#?
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?
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
1.compare 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.
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) '
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
If Cells(i, 3) Like "*loop*" And Cells(i + 1, 3) Like "*loop*" Then
i = i - 1
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
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
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.
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.
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
flg = True...................................