Free Microsoft Excel 2013 Quick Reference

Copy Cell Value from one Worksheet to another based on selected criteria

I know how to create things to have the user input data and based on that input, select return data in access; but I am drawing a blank when it comes to excel so I'll try to explain it the best that I can. Just need something simple and I am new when dealing with excel.

Working with Several workbooks:

Workbook 1-47 are individual books where certain cell values in those individual workbooks are populated from a the separate COSTWorkbook.

1) I want to link each individual Workbook to the COSTWorkbook based on a contract #. (Easy enough)

2) when the user opens the individual WORKBOOK, I want them to be able to select a Month (JAN, FEB) from a drop down list (easy) but I also need that selected month to be linked to a column in the COSTWorkbook. Once the month is selected then the corresponding cells from the Costworkbook are populated in the individual workbook. (hope you are following because I got lost for a minute).

Example: Workbook 3 is open (Contract # 12345 which is linked) and user selects the month MAR, then the data in COSTWorkbook for that row (C3, D3, E3, F3) is used to populate cells H26, H30, H34 and H38 in the open Workbook3.

Is this easy or am I thinking way too logically?

Thanks


Post your answer or comment

comments powered by Disqus
Hii,

I want to copy a cell value from one workbook to another workbook using VBA codes. For example, in Source Book.xls the values of C4 & C5, I would like to copy into the last raw of the Destination Book.xls in respective column ( B2 & C2) when I click on some Command Button in Source Book. Again at the same time I do not want the Destination Book.xls to be opened. Is it possible if we give the file path of the Destination Book.xls in VBA code of Source Book & copy the specific value in concern cells ?

I have tried to Record Macros but could not succeed.

H Shah

Hello,

This is my first post to this forum (be kind! ) and I am relatively new to VBA and need some help. I am currently using VBA to copy specific data from one worksheet to another, but would like to change it to copy the same specific data from one workBOOK to another. I would also like to add a parameter that would only copy data entered on today's date. My workbooks are named "PM-Test" (source) and "Changes"(destination).I really hope someone can help me! The code I am using currently to copy from one sheet to another follows. Thanks in advance for your assistance!
Sub Copy1to2()
    
    Dim InSH As Worksheet
    Dim OutSH As Worksheet
    Dim i As Long
   
    Set InSH = Sheets("LOG SHEET")
    Set OutSH = Sheets("CLIENT REVIEW")
   
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    OutSH.Range("A2:Y30000").ClearContents
    
    With Intersect(InSH.UsedRange, InSH.Columns("f"))
       .AutoFilter 1, "=Revision-DRG"
        .Offset(1).EntireRow.Copy
        OutSH.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        .AutoFilter
    End With
    
    Application.CutCopyMode = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub


Hi all,

I'm curious if it's possible to copy data from one TAB into another based on
certain criteria.

For example: if you have a spreadsheet with customers account numbers,
geographical location and a date to contact them. Would it be possible for a
formula to, based on todays date, go check what dates to contact a customer
is coming up this week, split the result up by geographical location, and
onto different TABs? The result should only include customers who needed to
be contacted within the workweek.

Any ideas or suggestions?

Thanks,
Tom

I need a way to automatically to copy cell values as they are being entered from one worksheet to another. Ideally, we would being keying off of a string in the column header of the originating worksheet to determine which column on the second worksheet that the cell gets copied to. Thanks.

I need a formula to copy rows of cells from one sheet to another based on a
specific cell name value (North, Central, or South). I will have a master
sheet containing information from three teams, North, Central, and South, and
three other sheets individually named. I want to move the row data to it's
related sheet based on these names. The names will be in the same column, "M".

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

Hello: I am not a VBA programmer but I have gathered enough info to accomplish most of what I set out to, Much thanks to this forum. I am still having an issue with one item. When I try to copy a value from one worksheet to another it is rounding off the number to 2 decimal places rather than 4. eg shows up on sheet 3 as $13.4100 rather than $13.4135.
Both cells are formatted to display currency to 4 decimal places.
The value being copied from sheet one is calculated using a formula
=IF(F31>0,M31/L31,IF(AND(G31>0,E31>0),K30,IF(AND(G31>0,E31=0),M31/L31,"")))

and the code used to transfer the info is:
Private Sub CommandButton2_Click() 'Tax Form Button - captures all sales for year

Sheet3.Activate

ActiveSheet.Unprotect
   

Dim lastrow3 As Long
lastrow3 = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row

If Cells(4, "A") = "" Then
Else
Sheets("Sheet3").Range(Cells(4, "A"), Cells(lastrow3, "H")).Select
Selection = ""

Sheets("Sheet3").Range(Cells(lastrow3, "B"), Cells(lastrow3, "H")).Select
    
     With Selection.Borders(xlEdgeTop)
        .LineStyle = none
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = none
    End With
End If



Sheet1.Activate
Dim i As Integer, rng As Range
Dim lastrow As Long, nextrow As Long
Dim Title As String
Title = ("MY WESTJET SHARES - TAX REPORT FOR " & TextBox1.Text)
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
nextrow = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row + 1

Set rng = Sheets("Sheet1").Range("T4:T" & lastrow)

    For i = 4 To lastrow
    
            If Cells(i, "T").Value = TextBox1.Text And Cells(i, "C").Value = "Sell" Then
            
            'Range(Cells(i, "A"), Cells(i, "L")).Copy
Destination:=Sheets("Sheet3").Cells(nextrow, "A")
            
            Sheets("Sheet3").Cells(nextrow, "A").Value = Range(Cells(i, "A"), Cells(i,
"A")).Value 'Date
            Sheets("Sheet3").Cells(nextrow, "B").Value = Range(Cells(i, "E"), Cells(i,
"E")).Value 'Share Price
            Sheets("Sheet3").Cells(nextrow, "C").Value = Range(Cells(i, "G"), Cells(i,
"G")).Value '#Shares Sold
            Sheets("Sheet3").Cells(nextrow, "D").Value = Range(Cells(i, "I"), Cells(i,
"I")).Value 'Price Sold For
            Sheets("Sheet3").Cells(nextrow, "E").Value = Range(Cells(i, "K"), Cells(i,
"K")).Value 'ACB/Share
            Sheets("Sheet3").Cells(nextrow, "G").Value = Range(Cells(i, "J"), Cells(i,
"J")).Value 'Capital Gain/Loss
            Sheets("Sheet3").Cells(nextrow, "H").Value = Range(Cells(i, "D"), Cells(i,
"D")).Value 'Sales Fee
            Sheets("Sheet3").Cells(nextrow, "I").Value = Range(Cells(i, "P"), Cells(i,
"P")).Value 'Date 1st
            Sheets("Sheet3").Cells(nextrow, "J").Value = Range(Cells(i, "Q"), Cells(i,
"Q")).Value 'Date last
            
            'Sheets("Sheet3").Cells(nextrow, "I").Value = Range(Cells(i, "I"), Cells(i,
"L")).Value
            'Sheets("Sheet3").Cells(nextrow, "J").Value = Range(Cells(i, "J"), Cells(i,
