Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

1 Create a macro to Copy & paste certain data to another sheet

I have no experience with macros @ all. Really need your help.
I have a spreedsheet that I sort by column A, depending on what code is in
column A, I need the data adjacent to it copied to another sheet.

Example: SHEET "OPEN"
COLUMN A COLUMN B COLUMN C COLUMN D
09 $25,000 ABC VENDOR 11/2/08
13 $55.00 XYZ VENDOR 10/19/08

IF COLUMN A ON WORKSHEET "OPEN" IS = 09 THEN I NEED EVERYTHING IN THE ROW
ADJACENT TO IT (COLUMN B THRU D) COPIED TO SHEET "SMITH". CAN ANYONE HELP ME.


Post your answer or comment

comments powered by Disqus

Related Results

  1. I need formula help or create a macro to copy and paste value only
  2. I need formula help or create a macro to copy and paste value only
  3. Create a macro to copy the value from number of excel sheets and paste it into another single excel sheet.
  4. Should I use a macro to copy and paste value?
  5. Should I use a macro to copy and paste value?
  6. Creating a macro to copy variables from different columns
  7. Help creating a macro to copy from one cell to another spreadsheet
  8. How to create a Macro to Copy Information in one sheet to another sheet.
  9. Creating a macro to copy rows from multiple sheets in Excel 2003
  10. Create a macro to copy/paste cell data
  11. Can i create a Macro to run on dynamic data??
  12. How do I create a macro to change a date format?
  13. Create a macro to format information extracted from total of pivot table
  14. Copy Data & Paste To End Of Table On Another Sheet
  15. Macro to copy and paste certain information according to a cell entry
  16. Help creating a macro to copy and paste a certain selection
  17. Need Macro to copy and paste the value of a row on 1 worksheet to another
  18. Create macro to copy paste many rows at once
  19. Macro To Copy/paste Graphs While Changing Only The Data Source Range
  20. How to write a macro to copy and paste data for the to the correct month column?
  21. Trying to create a macro to delete entire rows based on cell value that is variable in length and characters
  22. Need an helpt to write a Macro to copy Value from number of workbooks to single file
  23. Macro to copy data from a different application
  24. How to Create a Macro to Edit a Variable Amount of Information
I need to have values retained in cells and deactivate formula after use so
the subsequent formula can access same criteria cell for new information only
to be placed in new cells.

Steps:

Report downloaded each month onto Spreadsheet entitled 1.

