Macro to copy and paste certain information according to a cell entry

Hi. I am new to macros and to using VBA so i apologise for my ignorance on the matter. I would like to create a macro to copy and paste depending on the entry of a cell on another worksheet. I would like information to be pasted to the next blank cell + 1(+1 so there is a gap between each entry).

I have a list of names on "Sheet1" column A. If Column B = y next to the name, indicating that name was used, then i would like to copy and paste certain information(eg address, phone number) from "Sheet2" and paste it on "Sheet3". However, it is only information from certain cells that is needed so using a range (eg A1:P7) wouldn't work because certain cells in the middle are not wanted. Is it possible to just select certain cells in a range? I can't remove any of the cells as formulas are used. As there are a number of names and only some of them are used i would like to be able to paste in the next blank cell + 1.

I hope i made some sense and would be grateful for any help. Thank you very much.

Dawn


Hi, I need some help on creating a macro to copy and paste the value of a row on 1 worksheet (Open) to another worksheet (Closed), in the same workbook. Once "closed" is entered in column Q the macro should occur. The value of the "closed" row should be pasted to the end of the list in the Closed worksheet, the the row in the Open worksheet shoud then be deleted.

Need Help.xlsm

I have a macro that will copy and paste certain columns, to a new sheet.
It works in some workbooks, but it will not keep the values once they have been pasted into a new sheet in workbooks with filter in place. Problem I am having is that I need to filter data I have and then copy and paste that data into a new sheet. Is there a way to keep the filter in and make this macro work or is there a code I can use to run the filter when running macro for a specific word than copy and paste rows/columns that include that specific word?

	VB:
	
 NewTab() 
    Dim sName As String 
     
    sName = Application.InputBox("Enter the new sheet name:", Title:="New Sheet Title", Type:=2) 
    If sName = "" Then Exit Sub 
     
    ActiveWorkbook.Sheets.Add After:=Sheets(Sheets.Count) 
    Sheets(Sheets.Count).Name = sName 
     
    Sheets("PriceSheets-ItemList").Range("A:A").Copy 
    With Sheets(sName).Range("A1") 
        .PasteSpecial xlValues 
        .PasteSpecial xlFormats 
        .PasteSpecial xlPasteValidation 
    End With 
     
    Sheets("PriceSheets-ItemList").Range("J:J").Copy 
    With Sheets(sName).Range("B1") 
        .PasteSpecial xlValues 
        .PasteSpecial xlFormats 
        .PasteSpecial xlPasteValidation 
    End With 
     
    Sheets("PriceSheets-ItemList").Range("O:P").Copy 
    With Sheets(sName).Range("C1") 
        .PasteSpecial xlValues 
        .PasteSpecial xlFormats 
        .PasteSpecial xlPasteValidation 
    End With 
    Sheets("PriceSheets-ItemList").Range("AC:BK").Copy 
    With Sheets(sName).Range("D1") 
        .PasteSpecial xlValues 
        .PasteSpecial xlFormats 
        .PasteSpecial xlPasteValidation 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I keep getting an error saying that pastespecial xlvalues for Range("D1") cannot be run for some reason.

I have a macro that copies and pastes certain rows if they include the word "Period Hours" to another worksheet (Totals). My problem is that I need it to paste values only not the formula.

Sub Copyx()
Dim RngColF As Range
Dim i As Range
Dim Dest As Range
Sheets("Full").Select
Set RngColF = Range("C1", Range("C" & Rows.Count).End(xlUp))
With Sheets("Totals")
Set Dest = .Range("A1")
End With
For Each i In RngColF
If i.Value = "Period Hours" Then
i.EntireRow.Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub


Hi,

I need a macro to copy and paste cells from one worksheet to another, if there is the word true in the column L of that row.

Can anybody help?

Thanks in advanced!

I'm looking for a way to copy and paste the data in one cell (a1 for
example) to another workbook. But it's not as simple as that. I have
over 15 users each with their own spreadsheet that will have one cell
dedicated to their name. As users are added though I want the user's
name and the name of their workbook to be parsed into column A1, one
after the other. Thanks again in advance for any help. This is my
second question today already, any help would be greatly appreciated.

