Free Microsoft Excel 2013 Quick Reference

Comparing and picking up values from another worksheet

I need help! I have various short worksheets that have to check 1 long reference worksheet.

E.g. In Worksheet 1 Column E (blank), I was wondering if I can enter a formula that checks Worksheet 2... compare names in both worksheets and if the names match and there is an "X" in worksheet 2 column D corresponding to the matching names, then an "X" should be post under column E worksheet 1.

Not sure if this will show well, but I attached a file in notepad.

Worksheet 1
A B C D E
Class Name Annual Default Federal Funds
Principal Browne, Patricia 81396 $81,396.00 (blank)
Vice Prin Reyna Diaz 76960 $76,960.00 (blank)
Teacher ABRAMO, DELILAH 39720 $39,720.00 (blank)
Teacher ALLEN, SARAH 55320 $55,320.00 (blank)

Worksheet 2
A B C D
Name Code Code Def
ABRAMO, DELILAH 0 Unrestricted
ACOSTA, ELIA 7090 ECONOMIC IMPACT AID EIA/SCE
ALLEN, SARAH 0 Unrestricted
ALTEMURA, ROSA 3010 TITLE 1 IASA-1 BASIC GRANTS LOW-INCOME X


I have a summary sheet on my spreadsheet that picks up values from cells in different sheets. However, I need the formatting to be picked up as well (cell supplying values may have mixed formatting [e.g. italics, bold] within the cell).

Any ideas?

TIA

Les

I'm trying to return a value from another worksheet and don't want to use any VBA. I'm also having trouble with the syntax of the VLOOKUP command.

Worksheet A = data screen
Worksheet B = Lookup Values A1=Name B1=Number
Worksheet A in B1 type value --validated against WorksheetB B1 (this works)
return value in WorksheetB A1 into WorksheetA A1

I probably worded it more confusing than it needs to be, but I can't seem to get the vlookup to work:
=VLOOKUP(B1,BinList!$A$1:$B$7,1)
it returns #N/A

Any help without having to resort to VBA would be very much appreciated..!

I need to create a list of production figures for different facilities from a folder saved on the common server.

The folder is titled 'Daily Production Reports' and it has three subfolders titled 'CPF' , 'Manzalai' and 'Makori'. Within each subfolders are folders for the year 2009, 2010 and 2011 which once opened has 12 folders for each month of the year. In each of these folders are the workbooks that contain daily production values for each day of the month separately.

I made a macro to pick up values for the 1st of Jan 2011 from each of these folders for product A and B.
The resulted row has the data in the following order:

1-Jan-2011, CPF Product A value, CPF Product B value, MGP Product A value, MGP Product B value, Makori Product A value, Makori Product B value.

(the comma inserted above is differentiating each cell in the row just for reference)

The macro goes into each of the respective folders and picks up both product values for each facility and inserts it into the same row.

Each row value has the following route:
Row 1 column 1,2,3:Daily Production Reports>CPF>2011>January>DPR_CPF_01_01_2011

Within this sheet, the date is in cell reference: J21
CPF Product A value is in cell: T31
CPF Product B value is in cell:T38

Row 1 column 4,5:
Daily Production Reports>Manzalai>2011>January>DPR_MGP_01_01_2011

Within this sheet, the Manzalai (MGP) Product A value is in cell: R29
Manzalai(MGP) Product B value is in cell: R33

Row 1 column 6, 7:
Daily Production Reports>Makori>2011>January>DPR_Mak_01_01_2011

Within this sheet, the Makori Product A value is in cell: R29
Makori Product B value is in cell: R33

This is the route for the year 2011 and month Jan, I need to do this now for all the months and dates in the 3 years 2009, 2010 and 2011 which would be listed down in order.
For all the other files though the name of the month and year changes but the cell reference remains the same for the values.

The macro I've made so far is as follows:

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            
            .LookIn = "datacenterCommonProduction ReportsProductionDaily Production ReportsDaily Produciton
Reports"
            .FileType = msoFileTypeExcelWorkbooks
            
            
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                        Workbooks.Open Filename:= _
        "datacenterCommonProduction ReportsProductionDaily Production ReportsDaily Produciton ReportsCPF2011January