Title page (Natl Rank) for report reads current Month.
=IF('Natl Rank!$C$1="Month of March 06",'1'!$C$26."")
Enters value in cell based on C1 text reading "Month of March 06"

Cells directly to theright of the March cells are April cells.
Following month same cell Natl Rank C1 text gets changed to read Month of
April
=IF('Natl Rank!$C$1="Month of April 06",'1'!$C$27."")

March column goes blank. April column fills in

I need the data collected for March to be retained somehow but the report
that this data is coming from is over written each month. New month's data
is to be placed in cells next to the preexisting March data. If I change the
text to read Month of April from March my cells for March go blank. When I
overwrite the existing report for the new months data, the data inside the
March cells change and shouldn't, --

Should I create a macro to copy the March data before the new download and
Paste Special the values? If I do that then I am wiping out my formulas I go
but get to retain the data.
Rebecca

--
Rebecca

I need to have values retained in cells and deactivate formula after use so
the subsequent formula can access same criteria cell for new information only
to be placed in new cells.

Steps:

Report downloaded each month onto Spreadsheet entitled 1.

Title page (Natl Rank) for report reads current Month.
=IF('Natl Rank!$C$1="Month of March 06",'1'!$C$26."")
Enters value in cell based on C1 text reading "Month of March 06"

Cells directly to theright of the March cells are April cells.
Following month same cell Natl Rank C1 text gets changed to read Month of
April
=IF('Natl Rank!$C$1="Month of April 06",'1'!$C$27."")

March column goes blank. April column fills in

I need the data collected for March to be retained somehow but the report
that this data is coming from is over written each month. New month's data
is to be placed in cells next to the preexisting March data. If I change the
text to read Month of April from March my cells for March go blank. When I
overwrite the existing report for the new months data, the data inside the
March cells change and shouldn't, --

Should I create a macro to copy the March data before the new download and
Paste Special the values? If I do that then I am wiping out my formulas I go
but get to retain the data.
Rebecca

--
Rebecca

Hi
I need an help to create a macro to copy the value from number of excel sheets and paste it into another single excel sheet.
I have number files which are located in one location (ex: test, test1, test2, test3, test4)
I need to open each file and copy the item number which is in Cell B2 in the file named test.xls and paste it in the FINAL workbook and then again copy Total_Liab value in the tab named Calculations in the same workbook test.xls (total liability value) which is the last cell value in the column Q (example: Q51 in the Calculations sheet) Q51 and paste it next to the same item number in the FINAL workbook. Once it perform the action it should close the test.xls worksheet and then open the next test1.xls worksheet and should follow the same procedure as explained for the test.xls. Could you please help to create an macro

I need to have values retained in cells and deactivate formula after use so
the subsequent formula can access same criteria cell for new information only
to be placed in new cells.

Steps:

Report downloaded each month onto Spreadsheet entitled 1.

Title page (Natl Rank) for report reads current Month.
=IF('Natl Rank!$C$1="Month of March 06",'1'!$C$26."")
Enters value in cell based on C1 text reading "Month of March 06"

Cells directly to theright of the March cells are April cells.
Following month same cell Natl Rank C1 text gets changed to read Month of
April
=IF('Natl Rank!$C$1="Month of April 06",'1'!$C$27."")

March column goes blank. April column fills in

I need the data collected for March to be retained somehow but the report
that this data is coming from is over written each month. New month's data
is to be placed in cells next to the preexisting March data. If I change the
text to read Month of April from March my cells for March go blank. When I
overwrite the existing report for the new months data, the data inside the
March cells change and shouldn't, --

Should I create a macro to copy the March data before the new download and
Paste Special the values? If I do that then I am wiping out my formulas I go
but get to retain the data.
Rebecca

I need to have values retained in cells and deactivate formula after use so
the subsequent formula can access same criteria cell for new information only
to be placed in new cells.

Steps:

Report downloaded each month onto Spreadsheet entitled 1.

Title page (Natl Rank) for report reads current Month.
=IF('Natl Rank!$C$1="Month of March 06",'1'!$C$26."")
Enters value in cell based on C1 text reading "Month of March 06"

Cells directly to theright of the March cells are April cells.
Following month same cell Natl Rank C1 text gets changed to read Month of
April
=IF('Natl Rank!$C$1="Month of April 06",'1'!$C$27."")

March column goes blank. April column fills in

I need the data collected for March to be retained somehow but the report
that this data is coming from is over written each month. New month's data
is to be placed in cells next to the preexisting March data. If I change the
text to read Month of April from March my cells for March go blank. When I
overwrite the existing report for the new months data, the data inside the
March cells change and shouldn't, --

Should I create a macro to copy the March data before the new download and
Paste Special the values? If I do that then I am wiping out my formulas I go
but get to retain the data.
Rebecca

I have been trying to Create a macro to copy variables from different columns
but seem to be falling short as the variables that need copying are from different columns.
there is approximately 50 so do i have a time limit.. and they are the result of equations, so would this affect them
does it record my copying and pasting ? or do i need to program .

heres a simple example to work off in attachmentsBook3.xlsx

I need help creating a macro to copy from a cell in sheet1 to a cell in
sheet1. Though when it pastes I can't have it fill the same cell over
again I need it to paste it in the next cell down from it.

Example:

Sheet1 -> Sheet2

B12 -> B2
D12 -> C2
I5 -> A2
I17 -> E2
G12 -> D2
I22 -> F2
I27 -> G2

I have merged cells from B through G with rows 16-28 needing to be
copied to H2 but become unmerged in the seperate sheet. Can anyone
help me out?

Hi All

I was wondering how I can create a Macro to copy information that is o
Sheet1 to SHeet2. The kink is that it only should copy the informatio
in Column A when for example column C has a cell in it saying "YES"
Please let me know at your earliest convience.

Thank You

--
Message posted from http://www.ExcelForum.com

Hi,

Having searched the forums, and not finding exactly want I need, I wonder if anyone could advise. Apologies in advance for being a Visual Basic novice.

I would like to employ a macro to copy particular rows of data from each of 4 sheets to 4 consecutive rows on a fifth sheet.

Ideally, I would like to run the macro on the first sheet, possibly after highlighting a desired row, and for it to find and copy the same numbered rows from each of the 2nd, 3rd and 4th sheets, pasting them onto the fifth sheet (this may not the most efficient method). So, for example and picking an arbitrary number, row 31 on sheets 1, 2, 3 and 4 are copied/pasted to rows 1, 2, 3 and 4 on the 5th sheet.

I would like to be able to choose the row number (always the same) to be copied. I also need to repeat the process with other rows, copying/pasting over the previously pasted data (I've printed in the mean time).

Thanks.

I would like a macro that would transfer data from cells on one sheet
into cells on another sheet within the same workbook. I'm not familiar
with using the create macro feature so I was hoping to just load the
code in VB and run the macro. Any ideas? Thanks

---
Message posted from http://www.ExcelForum.com/

Hi,

I produce a report (example attached) each week, and need to know if i can run a macro on it so that certain rows are deleted from the sheet, depending on the what the text in the row says?

On the attached example, i need to basically delete any row that has (PARCEL ACCOUNT) after the company name e.g. cell A15 in the attached.

One factor that might complicate this is that the companies listed on the report may not be extracted to the same cell / row each week as the data extracted changes from week to week, and is therfore ordered differently each time.

I know how to create a macro to delete the same cells or rows each time the macro is run, but i need this one to only delete rows that contain the word PARCEL.

Could anyone help me please??

Many thanks in advance

James

I need to change the date format on 400,000 records in excel spreadsheets from YYYYMMDD to DD/MM/YYYY.

I have tried to do this by changing the cell format but this is not working.

Is it possible to create a macro to run on the data to change the format?

Hi
I've been trying to create a macro to do the following...

I've followed the instructions from another posting about normalising data using a pivot table.
What I would like to happen from the pivot table is as follows...after I double click the grand total..a new sheet opens with the information as follows..(this is just part of it).
I have tried to create a macro to do the following: rename the sheet to Prodn_Plan, sort the row information from largest to smallest and rename Row to Part_No, Column to Prd_Date and Value to Prd_Qty.
When I tried to create a macros to do this I get a "Run time error 9", Subscript out of range. " message.

Row Column Value
0 Jan-00 0
0 Jan-00 0
0 Jan-00 0
0 Jan-00 0
0 Jan-00 0
0 Jan-00 0
0 Jan-00 0
0 Jan-00 0
0 Jan-00 0
0 Jan-00 0
0 Jan-00 0

So here is my first DOH! question...given that this is Excel 2007, where should I put the macro to create this production sheet? I got another error message when I tried creating it as a form button on an existing sheet.
What would you recommend I do?

Many thanks...Anne

A macro that will

Hi,

I have a sheet in which you enter in new information. I need a macro to copy that information onto another sheet, onto the first empty row, so it does not clear the information that has already been entered. The first sheet is an information entry sheet, and the second is the database, which contains all of the information. I hope this is clear, if you do not understand, I will be happy to rephrase my question!

I have included two screenshots of the excel system. They are named.

Cheers,
Tom

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

Using the data sheet below, how do I write a macro that would find the data
that extists between the first (#1) (C2), (there's a header), to the last
(#1) (C10), INCLUDING any blank rows there may be between them and copy then
paste ALL the data from column A to S, to another sheet named R1. NOTE: this
needs to exclude any blank colums past (R) and further(I excluded the data
just for ease).
After that it needs to come back to this data sheet and copy everything
between the (#2)'s and copy them to R2 and so on until it reaches no other
numbers usually around the number 10, then return to R1, cell (A2).
Note, I put hyphens between cell values in row 2 to indicate new adjacent
cell value. Thanks everyone.

x a b c d e f g h i j
1 Header
2 110-fm-1-Hol-81-11/01/2006-Easy Obsession-T-8.50-0.50
3 111 fst 1 Fpx 79 09/24/2006 Easy Obsession D 8.50 3.00
4 113 fst 1 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
5 114 fst 1 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
6 115 fst 1 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
7 116 fst 1 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
8 Blank row
9 117 fst 1 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
10 118 fst 1 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
11 119 fst 2 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
12 120 fst 2 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00
13 121 fst 2 Fpx 72 09/10/2006 Easy Obsession D 6.00 0.00

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

Hello, I am new to the forum and excited to learn of its existence! I am a long time Excel user for basic functions...never tried to create a macro until recently. I do not know VB

My situation is that I am updating 2 worksheets daily with date and financial values. I am repeating a set of 6 steps up to 50 times and its sinking my time and killing my energy! I tried creating a regular macro to do the job and quickly discovered its limitations.

Here are the steps:

1) copy entire row
2) right click row below
3) insert copied cells
4) paste special -- "values"
5) select entire row (from step 4)
6) From the "Fill color" pull down - select white fill

7) in another 10-30 rows - repeat 1-6

I would be eternally grateful for anyone who can help me create a macro to run these steps!

Thanks for your time....

Asaya Adea

Objective: I am creating a report for schools whereby I will have charts on several topics. For each topic, I will have to produce the same chart on that topic for each school. Hence I was looking for a way to create the first chart on that topic, format it, and have a macro replicate (via copy/paste?) that chart, changing just the data that's showing.

I foresaw 2 options:
A. First, create the first chart in the series, format it how I wish (size, color, fonts, etc). Then call a macro that will iterate through the data and copy/paste that chart, relinking it to the iterating data.
B. Create a macro that iterates through the data, creating a chart at each step, and formatting that chart within the macro itself.

The question
What is the better approach to take? Formatting inside the macro seems daunting but perhaps this is the best way?

What I've done so far on these options/where I'm stuck

For Option A, I recorded the following (made no modifications myself):


	VB:
	
ActiveSheet.ChartObjects("Chart 18").Activate 
ActiveChart.ChartArea.Select 
ActiveChart.ChartArea.Copy 
ActiveWindow.Visible = False 
Windows("MyTest Macros.xls").Activate 
Range("L3").Select 
ActiveSheet.Paste 
ActiveSheet.ChartObjects("Chart 19").Activate 
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A6:E7"), PlotBy:= _ 
xlRows 
ActiveChart.ChartArea.Copy 
ActiveWindow.Visible = False 
Windows("MyTest Macros.xls").Activate 
Range("G21").Select 
ActiveSheet.Paste 
ActiveSheet.ChartObjects("Chart 20").Activate 
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A9:E10"), PlotBy _ 
:=xlRows 
ActiveWindow.Visible = False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Of course if I delete what it's produced and start over, as it doesn't know what "Chart 19" is the next time around. I thought perhaps a way round this is to store the copied or pasted ChartObject, and refer to its variable name when I use the ChartObjects.Activate function?

For Option B (formatting within the macro itself) I got a bit further and have started the iterative process:


	VB:
	
 
Dim totalschools As Integer 
Dim mystring As String 
Dim mycol1 As Integer 
Dim mycol2 As Integer 
 
numschools = 1 
totalschools = 10 
mycol1 = 3 
mycol2 = 4 
 
mystring = "A" & mycol1 & ":E" & mycol2 
 
Do While numschools

Hi ,

Before you look into the spreadsheet, below is the simple illustration. How i can wirte macro to copy paste value from column E in Table 2 to the respective month in Table 1. Month is dependent, and it is always change. There will be a selection for month in the spreadsheet. Please help.

Table 1: By Month View
April May June Type A Type B Type C Total

Table 2: Summary of April Data
A B C D E Group A Group B Group C Group D Total TYpe A 1 2 3 3 9 Type B 2 3 3 1 9 Type C 1 2 1 2 6 Total 24

Hello,

I am trying to create a macro to use on a speadsheet that checks the value of a cell in Column A and if it meets a criteria (which is variable) then it will delete the entire row.

The contents of the cells is below. The "Pc. Price:" part does not change, however the price does. The price can be from a whole number with no decimal point to having thousandths of a cent (i.e. .003).

The Pc. Price, Total Value, Total Pieces, Total Cartons, and Delivery address are the only things I want to be deleted. I can not figure out how to delete a line based on part of the cell value. The information is electronically fed to me so I have no choice in how it shows up on the excel worksheet. (I tried copy and paste into excel to hopefully pull the information apart, however last time I tried that it all went into column A anyways.)

Each row of information below is in 1 cell together, the decription of the value, and the value.

Pc. Price: 0.03
Total Line Value: 300
Line I want to Keep
Line I want to Keep
Line I want to Keep
Total Pieces: 10000
Total Cartons: 100
Line I want to Keep
Line I want to Keep
Delivery to: adress with letters, numbers, and dash's possible
Line I want to Keep

I've recorded a macro, however when I go to delete the lines it deletes the same row numbers everytime. I go through about 20 of these sheets a day, and the data for each part can be from 17-19 rows long, and there can be up to 200ish groups of the 17-19 lines of data on each sheet. So that didn't work out to well for me . Any help would be appreciated. Thanks!

Hi,

Anybody can help me to create a macro to open the number of files automatically and paste that value into the single workbook

I have data on various companies that run from cells a1 to certain range, the range will be differ for each company in a workbook. I have about 200 such workbooks (with data in cells range differs) and would like to write a macro that copies this value from each of this work book to a new single file. That file would list data for all 200 sites.
Is it achievable? I thought about doing vlookups and am afraid of too many links and time consuming, possibility of errors.

Thoughts?

Thanks in advance

hi all,

I need to create a macro to copy some data from SAP (similar to Access) and then paste back in excel. How do I do that?

Normally SAP is already opened,

I want to tell Excel macro to:

1) copy this customer # in A1, from excel spreadsheet (i know how to do this)
2) enter the # in SAP (there is a box, not a cell), hit enter, SAP will then pull up the data list,
3) copy the customer name in SAP
4) paste customer name back to cell B2 in excel.

Any help will be appreciated.

Thanks

Hi,

I have an Excel table full of information for a certain number of
individuals. I've created a macro to reorganize, transpose, and arrange the
data that works beautifully for information for two individuals. However,
the number of individuals whose information is in the table will vary, so I
need to be able to set the macro to do these tasks regardless of the amount
of information. I imagine that I need to be able to set it to copy and paste
all the rows for one name, then all the rows for the second name, so on,
until there is a blank cell (indicating that all of the information has been
manipulated and the macro can stop).

Any ideas about how to do this would be great, as well as resources for more
information. I've looked around but have found much specific to this issue.

I'm using Excel 2003 on Windows XP.

Thanks,

Matt


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