--
spirosu
------------------------------------------------------------------------
spirosu's Profile: http://www.excelforum.com/member.php...fo&userid=7442
View this thread: http://www.excelforum.com/showthread...hreadid=522393

I would like to copy and paste two columns from a worksheet to another worksheet simultaneously.

I would like to have a macro to do this function.

Thanks a lot for your time!

Taniks

I'm looking for a way to copy and paste the data in one cell (a1 for example) to another workbook. But it's not as simple as that. I have over 15 users each with their own spreadsheet that will have one cell dedicated to their name. As users are added though I want the user's name and the name of their workbook to be parsed into column A1, one after the other. Thanks again in advance for any help. This is my second question today already, any help would be greatly appreciated.

Hi,
I am trying to copy and paste some information from a website, into excel. Information like
1234, Lex Street,
345, New York,
New York City, 10022.

I wanted to paste the info into excel in the same cell, but for some reason the info goes into separate cell in the same column. Could somebody help me out here?
Thank you so much!

I am working on spreasheet with 9000 lines. I need a shortcut to copy and
paste same formula in all cells.

Hi Everyone,

I am new to macros and I normally only record them. I am trying to copy and select a variable range according to IDs in first column.
My basic objective is to copy the range associated to a person and then paste it somewhereelse. every ID has a variable range of information associated with them.
Can someone suggest a macro code to go through more than thousands of ID and copy the range associated with it and be able to paste it to any one particular area of a new sheet.
here is an example of what I would like to achieve.
A B C
1 1234 xxxx yyyy aaa@gmail.com
2 1234 xxxx yyyy
3 1234 xxxx yyyy
4 1234 xxxx yyyy
5 1234 xxxx yyyy
6 5678 zzzz aaaa bbb@gmail.com
7 5678 zzzz aaaa
8 5678 zzzz aaaa
9 5678 zzzz aaaa
10 5678 zzzz aaaa
11 5678 zzzz aaaa
12 5678 zzzz aaaa
13 5678 zzzz aaaa
14 5678 zzzz aaaa

I would like the macro to be able to copy cells b1:c5 and then paste it as a body of a message to the first email address and send it then copy and paste range b6:c14 as a body of a message to the second email address and send it..
so far I have been able to find codes that help me send info in every cell to their associated email address, I just need to be able to select and paste a variable range to the message of the body to complete the code:
Sub Create_Mail_From_List()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
       
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Reminder"
                .Body = Cells(cell.Row, "A").Value                                
                .Send  'Or use Display.
            End With
            On Error GoTo 0
            Set OutMail = Nothing
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub


I have a forms combo box /dropdown that I use the index function with to read 150 rows each containing 8 columns from another worksheet. It then shows the selected cells on the worksheet that contains the dropdown.
What I want to be able to do is to use the combo box and when I make the selection instead of using the index function to show the data in the cell, I want to copy and paste the data into the cell.
I believe this will require the use of a macro to take the information from the dropdown and then copy and paste it into the correct locations.
I am open to any way of doing this as long as the data is copied and not just linked.
Any help would be greatly appreaciated.

Hello,

Do anyone know how to write a macro so I can copy and paste certain
information and then for the next month do the next column over?

Ex.

book1
A B C D E
1 Acct. 1 10 0 0 0
2 Acct. 2 20 0 0 0
3 Acct. 3 30 0 0 0
4 Total =SUM(B1:B3) =SUM(C1:C3) =SUM(D13) =SUM(E1:E3)

book2
A B
1 20 30
2 30 40
3 40 50
4 90 120

book3
A
1 50
2 30
3 60
4 140

I would like to copy Book2 column A 1-3 to Book1 Column C 1-3.
1month later in time, I would like to copy Book2 Column B1-3 to Book1
Column D 1-3
2months later in time, I would like to copy Book3 Column A1-3 to Book1
Column E 1-3