"L")).Value
            'Sheets("Sheet3").Cells(nextrow, "K").Value = Range(Cells(i, "K"), Cells(i,
"L")).Value
            
            'Sheets("Sheet3").Cells(nextrow, "A").Resize(1, 12).Value = Range(Cells(i, "A"),
Cells(i, "L")).Value
            
           
            
            nextrow = nextrow + 1
        End If
    Next i

Sheet3.Activate


    
    Dim EndRow As Long
    EndRow = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row
    Cells(EndRow + 1, 1).Value = "Totals"
    
    
    Dim r As Long

r = Cells(Rows.Count, "B").End(xlUp).Row

Range("F2").Copy
Range("F2").AutoFill Destination:=Range("F2", ("F4:F" & EndRow))


Cells(3, "F").Value = "Adj. Cost Base"
    
    
    Cells(EndRow + 1, 3).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(EndRow + 1, 4).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(EndRow + 1, 6).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(EndRow + 1, 7).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(EndRow + 1, 8).FormulaR1C1 = "=SUM(R1C:R" & EndRow & "C)"
    Cells(1, 5).Value = Title
    
    'Range("A" & Rows.Count).End(xlUp).Select
    Sheets("Sheet3").Range(Cells(nextrow, "B"), Cells(nextrow, "H")).Select
    
     With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = xlAutomatic
        .Weight = xlThick
    End With

Range("A" & Rows.Count).End(xlUp).Select 'Selects the last cell in column A (used to deselect previous
selection)
'Unload Me

   
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

REPORTS.CommandButton3.Visible = True

End Sub
Sheets("Sheet3").Cells(nextrow, "E").Value = Range(Cells(i, "K"), Cells(i, "K")).Value 'ACB/Share This is the line giving me issues.

On sheet 1 the value is displayed to 4 decimal points but on sheet 3 there are 4 decimal places but the last two are both zero's
Any Ideas on why this is happening

hello friends,
i want to copy only values from one worksheet to another in particular cells
i have programmed it somehow but not for values...it just copy everything
code:

Sub grade1()
Dim row As Integer
Dim col As Integer
Dim i As Integer, n As Integer
Dim scol As Integer
Static srow As Integer
Dim count As Integer
Dim sheetno As Integer

sheetno = 1
srow = 2
row = 7
col = 3
Sheets("A1").Activate
n = InputBox("no. of student?")
For i = 1 To n
sheetno = 1
count = 1
Do While count <> 13
scol = 3
For k = col To 24
Sheets(sheetno).Cells(srow, scol).Copy Cells(row, k) <----here
scol = scol + 1
Next k
sheetno = sheetno + 1
count = count + 1
row = row + 1
Loop
row = row + 9
srow = srow + 1
Next i
End Sub


Hi,
i am currently trying to copy values from 2 seperate worksheet into one common worksheet. I have only been able to copy values from one worksheet to another but am having problem with the other worksheet. Below shows part of the code which i did. What i need now is to find the last non-empty row of Worksheet "LA" and Copy all values from Worksheet "Mod2-CLD" column A, D, K into Worksheet "LA" column A, B, C respectively. How should i go about approaching the problem?
Code:
    ActiveWorkbook.Sheets.Add.Name = "LA"
    Set insource1 = Sheets("Mod1-CLD").Columns("H:H") 'event type
    Set insource2 = Sheets("Mod1-CLD").Columns("F:F") 'business line
    Set insource3 = Sheets("Mod1-CLD").Columns("K:K") 'severity
    Set indest1 = Sheets("LA").Columns("A")
    Set indest2 = Sheets("LA").Columns("B")
    Set indest3 = Sheets("LA").Columns("C")
    insource1.Copy indest1
    insource2.Copy indest2
    insource3.Copy indest3
Thank you.

Regards,
Grace

Simple question guys!

I have forms set up and I need a workbook to automatic copy a value from one sheet to another for me. For example, when I enter 1155EFG in B3 of sheet1, I need it to enter that into cell d6 of sheet 2 for me...

this possible? how?

Thanks guys! As always, I appreciate you help!

I need to conditionally reference rows of cells from one worksheet to another. Specificially, I want to copy certain rows from one worksheet to another ONLY IF there is a "Y" or a "?" in a certain column. I want to leave any rows that have an "N" in the column, so in all, I have three variables: "Y & ?" should trigger the reference copy and "N" should leave the data on the main worksheet.
I am able to copy cells presently from one worksheet to another using the simple "=" function. I just would really like to do this conditionally so I get only the rows that I need.
Any help would be tremendously appreciated! Thanks!

I need to mirror individual cells from one worksheet to another but each transaction has a unique sequence number so the cell should mirror the right transaction based on its Seq. number

I've attached a file with two worksheets. The MasterData in one and a report extracted from the MasterData. I would like to mirror the comments field so each time a user input data in the comments field it mirrors the info to the same transaction in the masterdata.

I really appreciate all the help you can provide.

Thank-you,
Jarrinson

Hi,

I have a query about creating a macro to move certain figures from one column to another.

Look down column F and G for negative value. If a negative value is found then I would like this to be moved into the opposite column as a positive and removed from the first column.

So, for example. If F6 was -100 then I would like G6 to be 100 and F6 to be made blank.

I would need this macro to run from Row 4 to the bottom of the data, which, I have declared a variable as lastrow.

Any ideas on this guys?
Thanks

I am looking to copy data from one sheet to another based on the value in a to columns. If column "N" contains "WDWO" and if "I" is less than "600" then I need to move three fields in that row to a different sheet. the fields that need to be moved are in columns a, e, j. I have attached a sample of the sheets before and after. My original sheet is sheet 1 and my filtered sheet is sheet 2. As always thanks ahead for the help and it is much appreciated! And I need this in a macro so I can automate this process because I have to do this with 8 different sheets.

Hi there,

I am trying to transfer a set of data from one sheet to another based on 2 conditions.

The set of data goes from cell b5:b10.

The first condition is that cell b2 will set the worksheet that the data will be transferred to. e.g. if cell b2 says 'worksheet 1' then the data will be transferred to 'worksheet 1' if it says 'cats' then the data will be transferred to the worksheet titled 'cats.'

The second condition is that cell b3 sets the column that the data will be transferred to, e.g. if b3 says 'July' then the data will be transferred under the 'July' heading in the exact same order that it is originally found. The months Jan-December are horizontally listed across the target worksheet.

Can someone please tell me if this is possible? I would be very grateful for any help regarding this matter.

Thanks,

Jacob

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

I would like to be able to copy a number of different cells in a specefic row
from one worksheet to another (same document).

The criteria would be similar to the following: if A1 has a value and A2
does not have a value, copy cells A1, A3, A4, A5 from Worksheet 1 into the
next avaliable row in Worksheet 2.