2011DPR_CPF_01_01_2011.xls" _
        , UpdateLinks:=0
    ActiveCell.Offset(-107, 0).Range("A1").Select
    Selection.Copy
    Windows("macro.xlsm").Activate
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Windows("DPR_CPF_01_01_2011.xls").Activate
    Sheets("Partners Report ").Select
    ActiveWindow.SmallScroll Down:=-30
    ActiveCell.Offset(23, -2).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Windows("DPR_CPF_01_01_2011.xls").Activate
    ActiveCell.Offset(7, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Windows("DPR_CPF_01_01_2011.xls").Activate
    ActiveWindow.Close
    Workbooks.Open Filename:= _
        "datacenterCommonProduction ReportsProductionDaily Production ReportsDaily Produciton ReportsMGPDaily Production
Reports(Manzalai)2011January 2011DPR_MGP 01.01.2011.xls"
    Sheets("Partners Report ").Select
    ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Offset(-31, 1).Range("A1").Select
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Windows("DPR_MGP 01.01.2011.xls").Activate
    ActiveCell.Offset(4, -1).Range("A1:B1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Windows("DPR_MGP 01.01.2011.xls").Activate
    ActiveWindow.Close
    Workbooks.Open Filename:= _
        "datacenterCommonProduction ReportsProductionDaily Production ReportsDaily Produciton ReportsMakori-EPFMakori
EPF Daily ReportsDaily Production Reports2011January 2011DPR_MAK_01.01.2011.xls"
    Sheets("Partners Report").Select
    ActiveWindow.SmallScroll Down:=-21
    ActiveCell.Offset(-18, -5).Range("A1").Select
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Windows("DPR_MAK_01.01.2011.xls").Activate
    ActiveCell.Offset(-7, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("DPR_MAK_01.01.2011.xls").Activate
    ActiveWindow.SmallScroll Down:=-6
    ActiveCell.Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("DPR_MAK_01.01.2011.xls").Activate
    ActiveCell.Offset(1, 4).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    ActiveCell.Offset(-1, -4).Range("A1").Select
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, -1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Offset(0, -6).Range("A1:G1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.Offset(1, 0).Range("A1").Select
    Windows("DPR_MAK_01.01.2011.xls").Activate
    ActiveWindow.Close
    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
But this macro only picks up values for that one specific date. To do the same for all the files in the Daily Production Reports folder I either need to make the macro more generic? But how is that done? I'm not good with macros and this is my very first time. If there is any other solution to my problem I would be very grateful for your help.

Plus when a solution is given please explain the procedure in detail as Im an amateur in the field.

Hi all, I have another problem with my work.

Well, I need to create a VBA that will identify when a cell in column (L) shows "Expired", it will automatically copy a value from a cell from another worksheet and paste in back in the cell in column (M).

I tried using this VBA:

Sub
Sheets("Special Grooming Package").Select
Dim lRow As Long
lRow = Range("L" & Rows.Count).End(xlUp).Row

Sheets("Daily Form").Select
Range("B9").Copy
Sheets("Special Grooming Package").Select

For i = 13 To lRow
If Cells(i, 12).Text = "Expired" Then
Cells(i, 13).PasteSpecial Paste:=x1PasteFormulasAndNumberFormats, Operation:= _
x1None, SkipBlanks:=False, Transpose:=False

End If
Next i

Sheets("Daily Form").Select
Range("C9").Select

End Sub

I have attached a sample for easy reference... Hope someone can guide me on my VBA... Thanks!

Is it possible to pick up values from another worksheet by combination of values contained in the original worksheet? For example:

Cell B5 in the original worksheet has the value that says "ProfitSheet" and Cell B6 says "J21"

So is it possible for a cell to pick up the values from 'ProfitSheet!J21' ?

The reason why I am asking is because I have 50 odd worksheets and needs to be dynamic...

Any solution short of writting a macro will be greatly appreciated![/list]

People here have helped me greatly in the past and i'm hoping you can help again.

I'm using the following formula to pick up data from another worksheet

=SUMPRODUCT(--(RawActuals!$A$1:$A$10000="jun"),--(RawActuals!$E$1:$E$10000=A236),RawActuals!$M$1:$M$10000)

It's picking up most of the data, but it's missing some - and it's to do with formatting of SOME of the data within the RawActuals sheet

212605Agency Staff - Professional & Other212701Staff Cost Tfr to Change Programme221112Property Rents - Passing Rent221310Hire, office/courtr'm/related eqpt

The 1st, 3rd and 4th lines are correctly picking up the data - but the 2nd is not. They are all set to the same number format.

Anyone any suggestions as to why it's not working and more importantly how it can be fixed?

Hello there expert excellers!

I am trying to retrieve a cell value from another excel sheet using the VLOOKUP function.

Basically what I want is for excel to search columns in another excel file and if it finds it I want to retrieve the value next to it. For example, I want it to search 'food' in another excel file and when it finds it I want to retrive value next to it such as 'apple':

please help and many thanks.

I want a cell in one worksheet to copy a text value from another worksheet and maintain that referenced cell's text formatting. That formatting includes a change in font size within the cell and wordwrap.

Whenever I try a simple referencing it copies the text correctly but does not copy the formatting. Is there a way to do this? I'm ameneable to using VBA code if it is required.

Thanks.

Cell B24:B34 contain specific data from another worksheet.

For instance, cell B24 contains the following:
=IF(N('Project List Worksheet'!C4)=0,"",'Project List Worksheet'!C4) After I select cells B24:B34 and drag down using auto-fill I am hoping to yield the following pattern:
=IF(N('Project List Worksheet'!C6)=0,"",'Project List Worksheet'!C6) And continue that "+2" pattern for future cells in the new worksheet instead of getting this result:
=IF(N('Project List Worksheet'!C19)=0,"",'Project List Worksheet'!C19) in cell B39.

Thanks

I'm creating a check register. Each sheet has a half dozen columns, and each sheet will be a single month. Of the columns, 3 will have a drop down list that the user can pick a value from. It works fine if I put the values in a separate column on each sheet. But I woul like to have the values on a sheet by themselves, and then the list reference that sheet and column. When I try to enter the source into 'data validation', the only thing that appears is the formula, not the list.
For example, the drop down shows the formula... ='xxxx'!(a1:a20) instead of the list ie..
Vendor #1, Vendor #2, etc.

What am I doing wrong???

Thanks in advance.

I have two cells 1) is a named cell called Estimate its $A$1 (= and gets its value from another worksheet),

2) Another named cell Actual ($B$1), Which has no value in it.

When the user enters a value in named cell Actual ($B$1), I want to clear all values in named cell Estimate($A$1).

This same function would apply to several cells. I am not a vb programmer, I was trying to do this with a function but have not had any luck.. I need this to occur dynamically, not by pressing a button. Anyone have any good ideas?

I have two cells 1) is a named cell called Estimate its $A$1 (= and gets its value from another worksheet), 2) Another named cell Actual ($B$1), Which has no value in it.