Does anyone know a good way to do this and a way to update it every
month?
I can do it the first time, but how do I update?

Sub copypaste()
'
' copypaste Macro
' Macro recorded 10/13/2006 by HFP73L
'
' Keyboard Shortcut: Ctrl+q
'
Windows("Book2").Activate
Range("A1:A3").Select
Selection.copy
Windows("Book1").Activate
Range("D3").Select
ActiveSheet.Paste
Windows("Book2").Activate
Range("A6:A9").Select
Application.CutCopyMode = False
Selection.copy
Windows("Book1").Activate
Range("D8").Select
ActiveSheet.Paste
End Sub

Hi,

I am trying to get a macro that copies and pastes a certain highlighted portion of my spreadsheet and then pastes its on the same worksheet. I have the data separated how I want it and would like a way to write the code so I can use the copy and paste macro for other worksheets too.

The data in the worksheet is sorted first by ID 1, then ID 2, then finally by type. I have a macro that subtotals the amount then just shows the subtotals shown by the first grouping. I would like to highlight the subtotals, copy only the visible cells then paste to D9. The macro needs to work so I can use it for the next 2 sections as well, separately. this is just a small representation of the data (there are many more types on my real file and they vary with each group).

Finally, I need to put a normal border on the pasted section of the subtotals, then a bold border around the outside. After that, I need to highlight that in light yellow.

Thank you in advance for all of your help!!

Ryan

Hey guys,

I have a workbook in which i have lots of different sheets with the same formatting, what i am trying to do is have a macro automatically copy and paste rows from these sheets to another sheet named "overdue payments" if they match a certain criteria.

The criteria is in column K with the option of "Yes" or "No". I want the sheets to copy and paste if the answer is "No"

also i would like this to automatically update when the answer is changed.

I have attached a sample workbook for you to understand what i am talking about.

I would really appreciate some help as i am now really stuck! i know it can be done but I'm just not sure how to write up the macro!!

Thanks in advance

Hi,