Does that make any sense? Help would be much appreciated.

I have a workbook that is basically a template. I did get a macro working that brought over the range of cells from one worksheet to another, however, it came down to that it was inserting on the same row each time and pushing data down. I'd rather have my data pasted to the next empty row. Here is what I have, I imagine I need to edit the last few lines to say how to find the empty row and to paste? That is where I am stuck. I found several similar references in the forums, and tried to follow, but have not had success.


	VB:
	
 
Private Sub CommandButton1_Click() 
    insert_test_plan 
End Sub 
_____________________________________________________________ 
Sub insert_test_plan() 
     '
     ' insert_test_plan Macro
     '
     ' Keyboard Shortcut: Ctrl+t
     '
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast 
    Sheets("Testcopy").Select 
    Range("A1:K10").Select 
    Selection.Copy 
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst 
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst 
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst 
    Sheets("Test Plan").Select 
    Rows("17:17").Select 
    Range("L17").Activate 
    Selection.Insert Shift:=xlDown 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi all,

I am in desparate need of a macro that automatically copies the values from one file to another. Sounds simple?

Well, not quite.. Let me give you an example..

I have 2 files:
- MASTER FILE
- OLD VALUES FILE

My Master file contains some cells in which numbers are manually inserted, let us say, C2:C200. The cells are currently blank.

The OLD VALUES file contains the same cells, also with numbers manually input (and actually contains numbers).

Instead of simply linking the files to each other, I would like a macro that copies all numbers from one file to the other (paste special - values).

The user should be prompted to locate the file to copy from (OLD VALUES FILE), as this might be located somewhere in another folder.

Think of this whole thing as an update procedure. My master file sometimes changes, but I don't want to manuylly Re-Input all the figures from the old file to the new file. Therefore I would need this routine to manually copy all values in a certain range from one file another, with the user being promted to locate this file.

Does anybody have a clue how I can do this? Especially the prompting of the user to locate the file...

Thanks in advance for your help!!!!

Ciao, Titus.

I have C:MY DOCUMENTSTARGET.XLS opened to a worksheet called RESULTS. The
cell highlight is residing in B90.

There's another file C:MY DOCUMENTSSOURCE.XLS that also has a worksheet
called RESULTS.

Without opening SOURCE.XLS, I want to copy the cell formats from B90:G175
from SOURCE to TARGET. (How) Can I do that in a macro without opening
SOURCE.XLS?

I found something in Excel Help about copying a formula from one workbook to
another but that's not quite what I'm looking for. Thanks, in advance, for
any assistance.

I am a novice at Excel programming.

This will be really simple.

How do you define the syntax to copy a cell reference from one worksheet to a different cell reference in another worksheet.

This did not work for me:

Worksheets("Sheet1").Activate
Range("A1").Copy Worksheets("Sheet2").Range("C5")

Thanks.

This relates to a question I posted earlier about returning a blank value in a cell. What prompted that question is from my real question.
Here is a challenge I could use help with:
1. I want to place a value in a cell dependant upon the following.
2. First is to identify if there is text in an identifying cell.
3. If there is no text value, in the cell from this column, the return value should leave a blank cell.
4. If there is text in the identifying cell then the formula would look in seven other cells for a value and then return that one value.
5. What I am doing is placing amounts from one worksheet to another only if there is a text value in this identifying cell
6. The seven cells, which are being scanned, will only have a value in one cell, of the seven, and the new worksheet is to insert that value.

Here is the example:
Cells that will be used in the forumla will be: C1, E1, G1, I1, K1, M1, O1 and the identifying cell V1
The formula will look to see if there is a value in V1
If no the return value is blank
If yes, the formula will look at C1, E1, G1, I1, K1, M1 and O1.
Only one of these cells will have an amount and the return value will be that amount.
If this is all too confusing, I will eventually figure it out, but thought I would use the expertise that seems to reside on this forum.

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.

I have 2 files that have 2 data fields in common. I need to match both fields from one worksheet to another worksheet and return a 3rd value back to the other worksheet. Only with both data fields will I have one correct returned value. Here is an example set. I know I can use VLookup if I only need to match one value but I have to match two. Any help is appreciated. I feel like this should be easy but for some reason I just can't figure out tonight.

Thanks!

Worksheet 1 A B C D Code File # Name IUZ 123456 Joe Adams--This is the result field where I need to put the formual Worksheet 2 A B C D File # Code Name IUZ 345762 Billy Bob 4TZ 123456 Freddy Boy IUZ 123456 Joe Adams


No luck finding an answer? You could always try Google.