When the user enters a value in named cell Actual ($B$1), I waI I III want to clear all values in named cell Estimate($A$1). This same function would apply to several cells. I am not a vb programmer, I was trying to do this with a function but have not had any luck.. Anyone have any good ideas?

I have two cells 1) is a named cell called Estimate its $A$1 (= and gets its value from another worksheet), 2) Another named cell Actual ($B$1), Which has no value in it. When the user enters a value in named cell Actual ($B$1), I want to clear all values in named cell Estimate($A$1).This same function would apply to several cells. I am not a vb programmer, I was trying to do this with a function but have not had any luck.. I need this to occur dynamically, not by pressing a button. Anyone have any good ideas?

Hi, I'm currently working on a spreadsheet to track my Customer schedules, receipts, production, quality status and despatches. To do this I have a number of different tables within it. However when the new schedules are received from the customer some of the data I have on my information is more up to date than the customers.

In one worksheet I have a table with the new customer data, columns such as Order No, Delivery date, Part No etc etc. I then have another worksheet (internal tracking document) with identical data plus when the order was released, completed and despatched. I would like to create a formula that will look up the order numbers in the new customer data table find that order number in the internal tracking document and then return the value in the despatched column for the order number.

I am thinking some kind of if and lookup formula would work by saying find the order number in column A then if you find it return the value in column B for that row.