I am new to Macros and am trying to figure out how to create one that will copy and paste certain information into a new workbook with the click of a button. I am including a copy of the invoice I have created (It has a Macro in it that automatically updates the invoice #). For simplicity sake I have put the information that I would like to copy and paste into the second sheet of the attached workbook. I would like to click the update button and the macro copy and paste onto a new line each time I run this function. I have been able to do the copy and paste part, but it always goes onto the same line. Also is it possible to make the macro run a "SaveAs" function? Can someone either tell me how to create such a macro or provide one for me?

Thank you very much,

Hi all,

I am very new to macro thus would need your help to write VBA script on how to copy and paste the cell specific number of times.
For example, in sheet 1, i have a cell that specify no. of times to be copied. ie. 3
then in sheet 2, i have the following:
Col A
Coffee
tea
Milk

Using this macro, i will need to get:

Col A
Coffee
tea
Milk

Coffee
tea
Milk

Coffee
tea
Milk

Not sure how to do it, thanks for any assistance!

The tasks I need to do in Excel today are just are a bit more taxing than usual. Hence, the flood of questions…apologies.

I have started to write a macro to automate a component of the task I need to do repetitively for the next couple of weeks. So, it is an exercise for myself as well to learn more code for future tasks.

My “source” data is in the “Base_Data” worksheet within the Source.xlsx workbook. This worksheet has multiple columns of which some are not necessary for the analysis/macro. I would like the macro to copy the relevant data from this source data worksheet into new worksheets of Output.xlsx workbook based on the headers (these are manually copied and pasted in by myself). However, the issue is that the source data was one huge horrific spreadsheet with multiple entities in the same column. I need these entities to be split out into different worksheets in this new Output.xlsx workbook during this copying and pasting task. These entities are all in column B. For instance, there are entities such as “Table”, “Chairs”, “Stairs”… etc. in the “Base_Data” worksheet. I need these to be split out into separate “Table” and “Chairs” and “Stairs” etc. worksheets in the Output.xlsx workbook.

The necessary columns for each worksheet are to copy and paste from the source column into the target column as follows:

Source Column………….Target Column
A……………………………………….A
B……………………………………….C
C……………………………………….D
D……………………………………….E
E……………………………………….G
F……………………………………….H
G……………………………………….I
H……………………………………….J
I……………………………………….K
K……………………………………….M
M……………………………………….N
N……………………………………….O
O……………………………………….P
Q……………………………………….Q

I know this might be a difficult piece of code to write up (well, it is for me!!), and take some of your precious time, so lots of thanks for any help received but I am sure this will be of immense assistance for me in learning more VBA.

I have been trying to figure out a macros that will change a spreadsheet
from a horizontal format to the proper vertical one (my labels are located
in the first COLUMN, instead of the first ROW, with all of the data being
located in the rows to the right). "Offset" seems to be a neccessary part
of this macro, but I don't have a clue how to make it work.

I had another worksheet, where I individually copied and pasted the address
into their own set of rows (For example: A1:A7 contained the name, address,
website, etc for one customer). My macros was supposed to copy and paste
the appropriate cells, so that they would end up in cells: A1G1. At that
point, I could delete the empty row, and have a regular table. Here's what
I started doing...

HELP!!!!!!

Christine
******************************************
Sub Formatting()
'
' Formatting Macro
' Macro recorded 8/4/2005 by Christine Vitale
'
' Keyboard Shortcut: Ctrl+r
'
Range(ActiveCell.Offset(1, 0)).Select
Selection.copy
Range (ActiveCell.Offset(0, -1))
ActiveSheet.Paste
Range(ActiveCell.Offset(2, -1)).Select
Application.CutCopyMode = False
Selection.copy
Range(ActiveCell.Offset(-2, 2)).Select

*** This is where I gave up!*****
ActiveSheet.Paste
Range("A14").Select
Application.CutCopyMode = False
Selection.copy
Range("D11").Select
ActiveSheet.Paste
Range("A15").Select
Application.CutCopyMode = False
Selection.copy
Range("E11").Select
ActiveSheet.Paste
Range("A17").Select
Application.CutCopyMode = False
Selection.copy
Range("F11").Select
ActiveSheet.Paste
End Sub

I am trying to set up a spreadsheet to help track a budget. The worksheet titled "Purchased Items" will be filled out daily (and erased the next day for more orders) by those placing orders. My organization is divided up into separate cost centers with each having an allotted budget. I am wanting to track that annual budget by cost center in individual worksheets. I am trying to create a macro that can pull those rows with items purchased by a cost center to be moved to the appropriate worksheet. There are 3 types of orders that can be placed; "Regular", "Special", and "Emergency". On row 3, three different cost centers could have an order placed in each order type. I need the macro to only look at the range that meets the condition to be copied and pasted into the appropriate worksheet (e.g. cells A3:E3 need to be pasted into worksheet 6911 cells A3:E3. Each additional entry would be added to the next available row. Below is the macro I am attempting to make work:


	VB:
	
 cond_copy() 
    Sheets("Purchased Items").Select 
    RowCount = Cells(Cells.Rows.Count, "c").End(xlUp).Row 
    For i = 1 To RowCount 
        Range("c3:c20" & i).Select 
        check_value = ActiveCell 
        If check_value = "6911" Then 
            ActiveCell.Range("A3:E20").Copy 'I know that this is part of my problem, I don't want to copy and past the whole
range, only those rows that contain cost center "6911"
            Sheets("6911").Select 
            RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row 
            Range("a3:e3" & RowCount + 1).Select 
            ActiveSheet.Paste 
            Sheets("6911").Select 
        End If 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How do I limit what is copied and pasted? This macro is pasting everything into the specified worksheet if it finds "6911" in any cell.

Thanks in advance for any suggestions.

Regards,

Josh

I have a macro that copies and pastes into another sheet. When I add a row of data into my spreadsheet I want the macro to be able to change the range size and copy and paste the add row with the prior data.
Thanks

Hi Everybody,

I need a macro to copy values from sheet1 and paste them in the Sheet3
values only. Here I'm giving my specific problem.

>From Sheet1 Cells B4,N4,R4,W4,B5,&N5 which are text values to copied
and pasted in sheet3.
Then again from Sheet1 Ranges B24:B1203,c24:c1203,......upto
w24:w1203, except g24:g1203,I24:I1203,L24:L1203 these three ranges
should be excleded from the selection in B to W range, all the cells in
the range are having conditional formulas, if the condition is
satisfied it will return a numeric value if the condition is not
satisfied it will return "FALSE" or #N/A So now I need only the values
to be copied and paste special as values in Sheet3.

If some body can help for this macro it would be of a great help to me.

Thanks and Regards

Ramana

I have been tasked to find a macro that I can use to copy and paste data from one sheet to another by matching a reference from both sheets. Conditions that I need to work is that 1.) I need the data to be able to sort with associated data. 2.) The data is in Date format and must be kept so. 3.) Additional data may be entered and moved so I need to be able to do that for as far down the spread sheet as data exists.