Sounds easy but I'm stumped!!

I am trying to perform a find and copy function from one excel worksheet to another worksheet.
Unfortunately the data in the Source worksheet, is not in a regular format so I need to search for the first data cell "name 1" and copy the value in the cell immediately to the right of it to say row one column one of the target worksheet.
Then find the second data cell "number 1" and copy the value in the cell immediately to the right of it that to row one column two of the target worksheet, then find third data cell "address 1" and copy the value in the cell immediately to the right of it to row one column three.
I then need to go back and start to find first data cell of the next record, i.e. "name 2" and copy the value in the cell immediately to the right of it to the next row but the first column, then "number 2" to the second row second column etc. etc.
I can't just offset the cell references as the data is not in a regular pattern so I must search for it each time I go back to the source worksheet.

If you anyone is able to help me out with this it would be much appreciated.

Many Thanks in advance.......Fimez

** PLEASE DO NOT REPLY TO THIS. KEEP TO THE ORIGINAL MESSAGE**
May 2006 - added Userform Listbox version at the bottom
Because this comes up so frequently I have attempted to write a more generic routine that can be amended to suit various requirements.
You will need to change the named variables' values where indicated.
Code:
'=========================================================
'- GENERIC LOOKUP MACRO TO
'- FIND A VALUE IN ANOTHER WORKSHEET
'- AND RETURN A VALUE FROM ANOTHER COLUMN
'=========================================================
'- select the cell containing the first search value
'- and run this macro from there.
'- can be set to continue down the column
'- [** need to make changes below as required **]
'- Brian Baulsom  May 2005
'==========================================================
Dim MyValue As Variant
Dim FromSheet As Worksheet
Dim LookupColumn As Integer
Dim FromRow As Long
Dim FromColumn As Integer
'-
Dim ToSheet As Worksheet
Dim StartRow As Long
Dim LastRow As Long
Dim ActiveColumn As Integer
Dim ReturnColumnNumber
Dim ToRow As Long
Dim FoundCell As Object

'=============================================================
'- MAIN ROUTINE
'=============================================================
Sub DO_LOOKUP()
    Application.Calculation = xlCalculationManual
    '----------------------------------------------------------
    '- LOOKUP SHEET                     [**AMEND AS REQUIRED**]
    Set FromSheet = Workbooks("Book1.xls").Worksheets("Sheet1")
    LookupColumn = 1    ' look for match here
    FromColumn = 2      ' return value from here
    '-----------------------------------------------------------
    '- ACTIVE SHEET
    Set ToSheet = ActiveSheet
    ActiveColumn = ActiveCell.Column
    StartRow = ActiveCell.Row
    '-------------------------------------------------------------
    '- COMMENT OUT UNWANTED LINE, UNCOMMENT THE OTHER
    '- ..............................[** FOR MULTIPLE ROWS **]
    'LastRow = ToSheet.Cells(65536, ActiveColumn).End(xlUp).Row
    '-
    '- ..............................[** FOR A SINGLE VALUE **]
    LastRow = ActiveCell.Row
    '-------------------------------------------------------------
    '- COLUMN NUMBER TO PUT RETURNED VALUE [**AMEND AS REQUIRED**]
    ReturnColumnNumber = 2    ' column number
    '-------------------------------------------------------------
    '- loop through each row  (which may be only 1)
    For ToRow = StartRow To LastRow
        MyValue = ToSheet.Cells(ToRow, ActiveColumn).Value
        FindValue
    Next
    '-------------------------------------------------------------
    '- finish
    MsgBox ("Done")
    Application.Calculation = xlCalculationAutomatic
End Sub
'== END OF PROCEDURE ====================================================

'========================================================================
    '- FIND VALUE
'========================================================================
Private Sub FindValue()
    Set FoundCell = _
        FromSheet.Columns(LookupColumn).Find(MyValue, LookIn:=xlValues)
    If FoundCell Is Nothing Then
        MsgBox (MyValue & " not found.")
    Else
        FromRow = FoundCell.Row
        '- transfer additional data.
        ToSheet.Cells(ToRow, ReturnColumnNumber).Value = _
            FromSheet.Cells(FromRow, FromColumn).Value
    End If
End Sub
'=========================================================================
******************************************************
Code:
'==================================================================== 
'- MACRO TO FIND A VALUE AND PUT RESULTS INTO A USERFORM LISTBOX 
'- THIS ListBox HAS 3 COLUMNS 
'- Put subroutine into a normal sub xx () with code "UserForm1.Show" 
'- Brian Baulsom May 2006 
'==================================================================== 
'- THIS CODE SHOULD GO INTO A USERFORM MODULE 
'- The userform requires 
'-     1. TextBox1 for find value entry. 
'-     2. ListBox1. There is code below to set column count & widths. 
'-     3. CheckBox1 to define exact or partial match 
'-     4. Label1 to show number of items found 
'-     5. CommandButton1 to run the macro. 
'-     6. CommandButton to exit and unload the form 
'==================================================================== 

'========================================================================= 
'- MAIN ROUTINE 
'========================================================================= 
Private Sub CommandButton1_Click() 
    Dim MyInput As Variant 
    Dim FoundRow As Long 
    Dim ListEndRow As Integer 
    Dim ws As Worksheet 
    Dim FoundCell As Object 
    Dim LastRow As Long 
    '--------------------------------------------------------------------- 
    Set ws = ActiveSheet 
    LastRow = ws.Range("A65536").End(xlUp).Row 
    ListBox1.Clear 'clear the listbox 
    ListEndRow = 0 
    '---------------------------------------------------------------------- 
    '- SET LISTBOX COLUMN COUNT & WIDTHS in Points (=1/72 inch) 
    ListBox1.ColumnCount = 3 
    ListBox1.ColumnWidths = "20;40;40" 
    '---------------------------------------------------------------------- 
    '- input 
    '- convert to correct data type 
    '- may not really be necessary, but to be safer .... 
    MyInput = Me.TextBox1.Text   ' NB. Textbox output is always text 
    If IsNumeric(MyInput) Then 
        MyInput = CDbl(MyInput) 
    Else 
        MyInput = CStr(MyInput) 
    End If 
    '----------------------------------------------------------------------- 
    '- LOOK FOR VALUES IN COLUMN A down to last row containing data 
    With ws.Range("A1:A" & LastRow) 
        '------------------------------------------------------------------- 
        '- EXACT OR PARTIAL MATCH FROM CHECKBOX 
        If CheckBox1.Value = True Then 
            Set FoundCell = .Find(MyInput, LookIn:=xlValues, lookat:=xlWhole) 
        Else 
            Set FoundCell = .Find(MyInput, LookIn:=xlValues, lookat:=xlPart) 
        End If 
        '------------------------------------------------------------------ 
        '- FIND 
        If FoundCell Is Nothing Then 
                ListBox1.ColumnWidths = "50;0;0" 
                ListBox1.AddItem 
                ListBox1.List(ListEndRow, 0) = "No Match Found" 
        Else 
            FirstAddress = FoundCell.Address 
            Do 
                FoundRow = FoundCell.Row 
                ListBox1.AddItem 
                ListBox1.List(ListEndRow, 0) = ws.Cells(FoundRow, 1).Value 
                ListBox1.List(ListEndRow, 1) = ws.Cells(FoundRow, 2).Value 
                ListBox1.List(ListEndRow, 2) = ws.Cells(FoundRow, 3).Value 
                ListEndRow = ListEndRow + 1 
                Set FoundCell = .FindNext(FoundCell) 
            Loop While Not FoundCell Is Nothing And FoundCell.Address  FirstAddress 
        End If 
    End With 
    Label1.Caption = "Found " & vbCr & ListEndRow & " match" & IIf(ListEndRow = 1, "", "es") 
    TextBox1.SetFocus 
    SendKeys "{HOME}" & "+{END}"    ' to select textbox contents 
End Sub 
'------------------------------------------------------------------------------ 
Private Sub CommandButton2_Click() 
    Unload Me 
End Sub 
'------------------------------------------------------------------------------ 
Private Sub UserForm_Initialize() 
    TextBox1.SetFocus 
End Sub 
'------------------------------------------------------------------------------


Try activating the sheets first:

Worksheets("FTREGI_FUNDS_MOVE").Select
Range("E2").Select
Selection.Copy
Worksheets("SEIACHDisbursement").Select
Range("A2").Select
ActiveSheet.Paste

>-----Original Message-----
>Hello
>
>I created a macro in excel 97 that adds new worksheets
within a
>workbook and copies and pastes specific cell values from
>one cell in sheet1 to another cell in sheet2. When I
ran the macro, I
>received an error indicating "the object
>doesn't support this property or method - run time error
438."
>
>Any suggestions would be appreciated.
>Thanks in advance
>Jung
>
>
>Here is the code with the error:
>
>Sub AddNewWorksheets()
>
>Worksheets.Add
>Sheets.Add.Name = "SEITransfer"
>Sheets.Add.Name = "SEIWire"
>Sheets.Add.Name = "SEIACHReceipt"
>Sheets.Add.Name = "SEIACHDisbursement"
>
>End Sub
>
>Sub FundsMovement()
>
>Application.ScreenUpdating = False
>
>
>ActiveSheet.Name = "SEIACHDisbursement"
>Range("A1").Value = "FromAcct"
>Range("B1").Value = "FromIncome"
>Range("C1").Value = "FromPrincipal"
>Range("D1").Value = "DisbursementCode"
>Range("E1").Value = "DisbursementExplanation1"
>Range("F1").Value = "DisbursementExplanation2"
>Range("G1").Value = "DisbursementExplanation3"
>Range("H1").Value = "DisbursementExplanation4"
>Range("I1").Value = "DisbursementExplanation5"
>Range("J1").Value = "Taxid"
>Range("K1").Value = "ToENeeded"
>Range("L1").Value = "CUSIP"
>
>'copy and paste specific cells from ftregi_funds_move
worksheet
>(sheet1)
>'to SEIACHDisbursements(sheet2)
> Worksheets("FTREGI_FUNDS_MOVE").Range("E2").Copy
> Worksheets("SEIACHDisbursement").Range
("A2").Paste '(error on this
>line - the object
>doesn't support this property or method - run time error
438.)
>
>Application.ScreenUpdating = True
>
>End Sub
>.
>

I'm trying to write a macro that carries the cell values from one worksheet to another. It appears that since the values are placed on a different row in the new worksheet, they're not lining up properly. For example, the cell value from cell A2 in the 'workspace' sheet should be placed in cell C27 (a merged cell) in the 'agenda' worksheet. Also, additional rows may be added to the 'agenda' worksheet, so I've named the cells where the values from the 'workspace' template should start being placed (i.e. "activity" is cell C27, "page" is cell F27, etc.). When I write the macro to say Range("activity" & j) so that it copies the values from all rows, I get a range of object error. Nothing else I've tried works, Can you please help? Here's the macro and the file is attached. Thanks!

This macro places only the last row from the 'workspace' sheet into the 'agenda' sheet. I need for the values in all rows in the 'workspace' sheet to be placed in the 'agenda' sheet, starting with row 27. (Note: Copying the rows from the 'workspace' sheet will not work because I need to preserve the bold formatting in some of the cells in column C in the 'workspace' sheet.)

Sub test()

      lastrow = Worksheets("workspace").Range("A" & Rows.Count).End(xlUp).Row
     For j = 2 To lastrow
             
    Worksheets("agenda").Select
     Range("activity") = Worksheets("workspace").Range("A" & j).Value
     Range("page") = Worksheets("workspace").Range("B" & j).Value
     Range("outcome") = Worksheets("workspace").Range("C" & j).Value
     Range("party") = Worksheets("workspace").Range("D" & j).Value
     Range("time") = Worksheets("workspace").Range("E" & j).Value
                    
    For i = 1 To Worksheets("workspace").Range("C" ).Characters.Count
            Worksheets("agenda").Range("outcome").Characters(i, 1).Font.FontStyle =
Worksheets("workspace").Range("C" & j).Characters(i, 1).Font.FontStyle
            
    Next i
    Next j
           
End Sub
Tammy

All right, I'm using an INDEX/MATCH function to look up two values from a worksheet and match them BOTH in a record on named table from another worksheet in the same book and return the value from a cell in the applicable record from said table. Below is the formula:

=INDEX(LaneSummary,MATCH(A4,Origin,0)&MATCH(B4,Dest,0),3)

It works, as in it returns data, but it is evidently matching A4 and B4 to the wrong record on the Origin and Dest tables and returning the WRONG data. Help??

I am currently needing to link both the values and formats of cells from one worksheet to another. I don't think i can use conditional formatting, I have several cells that need to be blue, and others that need to be black. These cells will change from blue to black after they are approved. I have one sheet that is for working with (has several formats, to make it easy to find what is needed) and the other is to be clean and neat for printing. I was needing to have the color in the cells change on the clean sheet as the colors change on the working sheet. Is there a way I can do this through VBA? I also need to password protect the clean sheet.

Hello,

I have the following problem with which I need some help:

I have two different worksheets with article numbers and quantity data. One
of these includes the whole set of articles, while the other holds only a
selection of articles. I need to compare the article numbers and extract a
value from another column in the first worksheet when these article numbers
match. E.g.:

worksheet 1
article number quantity
12345 12
98765

worksheet 2
article number
12345

I want to extract the quantity number from worksheet 1 into worksheet 2,
only for the matching article numbers.

Many thanks

Regards,

Ben

Hey all,

I have a simple question. I am trying to look up data from a different worksheet and display it on another. Right now, I am using the formula IF('Raw Data'!AP4="","",'Raw Data'!AP4).

'Raw Data' is the spreadsheet I am "pulling" data from and AP4 is the cell. This formula works great, however, I am trying to increment the cells by 3. I.E. - I want the spreadsheet I am pulling the data from 'Raw Data' to be incremented from AP4 to AP7 to AP10 to AP13 to AP16...etc...etc.

I was wondering if there was a simple fix to my problem. Thanks for any help.

Hi All,

I have a question regarding excel. I am a relative newcomer to Excel Macros and such, and am having fun, but am frustrated with the following problem:

I have a sheet titled "To Do List" with 10 columns - the relevant columns for this problem are B and C. Column B is titled Client Name and column C is titled Client No. I have a separate worksheet (same book) titled "Sheet1." My data begins in Row 6 of To Do List (i.e. first cell I use for client name is B6). Further, I have an autofilter drop down list in row 5 of each column so I can sort ascending/descending by client name, number, or values in other columns. I am trying to get a function or a macro which will automatically fill in the client number (column C) when I type in the client name (B).

So far, I have the following function in column C which works for autofilling column C values based on values from Sheet1:

=IF(ISNA(VLOOKUP($B6,Sheet1!A:B,2,FALSE)),"",VLOOKUP('To Do List'!B6,Sheet1!A:B,2,FALSE))

This should be easy, however, here are the issues I encounter:

1. When I use autofilter drop down in Column B and sort ascending or descending, column C does NOT sort along with B (or the other columns).

2. I would LOVE to have this function or macro use approximate values for column B, or, in the alternative, to autofill based on values in Sheet1. In other words, if I have client name Adams, John D. in Sheet1, I would like for Autofill to pull that up the first time I type it into B in To Do List, as opposed to me having to type it in once and then autofill recognizes it, if this is possible (there are over 1300 client names and I may be adding new ones to just the client list (sheet1) so it'd be helpful for To Do List to automatically recognize them and autofill in their appropriate numbers.)) For example, 15A is GTH Owners Corporation. I'd like to enter GTH in To Do list and have the macro or function recognize this immediately. NB - when I use TRUE in the above function, it does not work in any meaningful way.

Problem 1 is my main concern. I can live with the "inconvenience" of having to type in a full client name one time, if filling all the above wants is a pain!

Thanks in advance for the support and help.

I am attaching my To Do List, with the appropriate sheets, for a clear understanding of what I am talking about (albeit with most client removed and with some name changes).

P.S. I already have a Macro on the sheet which automatically moves anything in Column A marked Done to the Done sheet (see file)

TY0036.XLS

How do I set up a spreadsheet with info and auto update it from another
worksheet.
I am wanting to make a worksheet but only send out part of the info to a
client not all but be able to autoupdate it reguarly.