A quick explanations of my task I have a big workbook but only going to be working with two sheets. The main sheet which is labeled Tracking will have alot of other columns labeled with data in it. The Sheet labeled Mail tracking is a smaller sheet with other data. My sample i excluded the unimportant items in both sheets. My goal it to move the ship date from the mail tracking sheet matching the package number to the Tracking sheet matching the same package number to column S on the tracking sheet. Keeping in mind the conditions noted above.

I know this may be really simple but this would be a very useful tool if i can also learn how to apply it to other different data sets. I have added an example attachment which will allow me to complete my task with out changing too much. I have limited excel experience but a general knowledge of how it works. As far as formulas and macros work very general. Any help is welcome and i will be trolling my thread till I can find a solution so ask any questions to clarify as needed.

Macro or Formula to Copy and Paste data

I have a worksheet with two tables. Table 1 is auto fed data. I would like to have a macro or formula that would take the data from Table 1 copy it (skipping the blanks) and paste it in to Table 2. I created a Macro to paste “Value” but it paste blanks over the existing data in table 2 and filled in the new data basically covering up what was already there. I would like for the data from the previous pasted rows to remain unless the data being pasted is new data. Sort of like an update.

Any assistance will be greatly appreciated.

I have attached a sample file. Tables are in Columns AD53 – AR65

Antoine

I have been trying to figure out a macros that will change a spreadsheet
from a horizontal format to the proper vertical one (my labels are located
in the first COLUMN, instead of the first ROW, with all of the data being
located in the rows to the right). "Offset" seems to be a neccessary part
of this macro, but I don't have a clue how to make it work.

I had another worksheet, where I individually copied and pasted the address
into their own set of rows (For example: A1:A7 contained the name, address,
website, etc for one customer). My macros was supposed to copy and paste
the appropriate cells, so that they would end up in cells: A1G1. At that
point, I could delete the empty row, and have a regular table. Here's what
I started doing...

HELP!!!!!!

Christine
******************************************
Sub Formatting()
'
' Formatting Macro
' Macro recorded 8/4/2005 by Christine Vitale
'
' Keyboard Shortcut: Ctrl+r
'
Range(ActiveCell.Offset(1, 0)).Select
Selection.copy
Range (ActiveCell.Offset(0, -1))
ActiveSheet.Paste
Range(ActiveCell.Offset(2, -1)).Select
Application.CutCopyMode = False
Selection.copy
Range(ActiveCell.Offset(-2, 2)).Select

*** This is where I gave up!*****
ActiveSheet.Paste
Range("A14").Select
Application.CutCopyMode = False
Selection.copy
Range("D11").Select
ActiveSheet.Paste
Range("A15").Select
Application.CutCopyMode = False
Selection.copy
Range("E11").Select
ActiveSheet.Paste
Range("A17").Select
Application.CutCopyMode = False
Selection.copy
Range("F11").Select
ActiveSheet.Paste
End Sub