Computer Support Forum

Simple Excel Macro to Copy Row from one Sheet to Another

Question: Simple Excel Macro to Copy Row from one Sheet to Another

I'll try to be as descriptive as possible.

Current Status:
I have an excel file with two sheets: sheet1 and sheet2.
Column "A" will have lots of data.
Some rows in column "A" (or the first cell for that row) will have the word "Directory".

I need to search sheet2, only in column "A", for the row that contains the word "Directory" and copy that whole row onto sheet1 in the same row. Continue doing that until it completes through all of the rows.

Example Data:

Row Column A Column B Column C
1 blah blah blah blah blah blah
2 blah blah blah blah blah blah
3 blah blah blah blah blah blah
4 Directory cool text huh?????
5 blah blah blah blah blah blah

Row Column A
Row Column A Column B Column C
1 blah blah blah blah blah blah
2 blah blah blah blah blah blah
3 blah blah blah blah blah blah
4 blah blah blah blah blah blah
5 blah blah blah blah blah blah

*Action: Column "A" find "Directory" Copy Row 4 to sheet1

Row Column A
1 blah blah blah blah blah blah
2 blah blah blah blah blah blah
3 blah blah blah blah blah blah
4 Directory cool text huh?????
5 blah blah blah blah blah blah

Relevance 100%
Preferred Solution: Simple Excel Macro to Copy Row from one Sheet to Another

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Simple Excel Macro to Copy Row from one Sheet to Another

"copy that whole row onto sheet1 in the same row"

Do you mean like this?

(make sure what you already have is "safe" before you try, obviously)

Sub test()
If ActiveSheet.Name <> "Sheet2" Then Exit Sub
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each Cell In Range("A1:A" & LastRow)
If InStr("Directory", Cell.Value) <> 0 Then
x = Cell.Row
Sheets("Sheet1").Cells(x, 1).Insert Shift:=xlDown
End If
Next Cell
Application.CutCopyMode = False
End Sub

1 more replies
Relevance 78.88%

I need to copy n number of rows from a sheet in my local machine to a sheet in fileserver.
My requirement is - if I select last 2 rows in the sheet in my local machine I need the rows to be copied after the first blank cell in the sheet in fileserver.
Also it would be helpful if the macro code is only in the local sheet and not in the fileserver sheet.

I am a newbie to macros pls help....

More replies
Relevance 100.86%

I hope someone can help me with some VBA code. Sheet1 has 500+ rows and 30+ columns of data, sorted by text in column G. I want to create a data input sheet to manually key in data. I need help to create a macro to cut and insert the row into Sheet1 - the first row after it finds a match in column G. Thanks

More replies
Relevance 90.61%


I am trying to classify and code my inventory items in excel.
My classification sheet contains data in columns which I would like to copy to rows in another excel sheet.
Here is the sample data in ACMOTORS-ATTRIBUTES sheet:

The above sheet has to be converted to columns as in Item_Classificatios_Form sheet :

Can anybody assist me in writing a macro for the same.


Answer:Solved: Excel Macro to convert Rows from one sheet to columns in another sheet

8 more replies
Relevance 84.05%


I want to copy the original sheet into another sheet.

I have sheet1 tab.

if I use this code:
ActiveWorkbook.Sheets("Sheet1").Copy after:=ActiveWorkbook.Sheets("Sheet1")

it created sheet1(2). but I want to specify my tab name.

if I chnage it and run:
ActiveWorkbook.Sheets("Sheet1").Copy after:=ActiveWorkbook.Sheets("helloworld")

It fails.. error comes up.

How do I make it work?

Thank you

Answer:Solved: EXCEL: copy sheet to another sheet

Hi sk0101,

rename it after copying:-

ActiveWorkbook.Sheets("sheet1 (2)").Name = "helloworld"


2 more replies
Relevance 83.64%

I was wondering if anyone here can help me.what im trying to do is: Create a macro to sort data line by line from a "Dump" worksheet to other existing worksheets. Lines will be sorted according to column B which has string values the same as the worksheet names.Sample data:A-B1 Town-Area2 Hamilton-Newcastle3 Charlestown-Newcastle4 Marricville-Sydney5 bondi-Sydney6 HamiltonA-Newcastle7 CharlestownA-Newcastle8 MarricvilleA-Sydney9 bondiA-SydneySo basically I want all lines with a value of "sydney" in collumn B to be sorted to an existing worksheet also named "sydney" The same for those with "Newcastle" in collumn B.The below macro that i copied from another thread(thanks Humar) almost does what i need but it will only copy 1 line to each tab.Can it be adjusted to copy all appropriate lines to each tab?Thanks in advance for any assitance.Cheers,michaelNOTE: the worksheet with all the data in it is named "Dump" Public Sub MoveToTab()Dim rngRow As RangeDim rngCell As RangeOn Error GoTo ErrHnd'use All used rows except first, in 'Dump' WorksheetWith Worksheets("Dump").UsedRange.Offset(1, 0) _ .Resize(Worksheets("Dump").UsedRange.Rows.Count - 1, _ Worksheets("Dump").UsedRange.Columns.Count) 'loop through all rows For Each rngRow In .Rows 'test if tab exists On Error Resume Next If Not Worksheets(rngRow.Range("B1").Text).Name <> "" Then On Error GoTo ErrHnd 'No Tab of this name - so create one and copy row Worksheet... Read more

Answer:copy line to new sheet macro

re: sorted to an existing worksheet also named "sydney"Since the sheets already exist, you don't need the parts of Humar's code that creates the sheets. This code just copies/paste the rows to the proper sheet.You used the "sorted". Do you want the data sorted after it is copied? I did not include code to do that. Let us know if you need that code also.Option Explicit
Sub MoveToTab()
Dim lastDumpRow, lastTabRow, myCity As Integer
Dim citySheet As String
'Find last row in Dump Sheet
lastDumpRow = Sheets("Dump").Range("B" & Rows.Count).End(xlUp).Row
'Loop through Dump sheet Rows
For myCity = 2 To lastDumpRow
'Set Sheet name variable
citySheet = Range("B" & myCity).Value
'Find current last row in each city sheet
lastTabRow = Sheets(citySheet).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy/paste row from Dump to current city sheet
Sheets("Dump").Range("B" & myCity).EntireRow.Copy _
Destination:=Sheets(citySheet).Range("A" & lastTabRow)
End Sub

5 more replies
Relevance 83.64%

Thank you very much in advance!!! I am trying to copy down the values from a different sheet called (tester) to a sheet called (copy to upload WS). I want the values from column G2 until the end of the data (the length varies month to month) from sheet (tester) to sheet (copy to upload WS) pasting starting from D13 until the end of the data.
I also want the same scenario except for (tester) column I2 till end of data pasted to sheet (copy to upload WS) column G13 till end of data.
Is it possible to combine both of these into one macro? As I mentioned the length of the data changes for both of these columns.

More replies
Relevance 83.64%

i have a spreadsheet that is looking up data from a daily update sheet, and I need to, if a column contains a particular piece of information, copy some info from one sheet to the next. The pertinent columns are thus:Sheet 1 (source, sheet is named "Import")
Index Serial Number
1 induct x10001
2 1 x10002
3 2 x10003
4 induct x10004
Sheet 2(destination, Sheet is named "Primary")
Serial Number
1 x10002
2 x10003
The macro would need to step through the Import tab checking each row (starting at 2, row one has the row headers) and copy cell 2(the serial number) on any row where cell 1 contains the word "Induct" and paste it in to column 1 of the primary tab in the first empty cell.

Answer:Using a Macro to copy information from one sheet to another

Currently this is what I have, but its not working. It copies the data but does not paste it in to the appropriate cell(doesn't paste anything anywhere, as near as I can tell. )Sub Induct()

'start at row 2. Row 1 has headers
x = 2
'Start the loop
Do While Cells(x, 1) <> ""
'Look for data with 'induct'
If Cells(x, 1) = "Induct" Then
'copy the Cell if it contains 'Induct'
Worksheets("Import").Cells(x, 2).Copy
'Go to sheet2. Activate it. We want the data here
'Find the first empty row in sheet2
erow = Sheets("Primary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
'Paste the data here
ActiveSheet.Paste Destination:=Worksheets("Primary").Cells(erow, 1)
End If
'go to sheet1 again and actvate it
'Loop through the other rows with data
x = x + 1

End Sub

2 more replies
Relevance 82.82%


I have a workbook that has two sheets. On the first sheet is a list of approximately 60 text strings (i.e.: customer is not happy, not negotiable, no one cares, etc). The second sheet has multiple columns from A:EF and approximately 785 rows. The data I am searching for is in column J (Notes) on the second sheet.

I am looking for a macro that will do the following:

1. Copy the headers from the second sheet to a brand new worksheet, then,
2. Search through the second sheet in column J (Notes) for all of the multiple text strings that are listed on the first sheet (A1), then,
3. When a match is found, copy and paste that Entire Row to the brand new worksheet created in step one on the next blank line on the third worksheet.

I need this macro to be able to be assigned to a search button and run from the first worksheet that contains the text strings. I also need to be able to ADD to the list of text strings should we discover that we missed one!

I really appreciate any help provided!!

Thank You all very much!!!!!

More replies
Relevance 82.82%

I need a macro that copies data from various cells in calculator then paste them in an archive sheet. the cells are not in any particular order but they will be in the archive sheet. then i need the macro to move down 1 row to the next blank row and zero out the calculations on the calulator sheet. and repeat every time this button is pressed.CAN ANYONE HELP!!!!????

Answer:I need a macro to copy data to an archive sheet

First, when posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "I Need Help With A Macro" we wouldn't be able to tell one question from another and the Archives would essentially be useless. I have edited the subject line of your post to show you what I mean. Scan the list of questions in this forum to see the types of Subject Lines that others have used. re: CAN ANYONE HELP!!!!????Second, please do not use all uppercase when posting. All uppercase is the internet equivalent of yelling and nobody likes to be yelled at. In addition, !!!!???? isn't going to get you an answer any quicker than a using the correct punctuation, a single question mark.As for your question, we would need more detailed information than you have provided before we could offer any suggestions. Since VBA is very specific in its instructions, we need to where the source data is located, what sheets you are working with, etc. Perhaps a sample of your data would help. If you are going to post example data, please click on the following line and read the instructions found via that link. Thanks.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

12 more replies
Relevance 82.82%

Hi I am looking for help I need a macro to move rows from on excel 2007 workseet to another based on the values in one colume.So move data from sheet1 or sheet2 based on data in colume L.. My sheet1 has header row which i will copy to sheet2Please help

Answer:Macro to copy rows from one work sheet to oth

Hello again,Sorry after reading through other posts I feel i have not given my request enough detail. If someone could help me I would be very grateful, also I am not very familiar with Macros.I have two sheets one called Expenditure which is the master sheet and one call Tput. I would like to automatically update the Tput sheet with rows copied from the expenditure sheets if the value in column L is "TP" both sheets would have the same heading rows. Oh and if possible once a row has been copied mark it as copied and only move new entries. I hope this is clearer and sorry to have put my request in twice.

2 more replies
Relevance 82.82%

I have a 2 sheet spreadsheet. In sheet "data" I have a range "allp". I need a macro that will copy and delete range "allp" then select sheet "test" and paste the copied values with the 1st value into cell B1 of that sheet. Any help would be greatly appreciated.ThanksJohn

Answer:macro to copy a range from 1 sheet and paste

Sub CutPaste()
Sheets("data").Range("allp").Cut _
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 82.82%

Hello everyone,
I have only little knowledge in excel VBA. Hence I like to have a macro with input buttons for entering start and end date that will copy the entire rows containing those dates and data to another sheet namely sheet 2.My data is in sheet1 and date column is will be nice if the date format is in dd/mm/yyyy.Any help is appreciated.
Thank you

More replies
Relevance 81.59%

New to Excel Macros, tried this from a thread from 2010, worked very well. Only thing is, in my worksheets, I have multiple rows with the same column value. I need to copy and paste them all into sheet 2, and then move on to the next gene, and so on.Sub GeneFinder()Dim srchLen, gName, nxtRw As IntegerDim g As Range'Clear Sheet 2 and Copy Column Headings Sheets(2).Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)'Determine length of Search Column from Sheet3 srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row'Loop through list in Sheet3, Column A. As each value is'found in Sheet1, Column I, copy it to the next row in Sheet2 With Sheets(1).Columns("I") For gName = 2 To srchLen Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole) If Not g Is Nothing Then nxtRw = Sheets(2).Range("D" & Rows.Count).End(xlUp).Row + 1 g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw) End If Next End WithEnd SubAnyone out there that can help? Thank you.

Answer:Macro t copy multiple rows (same column value) to new sheet

First, may I request that you click on the blue line at the bottom of this post and read the instructions related to posting VBA code in this forum. Please keep those instructions in mind the next time you post VBA code. Thanks!As for your question, this thread contains the solution. addition, this How To should help you write and debug VBA code. If you are going to be writing/editing VBA code, these techniques can be extremely helpful. infamous blue line:Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 81.59%

I have an excel file that has a sheet with pipeline information on loans (sheet name is "Pipeline"); there is a unique identifier on column D (loan number) in that sheet. In that same excel file but a different sheet called "Projections" that same unique identifier (loan number) is on column "D", there are 4 other columns with data that I would like to copy over to the Pipeline sheet from the Projections sheet based on the loan number match.I would like the macro to match the loan # on column D (range is D2:D600) from Pipeline sheet to column D in Projection sheet (same range) and if found I would like to copy the contents of columns I, J, K and L from the Projections sheet to the same columns in the Pipeline sheet for each loan number that matches.I honestly don't know where to start, I usually use the macro recorder and tweak from there but the macro recorder doesn't really help you create the logic, can anyone help me?

Answer:Macro to copy data from one sheet to another based on match

Why don't you just use VLOOKUP?The basic formula for Pipeline!I2 would be as follows. Drag this down as far as required.=VLOOKUP($D2,Projections!$D$2:$L$600,6,0)For Pipeline!J2 use =VLOOKUP($D2,Projections!$D$3:$L$600,7,0)To expand on this a bit, you could put this in Pipeline!I2 and drag it both down and across to Column L.=VLOOKUP($D2,Projections!$D$2:$L$600,COLUMN()-3,0)Finally, to eliminate the #N/A error that will occur if a value isn't found on the Projections sheet, put this in Pipeline!I2 and drag it down and across.=IFNA(VLOOKUP($D2,Projections!$D$2:$L$600,COLUMN()-3,0),"Not Found")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

5 more replies
Relevance 81.59%

I need to create a macro that when run accomplishes the following:

Creates a new sheet and names it the same as the value of the currently active cell in the current worksheet.

The macro should only work when Sheet1 is active, and only if the active cell is within a range in column A e.g. > A5.

If a sheet of the intended name already exists then it should display an Error Message.

The new sheet should actually be a copy of sheet 2.


Sheet 1 = 'Test List'
Sheet 2 = 'Blank Test Form'

If I enter '010' in Col A, Row 10 on sheet 1, and then run the macro (via a button) it should copy Sheet 2 into the workbook giving it a name of 'Test010'.

If poss, I'd like it to create the new sheet and place it alphabetically along the sSheet names at the bottom of the workbook!

One for XLGURU please?


PS I am about to start a VBA course so this will help me.

Answer:Macro to add a new sheet in Excel

16 more replies
Relevance 80.77%


I have built a small, simple Macro in Excel which simply un-protects a sheet, copies and pastes some data from another sheet, and then re-protects the sheet. However, when I run the Macro, a prompt asking for a password to un-protect the sheet is asked, which is fine, then the copy and paste happens, however, I am not asked to enter a password to re-protect the sheet.

When I exit the Macro, the sheet appears to be protected, however, simply selecting the un-protect sheet option, will un-protect it without prompting for a password. Is there something I'm missing? I wish to re-protect the sheet, where a password is required.


Sub Macro2()

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Any help would be gratefully received, thanks.

More replies
Relevance 80.36%

Simple problem - copy sheet then delete duplicate rows

I have a worksheet that is composed a header row, and columns of data (client call results).
Each client is identified by a unique ID number in column A, a seperate column F has the 'call number, from 1 to x'.
So a client can have a single or multiple rows of data. Each client group of rows is sequenced with call 1 as line 1, call 2 at line 2, etc. etc. as your proceed down the rows.
The entire worksheet is already sorted (with a header row) ascending by client id and call #

What I want to accomplish is this

1 - copy the existing worksheet (CallRecords) to a new worksheet (CallToday)
2 - in the new worksheet, scan down (or up) from first data row (2) to the last row {ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row} and
delete duplicate rows of the Client ID retaining the row with the max call number for that client id.
3 - sort the new worksheet (CallToday) ascending by column (G) date, and column (H) time.

Thanks for your help. I am a newbie to VBA.

Answer:Simple problem - copy sheet then delete duplicate rows

stewartrr said:

I am a newbie to VBA.Click to expand...

Not a problem, since you don't need it for this ; a combination of built-in functions will do.

It's not clear if you have blanks in col A -- if you do, that can be easily fixed.

See the attached. The list of ID #s in col A of Sheet2 was extracted from Sheet1 using Advanced Filter (unique records only). The formulas in col B of Sheet2 pull in the value from col B of Sheet1 (per the last row for each ID #).

Post back if you need help interpreting the formulas.

1 more replies
Relevance 79.54%

Hi all, I've written a macro which connects to Database and compares almost 500 fields which are present in an XL sheet, with a value in database. I've looped through the cells and compared each value with the field.i.e.,for i=2 to numofrows select * from table1 where field1=cells(i,1);nextThis approach is taking more time. Can i use something like this ?select * from table1 where field1 between (range1 to range2);pls let me know.. Its very urgent. Thanks in advance

Answer:Excel Macro to Compare Database to Sheet

Depending on the database, the spreadsheet, and relationship thereof, it might be faster to just load the entire table into a recordset, then pair records and rows from there. You might also try selecting the columns you care about, instead of every column.

3 more replies
Relevance 79.54%

Hi,I have a same situation in the sheet1 contain the list of Block name (total 341), and in the second sheet the addresses (total over 10000), is it possible to mark red each cell in block sheet that occurs in address sheet. but the block name should be exactly match with the word in the address.for example block name: "Chanditala"in address : 116,Chanditala,Kolkata Pin:700053 need to highlight RED if block sheet if it is present in the address(exact)

Answer:excel macro: how to find word one sheet to another

Like I said, a simple macro does the trick:
Sub RedBlock()
'Loop through Sheet1 A2:A341
For Each b_cell In Sheets(1).Range("A2:A341")
'Search Sheet2 A2:A1000 for Block
With Sheets(2).Range("A2:A10000")
Set b = .Find(b_cell, lookat:=xlPart)
'If Block is found, color Sheet1 cell Red
If Not b Is Nothing Then
Sheets(1).Range(b_cell.Address).Interior.ColorIndex = 3
End If
End With
End Sub
The only issue would be if the address cell contains words like "Chanditalao" or "kChanditala". Since those words contain "Chanditala", they would be considered a match. There is an easy fix for that, but I didn't include it just to keep the code simple. If that fix is required, replace this instruction:Set b = .Find(b_cell, lookat:=xlPart)with this:Set b = .Find("," & b_cell & ",", lookat:=xlPart)That instruction ensures that the search string (the Block) contains a leading and trailing comma, just like in your address string example.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

20 more replies
Relevance 79.54%

Hi,I'm trying to write a macro with below codings. Facing some difficulties, please help me to proceed further. Requirement: Need to allow the user to edit objects & format cells in a protected cell(All cells). Did the changes in "Review>protect sheet>Allow all users of this work sheet to>Format cells/Edit objects"Objective of this macro is "Multiple users will make start(A column)/end time(B column)".Not let the user to make any changes in A& B column(Both are protected).Its working fine in the first instance, Once i click start/end time "changes in "Review>protect sheet>Allow all users of this work sheet to>Format cells/Edit objects" going back to same position that users can not make any modifications.Sub Button1_Click() If Not Intersect(ActiveCell, Range("A:B")) Is Nothing Then ActiveSheet.Unprotect Password:="XXX" ActiveCell.FormulaR1C1 = Now ActiveSheet.Protect Password:="XXX" Else: MsgBox "This is not the correct cell." End IfEnd Sub

More replies
Relevance 79.54%

Hello i have little knowledge in Excel but i will explain in the most detail possible.When you load the excel workbook there is a master slide within this there is:Drop Down List of Students NamesAnd a Add Student.I have made the Drop Down list input range the columns "M" and "O"What i would like is that every-time I write a new students name and press the macro.It adds there name to the column "M" and then creates a new worksheet and titles it the student name you in-putted.Any help would be helpful as im truly stuck.

Answer:Excel Make a Macro Add a new Work Sheet

re: "And a Add Student."What is a "Add Student"?re: every-time I write a new students nameWhere are you entering the new student's name? Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 79.54%

I am trying move data from Sheet1 to new sheets based on values in column A (a person's name). My data has an indefinite number of rows, but each row has a name in column A. I would like a new sheet for each name, and the name of the sheet to match the name in column A. The sheet should contain all rows from Sheet1 with a particular name, then sort the rows based on another column (date), sorted by earliest to latest date.

Answer:excel macro to filter and paste to sheet

My mistake...this is the first time I have used and missed the salutation and closing...Yes, I am requesting help and would be very thankful for any assistance.Thanks again!

7 more replies
Relevance 79.54%

Hi,I'm trying to write a macro with below codings. Facing some difficulties, please help me to proceed further. Requirement: Need to allow the user to edit objects & format cells in a protected cell(All cells). Did the changes in "Review>protect sheet>Allow all users of this work sheet to>Format cells/Edit objects"Objective of this macro is "Multiple users will make start(A column)/end time(B column)".Not let the user to make any changes in A& B column(Both are protected).Its working fine in the first instance, Once i click start/end time "changes in "Review>protect sheet>Allow all users of this work sheet to>Format cells/Edit objects" going back to same position that users can not make any modifications.Sub Button1_Click() If Not Intersect(ActiveCell, Range("A:B")) Is Nothing Then ActiveSheet.Unprotect Password:="XXX" ActiveCell.FormulaR1C1 = Now ActiveSheet.Protect Password:="XXX" Else: MsgBox "This is not the correct cell." End IfEnd Sub

More replies
Relevance 78.72%

What is the macro to print out an active sheet in excel? What I am wanting to do is run a report that will update info and print it out from a different sheet in the same workbook.

Answer:Solved: print active sheet macro in excel

Worksheets(1).PrintOut where 1 is the index number of the sheet






1 more replies
Relevance 78.72%

Ok, first of all I'm new to your site and WOW! I am incredibly impressed with the wealth of knowledge I've come across while searching through various forums! I've already found all kinds of great macros and ideas that will make my workload a lot easier. That being said, I have a problem with efficiently sorting some data and I hope one of you macro gurus can assist :)I've found several macros that do something similar to what I'm looking for, so hoping to get something a little more specific. Basically, I have some weather equipment that monitors up to 5 things in the air and then transmits data to my computer via radio, and spits out text files that I then copy into excel. It creates one text file for each thing I'm monitoring. The format is:Date (mm:dd:yy:hh:mm:ss), WhatIsMonitored, Unit#, ConcentrationSo for example if I'm using this equipment to monitor the concentration of oxygen in the air over the course of a couple minutes using just one unit (and by unit I mean the machine that is doing the monitoring), it will give me something like:09/01/2010 17:02:55,O2,U_10_O2,20.90000009/01/2010 17:03:09,O2,U_10_O2,20.90000009/01/2010 17:03:25,O2,U_10_O2,20.90000009/01/2010 17:03:40,O2,U_10_O2,20.90000009/01/2010 17:03:55,O2,U_10_O2,20.90000009/01/2010 17:04:10,O2,U_10_O2,20.90000009/01/2010 17:04:25,O2,U_10_O2,20.900000So for in this case it's not too much trouble to just copy the text into Excel, then use the "text to columns" feature to put it in columns.The problem I'... Read more

Answer:excel 2007 macro to sort data to new sheet

After you use the text to columns feature, I assume your data looks like this:
1 09/01/10 17:02:55 O2 U_10_O2.txt 20.9
2 09/01/10 17:03:09 O2 U_11_O2.txt 20.9
3 09/01/10 17:03:25 O2 U_11_O2.txt 20.9
4 09/01/10 17:03:40 O2 U_11_O2.txt 20.9
5 09/01/10 17:03:55 O2 U_12_O2.txt 20.9
6 09/01/10 17:04:10 O2 U_12_O2.txt 20.9
7 09/01/10 17:04:25 O2 U_13_O2.txt 20.9
This will probably need some tweaking, but it might get you started:Sub SplitSheets()
Dim ws As Worksheet
Dim lastDate, srcRw, dstRow As Integer
Dim newdate, wsName As String
'Determine last row with data
lastDate = Range("A" & Rows.Count).End(xlUp).Row
'Loop through data
For srcRw = 1 To lastDate
'Remove slashes from dates
newdate = Month(Cells(srcRw, 1)) _
& "-" & Day(Cells(srcRw, 1)) _
& "-" & Year(Cells(srcRw, 1))
'Build worksheet name
wsName = newdate & " Unit " & Mid(Cells(srcRw, 4), 3, 2)
'Create sheet if it doesn't exist
On Error Resume Next
Set ws = Sheets(wsName)
On Error GoTo 0
If ws Is Nothing Then
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = wsName
End If
'Copy row to next open row in corresponding sheet
dstRow = Sheets(wsName).Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets(1).Cells(srcRw, 1).EntireRow.Copy _
Destination:=Sheets(wsName).Cells(dstRow, 1)
Set ws = Nothing
End SubClick Here Before Posting Data or ... Read more

10 more replies
Relevance 78.72%


I am working in Excel (Hyperion essbase) where I am retreiving data from a number of sheets. I want to exclude a specific sheet in my workbook by it's sheetname (and not by countposition as I know I can use in this specific case by putting the sheet for instance last). The sheet I want to exclude is named "TOT" Here is my code that I thought would work but it still includes sheet "TOT" when running the macro:

Month = InputBox("YTD month?")
For i = 1 To Worksheets.Count
If Sheets(i).Visible And TypeName(Sheets(i)) <> "TOT" Then
ActiveSheet.Range("B7:C7").Value = "Y-T-D(FY09 " & Month & ")"
End If



More replies
Relevance 78.72%

I have an Excel workbook that contains two a sheets of information.
I have recorded a Macro that creates a third worksheet that has the same formats & formulas as the first two, but the data is blank (i.e. for a user to fill in).
Problem is that the macro is looking for a specific sheet number to add and copy to, so in VB code I have:


followed by various bits copied and pasted.

I need to stop the macro looking to create a specifically numbered sheet. Otherwise, if sheet 3 were deleted, the macro would stop working because the next sheet is automatically numbered sheet 4.
Also, I need to be able to use the macro more than once - i.e. I may need to add several new sheets in a session, not just one, and at the moment this means the macro works once and then fails.

Bearing in mind that I know very little about VB, what can I do?



Answer:Solved: Excel: Stop Macro from Specifying Sheet Number

6 more replies
Relevance 78.72%

I'm new to macros, and struggling.
I have a daily list of stock used.
I've written a macro that will add a new sheet, auto numbered.
What I can't do is figure out how to take the totals from "Day1" and put them in the "Stock Used To Date" row on "Day2". I can write the macro to specifically copy the data from sheet "Day1" to "Day2", but what happens when I need to update "Day3"?
There must be a way to refer to the current sheet number - 1.
Here's the example:

Sub AddYesterdaysTotals()
' Define which sheet we are on

' AddYesterdaysTotals Macro
' Macro recorded 13/11/2007 by

ActiveCell.FormulaR1C1 = "=DAY!R[3]C"
ActiveCell.FormulaR1C1 = "=DAY!R[3]C"
ActiveCell.FormulaR1C1 = "=DAY!R[3]C"
ActiveCell.FormulaR1C1 = "=DAY!R[3]C"
ActiveCell.FormulaR1C1 = "=DAY!R[3]C"
ActiveCell.FormulaR1C1 = "=DAY!R[3]C"
ActiveCell.FormulaR1C1 = "=DAY!R[3]C"

End Sub
Any suggestions would be very gratefully received!

Answer:Solved: Excel Macro - how to reference previous sheet

8 more replies
Relevance 78.72%

After upgrading from Win 2K to Win XP Pro, and Office XP to Office 2003, some of my Excel macros that fetche data from Factset in a batch and create multiple sheets no longer rename the sheet tabs on one of the 3 PCs I use at work. The other PCs with the same upgrade do not have this problem.

The tabs are supposed to be renamed with the descriptive data from respective sheets. These macros were created over 4 years ago and it was the first time that it failed.

Another VB based application built by Factset in 2002 which uses Factset' s Script function to fetch data from its PA application and export the results to Excel also is having seemingly related problem.

The process is supposed to create multiple reports and export them on separate sheets in the same workbook. However, even though the reports ran through and created a big file, it ended deleting all the other sheets except the last one with a tab name "Sheet1 (x)".

What could be the possible sources for this problem and how canI get around it?



Answer:Excel Macro no longer names sheet tabs

8 more replies
Relevance 78.72%

Hi Guys,

I'm after a bit of help.

I'm working on a spreadsheet in Excel 2003, it's a sheet to monitor staff Holidays/lieu days/entitlement etc.
The basic setup is as follows:
the main sheet is 'Staff List' and each Staff member will have their own sheet (named as per their initials)

What I like to achieve is to have an 'fool proof' button (macro) to automatically insert a new sheet and insert the staff members name in cell AD2 and name the sheet using the staff members initials
and also insert a row on the 'Staff List' sheet (row 50, I will manually move it to the proper location (this macro is simply for the times I'm not in and colleagues mess up the formulas)).
Now the tricky bit is that there are a number of formulas on the 'Staff List' that would need to be copied to the new row

I have attached a test file to play around with for anyone who would like to.

Any Help/suggestions much appreciated!


Answer:Solved: excel 2003 insert sheet macro

16 more replies
Relevance 78.72%

I found this code in this forum.
i want to add recipient as CC or BCC. What is the correct code for that?
Thanks in advance!

Public Sub email()

Dim SubJ, Recip As String

SubJ = "Enter your suject"
Recip = "[email protected]"

ThisWorkbook.SendMail Recip, SubJ

msgbox "Email Sent"

End Sub


Answer:Send excel sheet ( email) through macro with recipient and cc

6 more replies
Relevance 77.9%

I need a macro that will go through all the excel files in a directory and add a template file. Replace the template formula names with the filename on the template sheet (I can do this with a find/replace type options on the template sheeet inserted)

With 100's of files this will save a lot of time.

Next is to create a Master summary file that will copy/paste values from all the template sheets into one file.

More replies
Relevance 77.9%


I'm new to this, so any help would be appreciated.

My task is this - make checkboxes that would swoh only wanted rows and then copy any wanted rows to another sheet which has a specific design.

So I have 2 columns "Description" and "price".To each row I made 15 checkboxes with macro that should remove unwanted rows using this macro:

Sub CheckBox2_Click()
If Selection.EntireRow.Hidden = False Then
Selection.EntireRow.Hidden = True
Selection.EntireRow.Hidden = False
End If
End Sub

And this actually works (to my surprise), but the thing is I want to make a button that would have macro which would copy only checkboxed (marked) rows to another worksheet. I tried to record macros, but that didn't work because it seems that I have to link all checkboxes to that button and only then I'll get a result. Now it copies all rows, but not the marked ones.


Answer:macro excel 2007 that copies filtered rows to another sheet

13 more replies
Relevance 77.9%

Hey everyone,

I was wondering if anybody on these forums could help me with the below task?

I run a backup program that generates email reports to a subfolder in my Outlook 2010 every 6-8 hours to tell me the status of every backup job that I have set up and have running. The email details the job name, the job start time, and the job status (running, completed successfully, failed, missed, cancelled etc).

At the moment, I sift through each and every one of these emails (I have over 100 backup jobs per day) and enter the relevant data manually into my excel spreadsheet. The spreadsheet looks like the following:

Job name Monday 1st Oct Tuesday 2nd Oct Wednesday 3rd Oct Thursday 4th Oct

What I&#8217;d like is to automate this process, so that each day, perhaps every 4 hours, a script would run that would search for certain values within each email I receive and update the excel sheet accordingly.


- Monday 1st Oct, 10am &#8211; email comes into inbox with subject/body containing &#8220;Job_xxx has started &#8220; >>>> update Excel sheet to record RUNNING under column &#8216;Monday&#8217; in row titled 'job_xxx'
- Monday 1st Oct, 1pm &#8211; email comes into inbox with subject/body containing &#8220;job_xxx completed successfully&#8221; >>>> update Excel sheet to record SUCCESS, ... Read more

More replies
Relevance 77.9%


I am using excel 2003 (version: 11.8341.8341) SP3 and created a macro to rename the active sheet:
Sub renamesheets()
Dim activesheet As String
If activesheet = "[B]Sheet01[/B]" Then
Set Sheets("[B]Sheet01[/B]").Name = "[B]qryOfficeNetForeign[/B]"
Application.ScreenUpdating = True
Else: End If
End Sub
SO the active sheetname is: Sheet01 and should be renamed to qryOfficeNetForeign.

This code is not working I am wondering why...


Answer:Solved: macro to rename active sheet in excel 2003

You don't need the SET keyword.
If ActiveSheet.Name = "Sheet01" Then
ActiveSheet.Name = "qryOfficeNetForeign"
End If
If ActiveSheet.Name = "Sheet01" Then
Sheets("Sheet01").Name = "qryOfficeNetForeign"
End If


2 more replies
Relevance 77.9%


So I am trying to figure out a macro that will allow the user to click a button and return to the last active sheet.

Basically why I need this is because I have 2 separate interfaces that have links that go to the same set of sheets. Based on what interface you are using (quick or advanced) I want the "return to home" link to go back to the interface that you were previously on.

I am imagining something like If(lastactivesheet=1,link to interface1), if (lastactivesheet=2, link to interface2) else (link to interface1)

I am not great with programming so any help would be much appreciated

Answer:Solved: Easy Excel Macro help, return to last active sheet

Check this link. It may answer your question.

3 more replies
Relevance 77.49%

I am trying to learn how to write macros so we can do some engineering analysis. im an intern at my job and was just thrown an excell book and told to figure it out. any helpw ould be great.

Soo i have this idea where you just enter in data on the first worksheet and then then the macro is used it sends it to the second sheet where all of my formulas and analysis goodies are. any ideas for a simple macro for this. My boss wants to make it where if you enter two sets of data it makes two worksheets. is that possibe?

Answer:Solved: a simple copy and paste macro help

8 more replies
Relevance 77.08%

Hi. I have a select range of cells with a set of code that unprotects the sheet when selected in order to allow hyperlinks to be created.

However when I select an active hyperlink in one of those cells it gives me the following error even though it works correctly:

Run-time error '9':

Subscript out of range

The code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 13 Then
Sheets("Summary of Contracts").Unprotect Password:=""
Sheets("Summary of Contracts").Protect Password:=""
End If
End Sub

I would like to ask whether this error can be avoided or whether some coding can be added to hide/remove the error message itself.


Answer:MS Excel 2000 Select hyperlink on a macro enabled protected sheet

I tried this on my machine and it was caused when the sheet name wasn't 'Summary of Contracts'. Suggest you need to check the sheet name, if you don't use the sheet names then you could use sheets(1).unprotect etc. But thats not a great idea either!

3 more replies
Relevance 77.08%

Hi, any help very much appreciated. Excel VBA. 2007, Windows 7.Enter a numeric value in cell B10 in Sheet 1, say 20Copy the numeric value of rows from sheet 2 (one column only) and paste in sheet 1 starting at cell B15 say.So 20 values in column B say in sheet 2 will be copied and pasted to Sheet 1 cells B15 down.Hope that makes sense!Vern

Answer:excel vba copy from one sheet to 2nd

There appears to be some specifics missing from your request:For one thing, you don't say what Row in Sheet 2 Column B the data to be copied begins.What do you want to happen if you enter 20 in one instance and now have data in Sheet1!B15:B34, then you enter 15 the next time? Should all of the old data be cleared or should the data in B15:29 be overwritten and the data on B31:B34 left alone?If the old data should be cleared, will there be any data below the pasted data that should not be cleared or can the code simply delete all dat from B15 and below? Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 77.08%

Can't get this to work. Help! Dim sReportFileNameA1 As String sReportFileNameA1 = InputBox("Enter the Report Name.") Sheets(" & sReportFileNameA1 & ").Select ActiveWindow.SelectedSheets.CopyThis code doesn't copy the worksheet I specify. What's wrong?

Answer:Excel VBA to Copy Sheet

Drop the quotes and the ampersands.When a value is assigned to a variable, you just use the variable:
Sheets(sReportFileNameA1)Also:You rarely have to Select an object in VBA to perform an operation on it. You can refer to the object and perform the operation in one step:
ActiveWindow.SelectedSheets.Copycan be reduced to:
Sheets(sReportFileNameA1).CopyHeck, if you wanted to, the whole thing could be reduced to one line:Sub CopySheet()
Sheets(InputBox("Enter the Report Name.")).Copy
End SubAin't VBA fun?Posting Tip: Before posting Data or VBA Code, read this How-To.

5 more replies
Relevance 76.67%

I'm a VBA newbie and would much appreciate any assistance in creating a macro to carry out the following task:

I am analysing several funds. I have spredsheet which calculates certain statistics for each fund when the fund is selected from a drop down menu.

I have an output table with the fund names in the first column, Statistic 1 in column 2 and Statistic 2 in column 3. In order to populate this table I have to manually select each fund and then copy and past the calculated statistics into the table. I would like to a macro which automates the task of selecting each fund in turn and copying and pasting the results into the table. To illustrate the issue more clearly I have attached a sample spreadsheet with some dummy data.

Any assistance would be much appreciated.

Answer:Solved: Help needed with simple copy and paste macro

7 more replies
Relevance 76.26%

Thanks for reading, first of all.

I am not the most savvy person when it comes to programming. I can manage my way around formulas pretty well, but when it comes to VBA and macros I am utterly lost. I believe I need a macro to do what I want, but I have no clue where to begin. I have searched forums but what I need is so complicated that I don't know where to look to find it so I thought I would ask for help.

I have a spreadsheet which tracks projects I am working on. Right now the sheet I am working on is "2013" for example. The sheet has several columns, but the only columns of significance to this macro are:

Account Name
Requested Date
Due Date
Completed Date

Account and Requested Date are currently manual entries.
Urgency is a data validation dropdown with: NORMAL, RUSH and OTHER.
Due Date is a formula which calculates a due date based on the Urgency selected. (Except Other, in which case I override the Due Date manually.)
Completed Date is also a manual entry.
The 3 stages are data validations which default to a blank cell and then have a Square Root sign (which looks like a checkmark) as the only other option.

Now that I have explained the source data, allow me to go into detail what I would like this to do.
I would like to populate the first sheet of the spreadsheet with seven lists. These lists would, ideally, tell me at what stage each project is at (by account and due date). The lists I have set up are in seven bo... Read more

More replies
Relevance 76.26%

I would like to copy a formula from one excel sheet in the same workbook to another. =SUM(NOMINEES!C8:C11) my next row i need the formula to be =SUM(NOMINEES!D8:D11) but when i try it goes to =SUM(NOMINEES!C9:C12)Hope somoneone can guide meThanks

Answer:Copy formula in excel from one sheet

I'm sure there's more to this than you've told us, but one way to get what you want is to try this:1 - Copy the formula into the cell in the next column, which will give you the column offset that you want2 - Move your cursor over the outline of the cell until it turns into 4 arrows3 - Drag the cell contents to the cell where you want it and it will retain the column offset. Posting Tip: Before posting Data or VBA Code, read this How-To.

2 more replies
Relevance 76.26%

Hi,i have a worksheet with lots of tasks records, some completed, in progress, overdue, etc. can vb auto copy the entire row over to a corresponding status sheet if the status column answer to criteria?sheet1 -Maincolumn a =taskscolumn b =statusstatus drop-down list is in-progress, completed, pending, overduesheet2 -in_progresssheet3 -completedsheet4 -pendingsheet5 -overdueanyone can help?

Answer:excel-auto copy a row to another sheet

You should be able to do it using VBTo get Help with VBOpen ExelClick the Microsoft Office Button , and then click Program Name Options, where Program Name is the name of the program you are working in, for example, Word Options.Click Popular, and then select the Show Developer tab in the Ribbon check box.If you need more help I suggest you repost in the Office Software section.

5 more replies
Relevance 76.26%

I have a worksheet contains Sheet1 and Sheet2:Sheet1: contains Data about foreign workers, my reference to each worker is the Passport Number, some passports will expire soon or later,So, the main question is (How can i copy full row to Sheet2) when the passport is less than 1 year or less than 365 days FYIPassport expiry date identified as "pp_expiry" & start from cell No. (L7)Passport number identified as "pp_no" & start from cell No. (K7)thank you very much

Answer:Excel - Auto Copy A Row To Another Sheet

A macro could be written to copy the rows but something would need to trigger it. That would be based on when you want the copy to occur. The code could be run manually or it could be triggered by an "event" such any change to the worksheet, the opening of the worksheet, the saving of the worksheet, etc.In addition, since this a copy, and not a "move" (cut/paste) we would need a way to prevent the code from copying the same rows multiple times. This could be easily accomplished by putting a "flag" in unused column of Sheet1 once the row is copied.Any additional information you could provide related to how you use the worksheet might be helpful.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 76.26%

Hello friends, I have a template for recording statistics in an Excel workbook. The template is currently on Sheet 1 only, but I will need to copy it onto the other sheets (ie. sheet 1, sheet 2, sheet 3, etc) for recording other information. How do I copy this template from sheet 1 onto the other sheets? Thanks.

Answer:Copy Template to Sheet in Excel

Right click the sheet tab, select Move or Copy, click (move to end), tick Create a Copy and click OK.

1 more replies
Relevance 76.26%

I have a main database on one worksheet with the different types of sectors i.e. health, finance and whether they are active or not.I wish for the information to be copied to another sheet dependant on the sector i.e all health to health tab and all finance to finance tab regardless of whether they are active or not and then for all active to go the active tab and all non active to go to non active tab, regardless of sector.Any clues.

Answer:Excel - Auto copy a row to another sheet

You didn't label the columns in your example, so I am assuming that they are Columns A & B.Right click the sheet tab for your Main sheet, choose View Code and paste this code into the window that opens.Then go back to your Main sheet and change some Drop Downs. That row of data should be copied as you requested.Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if Change was made to Column A or Column B
If Target.Column = 1 Or Target.Column = 2 Then
'If True, then determine the next available Row in Column A of Target sheet
nxtRw = Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Row from Main sheet to Target sheet
Range(Target.Address).EntireRow.Copy _
Destination:=Sheets(Target.Value).Range("A" & nxtRw)
End If
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

8 more replies
Relevance 76.26%

What is the correct way to copy a whole sheet to another existing sheet in the same workbook?i would like sheet4 to store my data and copy it to sheet1 each time i run my macro, sheet1 being the sheet the macro does its work on.every which way i have tried to do this it either doesnt work, or copies 1000s of columns of blanks and excel says it has no resources.the macro is going to destroy the data on sheet1 when it runs, but i will need to repeat this proceses a few times to get my output data the way i want. so i need it to refresh sheet1 with the unadultered data each time i run the macro.

Answer:How to copy all data from one sheet to another excel vba?

Why not just copy the entire Sheet4 to a new sheet instead of copying the data? Then run the other macro against the new sheet.This code will delete the new sheet each time and then copy Sheet4 to recreate it.Sub CopySheet4()
'Don't display error if MyDataSheet doesn't exist
On Error Resume Next
'Don't display confirmation message before deleting sheet
Application.DisplayAlerts = False
'Delete the MyDataSheet
'Renable Alerts
Application.DisplayAlerts = True
'Copy Sheet 4 and rename it
Sheets("Sheet4").Copy Before:=Sheets(1)
Sheets("Sheet4 (2)").Name = "MyDataSheet"
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 76.26%

Perhaps it's too simple for me to understand...or I'm .....

I want to create a manual macro that will allow me to add 85 to whatever value may currently be in the cell. Nothing if/then scenario.

One of those things that I can normally do with my eyes closed but can't seem to make work.

Any help would be appreciated.

Answer:Simple Excel Macro Help

8 more replies
Relevance 76.26%
Question: Simple Excel Macro

How do you create a very simple macro to automate a repetitive step or two I'm doing in an Excel Spreadsheet?

Answer:Simple Excel Macro

Francine: Did I not include the instructions on how to record a macro in Word when I sent you that email? I'll resend it, if necessary. It's the same steps to do it in Excel. The only difference is that you create the toolbar button AFTER you record the macro. Basically:

Hit Tools-Macro-Record new macro, give it a name. Take the steps you "usually" take, then hit the Stop button on the Record Macro toolbar. To test it, hit Tools-Macro-Macros and run the macro. When you're ready to create a toolbar button, just hit View-Toolbars-Customize, click on the Commands tab. On the left column, scroll down and select macros. On the right, click on the smiley and drag it onto a toolbar. Edit/change the button as directed in the Word Macros document. Close the toolbars dialog. Click on your new toolbar button and it will ask you to assign a macro, double-click your macro name. Hit the button again to run the macro.

Brainbench MVP for Microsoft Word

3 more replies
Relevance 76.26%

Good morning all,I am learning how to write a simple Macro program in Excel:A                B                 C===         ===             ===Jame        Apple           AppleTom         Orange         AppleDavid       Apple           Apple-------------------------------------------Here is the code:Sub testing()For N = 1 to 3If cells(N,2).value=cells(N,3).value ThenRange(corresponding-value).copyRange(corresponding-value).pastespecialEnd IFNextEnd Sub------------------------------------------------------------------------The program will copy specific rows when the cells in colum  B equals those in column C,and then it will paste the specific rows of data somewhere in the same worksheet.The program works but it returns empty cells.Here is what the result looks like:=====JameEmpty cellDavid=====Is there any method to skip the empty cells?Please kindly help if you could!KittyP.S. I am using Excel 2002

More replies
Relevance 75.44%

I need a visual basic macro for excel 2002 that will do the following:

From sheet1:ColumnA
Select rows until sheet1:columnA value does not start with REC (as the no of rows is unbalanced, sometimes 7 rows, sometimes 10 rows, sometimes 8 rows, etc)
copy selected rows and transpose to sheet2:columnA to however many rows were selected

Repeat until end last row
My real table has 30000 rows and the rows are unbalanced

I am attaching a test file.

Please help me and thank you for your support and time.

Tech Support Guy System Info Utility version
OS Version: Microsoft Windows XP Professional, Service Pack 3, 32 bit
Processor: Pentium(R) Dual-Core CPU E5300 @ 2.60GHz, x86 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 2047 Mb
Graphics Card: ATI Radeon HD 4300/4500 Series, 1024 Mb
Hard Drives: F: Total - 99998 MB, Free - 30856 MB; G: Total - 205236 MB, Free - 153405 MB; H: Total - 305168 MB, Free - 180367 MB;
Antivirus: None

Answer:Visual Basic Macro for Excel 2002 columns to rows in new sheet using unbalanced data

Can you also attach a second workbook showing how the data should appear after the macro is run?


3 more replies
Relevance 75.44%


I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.

Answer:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?

1 more replies
Relevance 75.44%

Good day..I've searched for an Excel macro that does the following :1. Press a button to open a search box2. All results found to be copied to sheet # 2This would work similarly to a web search engine, where all found results appear. I've seen quite similar examples here in the community, but search parameters must be included beforehand in the macro contents. I need one that is flexible and allows me to use a search box for whatever text string I need to find in each case.Thanks a lot...

Answer:Excel search box and copy all results to sheet # 2

I'll let you know right up front that if I were a user, I would not like the way the following macro works, but it does what you've asked. I've look around for a Forms method that accepts both a text input from a user as well as a "radial button" choice, but as I said my Forms skills are minimal.I'll keep looking...Option Explicit
Sub FindCopy()
Dim myString, firstAddress As String
Dim nxtRw As Long
Dim c As Range
Dim tryAgain As Boolean
Dim mySize As String
'Get input from user
myString = Application.InputBox("Enter A Search String")
'Exit if Cancelled
If myString = False Then Exit Sub
'Force valid entry
If myString = "" Then
If MsgBox("The Search Field Can Not Be Left Blank" _
& vbLf & vbLf & "Do You Want To Try Again?", vbYesNo + vbQuestion) = _
vbNo Then Exit Sub
GoTo startSearch
End If
'Set lookat value based on Search String length
If MsgBox("Exact Match Only? " & vbCrLf & vbCrLf & _
"Yes For Exact Match Of " & myString & vbCrLf & vbCrLf & _
"No For Any Match Of " & myString, vbYesNo + vbQuestion) = _
vbYes Then mySize = xlWhole Else mySize = xlPart
'Search entire sheet
With Sheets(1).Cells
Set c = .Find(myString, LookIn:=xlValues, LookAt:=mySize)
'Perform Copy/Paste/FindNext if myString is found
If Not c Is Nothing Then
firstAddress = c.Address
'Clear Sheet2
... Read more

15 more replies
Relevance 75.44%

I have a spreadsheet that contains monthly data. When I copy the sheet to a blank sheet for the next month, I lose the row height and column width. It defaults to a standard size and I have to go back to the original sheet to see the size and resize the new sheet's columns and rows.

There must be an easier way, isn't there?

Thanks for any help,

Answer:Excel 2000 - copy a sheet and keep format

7 more replies
Relevance 75.44%

Hello,I am using the exact codes stated below.If possible, could you please let me know;1) How or the additional codes necessary for having the same column heading always present in the search result sheet?2) How could I start with the input box showing in order to initiate the running of the VBA code automatically?Option ExplicitSub FindCopy()Dim myString, firstAddress As StringDim nxtRw As LongDim c As RangeDim tryAgain As BooleanDim mySize As StringstartSearch:'Get input from user myString = Application.InputBox("Enter A Search String")'Exit if Cancelled If myString = False Then Exit Sub'Force valid entry If myString = "" Then If MsgBox("The Search Field Can Not Be Left Blank" _ & vbLf & vbLf & "Do You Want To Try Again?", vbYesNo + vbQuestion) = _ vbNo Then Exit Sub GoTo startSearch End If'Set lookat value based on Search String length If MsgBox("Exact Match Only? " & vbCrLf & vbCrLf & _ "Yes For Exact Match Of " & myString & vbCrLf & vbCrLf & _ "No For Any Match Of " & myString, vbYesNo + vbQuestion) = _ vbYes Then mySize = xlWhole Else mySize = xlPart'Search entire sheet With Sheets(1).Cells Set c = .Find(myString, LookIn:=xlValues, LookAt:=mySize)'Perform Copy/Paste/FindNext if myString is found If Not c Is Nothing Then firstAddress = c.Address'Clear Sheet2 Sheets(2).Cells.ClearContents Do'Find next empty Row in Shee... Read more

Answer:Excel Search Box And Copy All Results To Sheet # 1

How did you solve the problem?You can just add a button on the sheet which will call the FindCopy routine. If you do not have the developer tab on your ribbon, do thisGo toFile>Options>Customize Ribbon - then tick developer in the right hand side list boxclick ok to get out of that menu.Now on your ribbon you will have the 'Developer' tab, click on it and select 'Insert' under ActiveX controls click on the first item which is a button, draw the button where you want on your sheet. Once its there double click it and within the code module insert this line.Assuming sheet1 is where the original code isSheet1.FindCopySo if your button is called CommandButton21 then your code will look likePrivate Sub CommandButton21_Click()
End SubNow whenever you click on this the search box will appear.

17 more replies
Relevance 75.44%

I got the below code from site.

Is there anyway of modifying this code so it saves each work with a specified password?

This process is carried out every month and there are 2 lots of 242 (1 set of new account and 1 set of renewal account for loans) new workbooks created so takes an age to open them all and resave with a password.

Thanks in advance for any help.
Sub Copy_Every_Sheet_To_New_Workbook()
'Working in 97-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

'Copy every sheet from the workbook with this macro
Set Sourcewb = ThisWorkbook

'Create new folder to save the new files in
DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString
MkDir FolderName

'Copy every visible sheet to a new workbook
For Each sh In Sourcewb.Worksheets

'If the sheet is visible then copy it to a new workbook
If sh.Visible = -1 Then

'Set Destwb to the new workbook
Set Destwb = ActiveWorkbook

'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143... Read more

Answer:Solved: EXCEL - Copy Every Sheet To New Workbook

'Save the new workbook and close it
With Destwb
.SaveAs FolderName _
& "\" & Destwb.Sheets(1).Name & FileExtStr, _
FileFormat:=FileFormatNum, Password:="xyz"
.Close False
End With

Spent some time and worked it out - lol

Added in the bold section into the save section of the code.

1 more replies
Relevance 75.44%

Anybody good with Microsoft Excel or know of a forum for Excel users?I've got a list of staff who do overtime. They pick their names, dates, sessions etc from pick lists which I've set up (one row per event). At the end of the month I sort into name order. In a second worksheet I have the same information, but split up into individual staff, including payroll number and payments, which I print out for Payroll Dept. To get information from the 1st worksheet to the 2nd, I currently highight all the rows relating to each member of staff, copy and then paste into the 2nd worksheet. Without using macros (which is diasbled at work), is there a way to get Excel to look up each member of staff on the 1st worksheet and to automatically copy each row that matches into the 2nd worksheet? I've tired VLOOKUP but it keeps picking up the same (first matched "record") each time.It's a long shot, but I hope that someone will be able to help.

Answer:Excel 2002 how to search a sheet & copy to another

You need to include the 4th parameter in the VLOOKUP as FALSE and it will then look for an exact match.

5 more replies
Relevance 75.44%

I have a budget workbook set up in Excel, and in that workbook there are many different sheets all part of the one book.

I have created a formula in one sheet from the data that is collected there, and that data is important for information collected on another sheet. I want to copy the formula and the data from that from that sheet and have it appear in the other excel sheet. I also want the data to change on the second excel sheet as the results from the formula change in the first sheet.

When i have tried to just copy and paste the formula from one sheet to the next I just get a ref error.

Many thanks for your help in advance.



Answer:Want to copy data and formula from one Excel Sheet to another.

This may help
How to Copy Formulas in Excel |

5 more replies
Relevance 75.44%

HI, I would appreciate help with something I am trying to createI have a workbook with multiple sheets that break up a list of items into categories. Each sheet is set up with column A part number column B qty column C descriptioncolumn D Price coumn E BxDsome of the items in column be are setup with a =+ or an IF() formula ( I am not sure if this will mater) I want to set up a system where lets say I put a qty in column B on line 4 on sheet 1, qty of 5 on line 10 on sheet 2 etc it will copy these automatically to the Master parts listSo on the master parts list I will get a print out of the entire line of columns a-e and not cause any issues from page to page? Is this possible to do? I am using ms office 2007 and I am not a power user. Thanks for any feedback in advance

Answer:copy excel spread sheet lines to another

re: "some of the items in column be are setup with a =+ or an IF() formula ( I am not sure if this will mater) "Assuming you mean Column B, not "column be", then I think it will matter because of your next line:"lets say I put a qty in column B"If you put a quantity in a cell that currently contains a formula, you are going to overwrite that formula.As far as the rest of your question, we can't see your workbook from where we're sitting, so we have no idea what your "Master parts list" looks like. Therefore we can't offer any specific advice.You are going to have to supply some more details before we can offer any help.Posting Tip: Before posting Data or VBA Code, read this How-To.

2 more replies
Relevance 75.44%

I need a code for the follow scenario: Also if you can kindly help with how to input the code. Thank you in advance. My Sheets are Titled: Sheet 1: Master Clients Sheet 2: Current Quotes Sheet 3: Virtual Quotes Sheet 4: Follow Up On sheets 2-4, column ' I ' has a drop down with 2 options: Accepted or Declined. If "Declined" is selected on sheet 2 (Current Quotes) I would like to copy the ENTIRE ROW over to sheet 3 (Virtual Quotes). If "Accepted' is selected on sheet 3 (Virtual Quotes) I would like to copy and paste the ENTIRE ROW over to sheet 4 (Follow Up). On all sheets there is a total of 12 columns (letters A through L). I appreciate your help in advance. Thank you.

More replies
Relevance 75.44%


I'm writing (rather than recording) macros for the first time.

I need to get excel to select a cell on a specific row, within the same column as the current active cell.

I have been trying...
... but this just shifts the active cell down by 16 rows, rather than selecting the cell in row 17!

I know this should be simple but I am finding Excel VBA help files atrociously difficult to follow.

Hoping somebody can come to my rescue...


Answer:Surely a simple Macro in Excel?

7 more replies
Relevance 75.44%

I need a simple excel macro for my work. I have tried looking it up online but I cannot get how to build one and I am in dire need of one I would love it as well if the person who gives me the macro could explain something about it.
Here is what I need. I need a macro that I can change as needed to write coins in Excel.
Example: you have A1, A2, A3, A4 as 0.25, 0.10 , 0.05, 0.01. These are of course quarters, nickels, dimes...
You have B1-B4 as the number of each type of coins that you currently have. I want a macro that when you put in B1-B4 the number of coins you have to tell you in C1-C4 how many whole rolls of coins(different coins have different rolls, quarters in 40's, nickels in 40's, pennies in 50's..etc) you have 0 to however many and in D1-D4 how many loose coins you have.
Again any help would be greatly appreciated and I would very much so contribute to y'alls site as well

Answer:needed simple excel macro

14 more replies
Relevance 75.44%

I'm sure this is a simple task for you boffins out there.

In Col A, I have a list consisting of a series of number ie
Unfortunately, these lists of numbers are taken from several different sources and some contain spaces ie
24,56,23,98,34, 77,01
65,98, 12,17,36, 99,31
I just need a macro, which I can then assign to a toolbar button, which eliminates these spaces.
All the list is contained to Col A and the number of rows varies from, say, 18 rows up to possibly 25.000 rows.

I'd greatly appreciate any help with this please.

Answer:Solved: Excel - Need simple macro

6 more replies
Relevance 74.62%

copy and paste formula will not work suddenly I am unable to copy and paste a formula in a cell to a new sheet. Nor can I copy and paste a formula one row down from the original formula. example A1 has formula of sum(d1:f1) when I try copy paste formula in a1 to b1 it does not copy over.

Answer:excel 2010 will not copy & paste a formula to new sheet

What version of Excel?What does happen, if anything, when you copy/paste?Have you tried Copy / Paste Special / Formula?Do you have Skype or have recently installed Skype?MIKE

13 more replies
Relevance 74.62%

Hi there!

I have customer details in Excel and for my business I would like some sort of way where I can have macro/button which on that sheet automatically copies the customer details in that sheet, to another sheet which holds the old customer details. But then deletes it in the customer details sheet.

So a kind of fancy cut and paste! I really need to sort this out, and will be happy for any ideas and suggestions!


Melissa xx

Answer:Help! Automatically copy table in excel to different sheet and then delete!

Hi Melissa

Can you post a sample workbook that reflects the structure of your workbook i.e. the sample file should not contain real names, addresses etc but should have dummy data and contain the same number of columns, column headings sheet names etc. We can then build for you a macro to do what you want.

3 more replies
Relevance 74.62%

I get the following message, if I open a workbook with different sized cells and data, select all[Complete Sheet] in the left corner[...A1]...................and.............then open a new workbook from an EXCEL ICON on my desktop...............and..............paste the contents into it by select this complete sheet[...A1...].........

Message:"Data on the clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway ?"

If I now paste the data, I end up with a very wide sheet with default column sizes....All our computers behave like this.
Note:If I do not open a 2nd new workbook from the desktop icon/excel, but rather go...
...>FILE......NEW....and open a new workbook from here, then this function does work.

Why is this, I always worked like described originally...?

Answer:EXCEL:Copy & Paste sheet between two workbooks, one original and new ?

I'm guessing here, but i think it's because when you open Excel by clicking on the desktop icon, you're opening a new instance of Excel. When you're working in a the same instance of Excel with multiple workbooks open, Excel is able to keep track of and transfer a lot of information between workbooks. However, it can't do as much when working between multiple instances. Each instance is like a walled off garden, and only limited copy/paste functions work between them.

Hopefully that makes sense.

And you used way too many ".........................."

2 more replies
Relevance 74.62%

I am working on a sales commission spreadsheet and am looking for a way to enter a record of a customer/sale on a line on 1 sheet (ex: May sales) and upon a trigger (entering a value in a cell when the job is closed) have the customer record (or better yet selected cells or fields from the record) copied to a line on a second sheet (ex: May closed jobs) where commissions would be calculated based in part on the date the job was closed. There are incremental commissions paid depending on the cumulative sales closed in the month so the copied records would need to populate the second sheet in chronological order upon being "triggered". Hope this makes sense, any ideas? Of course i could manually copy them at the correct time but would like something where the closed date is entered ahead of time with the original record on sheet 1 and upon entering a trigger (confirming the job is in fact closed) it auto populates sheet 2.

Answer:automatically copy part of a record to another sheet in excel?

forgot to include I am using Excel 2010 and Windows 8

1 more replies
Relevance 74.62%

need a simple excel macro to create 31 sheets and name them for the dateexample:sheet 1 is named 05-1sheet 2 is named 05-2sheet 3 is named 05-3etc for the whole month to 05-31

Answer:need a simple excel macro to create 31 sheets and name them

Assuming that you are starting with a "standard" new workbook with 3 blank worksheets, this code will result in 31 sheets named as you requested...
Sub DateSheets()
'Rename Sheets 1 - 3
For sht = 1 To 3
Sheets(sht).Name = "05-" & sht
'Add and Name sheets 4 - 31
For sht = 4 To 31
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "05-" & sht
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 74.62%

I need HELP !!!! I need a simple macro that will simply move the cursor around the spreadsheet ( right,left,up or down) without embedding the ending cell address in the macro itself !

EXAMPLE: Macro 1 - to move the cursor (up) 1 cell, to the (right) 2 cells and ( down) 1 cell. The key is, I need the macro to do this each and every time it is executed, in this pattern, no matter where it starts ( assuming it is not blocked by margin constraints )

PLEASE !!!!!!! I'm going crazy !


Answer:a simple but complex macro - 2002 excel HELP!

10 more replies
Relevance 74.62%

G'day Magicians

I am an incompetent Excel macro creator and would appreciate some help.

I have a spreadsheet column with text in it
I want to move every second row two cells right and one row up
I want to delete every third row

Should be easy but I've just wasted two hours trying to do it can someone please help me tonight?

Forever grateful


Answer:Solved: Simple Excel Macro Required

16 more replies
Relevance 74.62%


Can someone help me, I am trying to create a macro in excel that allows me to create a copy of a worksheet, clear its contents then hyperlink it to a particular cell.
The problem is that I want it to link a different page to a different cell every time i.e. create sheet 2(3) and link to R3. create sheet 2(4) link to cell R4, but I can only make it so that it creates the copy, deletes the contents and the links cell R2 with Sheet 2(2), at the moment the coding looks like this:

Sheets("Sheet2").Copy Before:=Sheets(2)
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'Sheet2 (2)'!A1"
What would I need to add or change to make it do what I want to?

Thanks Claire

Answer:Excel Macro Frustration, I think I'm missing something simple!

Can you post a sample workbook?


2 more replies
Relevance 73.8%

I would like to be able to copy onto an Excel sheet the Most Visited file/document shortcuts.

I'm not referring to any internet shortcuts/bookmarks.

The list of Most Recent files visited can be seen at:
C:\Users\[Owner Name]\AppData\Roaming\Microsoft\Windows\Recent

But I'm just looking to copy the shortcuts for say the top 25 or 50 most visited files.

Does anyone know how to do this ?

More replies
Relevance 73.8%

Excel 2003 - I Need To Copy the Data Identified By SUMPRODUCT To Another Sheet?Hi There.Please could someone point me in the right direction to solve a problem I'm having?In my workbook Sheet 1 contains a dynamic range from A1 to J880. (Capacity is 65536).Sheet 2 has different SUMPRODUCT formulae to count the occurrences in the range which fulfil certain criteria, sometimes with as many as 4 conditions. SUMPRODUCT returns an accurate number of occurrences but I need a function/macro/formula to take things a step further. Is there a way (without autofilter or pivot table) to automatically copy all the data in the rows identified in the range by the SUMPRODUCT formula into a separate list on Sheet 2? I know how to sort and autofilter the data manually but I'm designing the workbook to simplify and automate the process as much as possible for people who hate 'having to use' computers (strange people that they are!). Many Thanks. Gordon.

Answer:Excel 2003 - Copy Data Found By SUMPRODUCT To Another Sheet?

I don't know what you mean by "the rows identified in the range by the SUMPRODUCT formula".Please explain further.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 73.8%

I would like to be able to copy onto an Excel sheet my Internet Explorer Most Visited Places Shortcuts, NOT all history or recent places shortcuts.

You can view your internet Most Visited Places Shortcuts by:Get into Internet ExplorerPress Control H (which should bring up the Favorite-Feeds-History menu? or else press Control H a second time to get it)

Select the History tabSelect the down arrow under History and select "View by Most Visited"This will bring up the Internet Most Visited Places Shortcuts menu, which contains a list of the website places a person has visited the most.

From here you can also see the number of times you have visited a single shortcut by right clicking a shortcut, select Properties, and look at the Most Visited number I want to learn how to and also if possible get VBA code to copy these most visited shortcuts, say the most visited 50 or as many up to that as I can get, to an Excel sheet.

Please note that?I know that I can find internet History shortcuts at:C:\Users\[Owner Name]\AppData\Local\Microsoft\Windows\History
And can find internet Most Recent shortcuts (favorites) at:C:\Users\[Owner Name]\AppData\Roaming\Microsoft\Windows\Recent

But I do not want to find and copy all history shortcuts or just most recent shortcuts? I want to find and copy the most visited shortcuts.

Does anyone know how to do this ?

Answer:How do I copy Internet Most Visited Places shortcuts to an Excel sheet

Are you asking how to copy them all at once? I don't know how to do that but you can copy them one at a time by right-clicking and selecting Properties, within the Properties dialog is the link which you can then copy/paste into an Excel spreadsheet.

2 more replies
Relevance 73.8%

I would like to be able to copy onto an Excel sheet my Internet Explorer Most Visited Places Shortcuts, NOT all history or recent places shortcuts.

You can view your internet Most Visited Places Shortcuts by:Get into Internet ExplorerPress Control H (which should bring up the Favorite-Feeds-History menu? or else press Control H a second time to get it)

Select the History tabSelect the down arrow under History and select "View by Most Visited"This will bring up the Internet Most Visited Places Shortcuts menu, which contains a list of the website places a person has visited the most.

From here you can also see the number of times you have visited a single shortcut by right clicking a shortcut, select Properties, and look at the Most Visited number I want to learn how to and also if possible get VBA code to copy these most visited shortcuts, say the most visited 50 or as many up to that as I can get, to an Excel sheet.

Please note that?I know that I can find internet History shortcuts at:C:\Users\[Owner Name]\AppData\Local\Microsoft\Windows\History
And can find internet Most Recent shortcuts (favorites) at:C:\Users\[Owner Name]\AppData\Roaming\Microsoft\Windows\Recent

But I do not want to find and copy all history shortcuts or just most recent shortcuts? I want to find and copy the most visited shortcuts.

Does anyone know how to do this ?

More replies
Relevance 73.8%

I wanna copy online Data in another Sheet. on 1 Sec Interval. I have online Data Feed in 1 Sheet... from that I wanna copy particular Row/Column Online LTP (Last Trade Price) in another SheetIf possible give me online Reply.. my mail id is [email protected] in Advance......edited by moderator: Email address deleted

Answer:I wanna Copy Online Data Feed from Excel 1 Sheet to Another

Please do not post your email address.We do not want this site to become known as a place where email addresses can be harvested.DerbyDad03Office Forum ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 73.8%

Hi I have a very basic macro I need coded. I have an excel file with Column A,B,C,D,E,F,G Column B = last name of user Column C = first name of user Column D - institution number There are about 5000 users listed who have trained on Equipment X. I need to find which Equipment X trained users are also trained on equipment Y. Column E, F, G are the same setup as B,C,D and list out the folks who are trained on equipment Y (about 3000 users). I need a Macro go through each user and in Columna A...check if user trained on Equipment X is also trained on Equipment Y (which basically entails seeing if a particular B,C,D set of info is found in the list of 3000 users in E,F,G. Some names are idential but their instituion will be different so the macro needs to make sure it checks the complete record (if #B,#C,#D = any #E,#F,#G). If it finds the name put a 1 in the respective column A next to the name of the equipemnt X trained peron. If not put a 0. Does this make sense...any help would me MUCH appreciated!

See attached file.

Answer:Simple Urgent Excel Macro Help! Boss is killing me!

Sounds like a good place to use a database instead of a spreadsheet.

3 more replies
Relevance 72.98%

I'm hoping that someone out there would be able to help with this (in excel 2003). I've goofed around in excel a bit and can figure out a number of simpler items, but this one (after hours and hours of googling) escapes me.
I have an excel worksheet that lists, by row, the details for a photograph.

There columns are:

Column = English

zrecoID = Record ID
zmapID = Map ID
zphotID = Photo ID
zcate = Category
zelev = Elevation
zbroaloca = Broad Location
zunitloca = Unit Location
zdesctitl = Description Title
zdesccode = Description Code
zaddidesc = Additional Description
zseve = Severity
zstat = Status
zdocu = Document
zpriophot = Priority Photo
zphotfile = Photograph Filename
zphotloca = Photograph Location
zunitfoot = Unit Footage
zquan = Quantity
zperc = Percentage

For each unique issue in "Category", then by "Description Title", I need to retrieve the "Photo Location" from each row which has Active (there will be up to 4 active) in the "Priority Photo" column selected.
That data needs to be copied to a new sheet with the "Photo Location" of the 4 "Priority Photos" placed separately into 4 cells on one row.
I also need to list the "Photo ID"'s of each where the "Status" = Active and place that into the new sheet into one cell separated by (, ) -- comma delimited I believe.

Hope that makes sense. I'm attaching an example .xls file that I've manually copied and pasted the... Read more

Answer:Excel: Filter or Search, Copy, Join &/or Transpose data from one sheet to another

16 more replies
Relevance 72.98%

Hi All,

I really hope someone can help me. I've been looking through all the forums and haven't quite found a macro code that has worked for me. I know this is very simple, but I have no idea what I am doing. Here it goes....

I have a very simple expense report on Sheet 1 of an Excel (2007) workbook. In this worksheet, the first couple of columns simply list the expense items. However, there is a table with only 14 rows (including the header) of data (6-19). Underneath row 19 is a total row that sums the 2 columns of information (don't need to worry about this row but thought I'd tell you anyway). This table only has 4 columns as follows:

Column G - Code
Column H - Category
Column I - $ (this column is using the SUMIF function to calculate the expense category totals)
Column J - % of Total (this column is simply dividing column I by the Total $)

I don't know if this matters or not but there is also a pie chart linked to this table of information.

I would like this table (H6:J19) to automatically sort by % of Total (in descending order) any time I add new expenses so that I don't always have to sort manually.
If there is a way to do this, I would so appreciate simple instructions. I've attached a sample of what the report looks like.
I hope you guys can help!!!

Answer:MACRO NOVICE - Simple Auto Sort using Excel 2007

Hi Janel, welcom to the board

I think this should do the trick, I don't have 2007 here but I used your sheet in 2003

Add the following code the the Sheet1 project


Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Range("G7:J19").Sort Key1:=Range("J7"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
End Sub

To do this just right-click on the Sheet1 tab and select view Code, you will automatically be in the VBAProject.

In the right screen paste the code above.

every time you change values in the list the range G7:J19 without hearders will be sorted by Total % descending order, th epoie chart will do it's own work since it's related to this table.

Happy coding.

1 more replies
Relevance 72.98%

I'm finally getting near to the end of my mission to get my trading website online but I have one nutty task left to crack with my data, and I could do with a bit of help please.

I have about 6000 products (graphic novels - I run a comic shop) and I have painstakingly put together my related product info in a spreadsheet laid out as in the 'current layout' below. For most of the products there are 4 related products (0,1,2,3) but for some there are only 2 (0,1) or 3 (0,1,2). The 0,1,2,3 simply pertains to the order from left to right the related products are shown on the site.

What I need to do is get them into the 'required layout' shown below so I can just cut and paste them into a specific Access table that already exists in the online trading software.

However, I'm struggling with this as my Excel skills don't really go in macros and loops and whatnot. (I'm pretty chuffed with myself when I use something like concatenate!)

I get the impression this should be reasonably straightforward though, which is very frustrating. Can anyone recommend an available macro or bit of code that does this sort of thing? Or am I just missing some very obvious functionality in excel?

many thanks in advance,
CURRENT LAYOUT (this is meant to be 5 columns - Product Reference, 0, 1, 2, 3 but it doesn't want to tab nicely)

Product Reference 0 1 2 3
9780867196818 9781593073558 9781593075330 9781593077778 9781560979494
978156... Read more

Answer:Solved: Is there an available macro to do the simple rejig of my excel data I require

6 more replies
Relevance 72.98%

I'm trying to write an Excel macro that goes down a column. When it sees an empty cell, it should fill that cell in with the value of the cell that is just above it.

Thanks in advance.

Answer:Solved: Simple Excel Macro to Fill empty cells

8 more replies
Relevance 71.75%


I need to write a macro which has to automatically fill the data written in sheet1 to sheet3.This should happen row wise.

For example if the data entered in A3 of sheet1 then this data should automatically get entered in A7 of sheet 3 upon a button press.

Advance thanks for your replies.
Sandesh K R

Answer:Macro to insert data entered in one sheet to another sheet

Isn't this just a copy and paste?
You could record a macro and then edit it

1 more replies
Relevance 71.75%


I am looking for help on VBA to unhide a specific sheet to enable the macro to run, then once complete to rehide the sheet.

Code I have so far is as follows:

Application.ScreenUpdating = False

ActiveCell.FormulaR1C1 = "='W2-1'!R[9]C[3]-'W2-1'!R[9]C[4]"
ActiveCell.FormulaR1C1 = "='W2-1'!R[9]C[1]"
ActiveCell.FormulaR1C1 = "=""W2-1 - Jnl No.""&'W2-1'!R[9]C[-2]"
ActiveCell.FormulaR1C1 = "='W2-1'!R[9]C[3]-'W2-1'!R[9]C[4]"
ActiveCell.FormulaR1C1 = "='W2-1'!R[9]C[1]"
ActiveCell.FormulaR1C1 = _
"=IF('W2-1'!R[9]C[-2]="""",IRIS_JNL_W2.1!R[-1]C,""W2.1 - Jnl No.""&'W2-1'!R[9]C[-2])"
Selection.AutoFill Destination:=Range("A2:C500"), Type:=xlFillDefault
Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "A").Value) = "0" Then
Cells(i, "A").EntireRow.Delete
End If
Next i
Selection.PasteSpecia... Read more

Answer:Solved: Unhide sheet, to run macro, then hide sheet

to hide
worksheets ("IRIS_JNL_W2.1").Visible=False

to show
worksheets ("IRIS_JNL_W2.1").Visible=True

Peace be upon you

1 more replies
Relevance 71.34%

i want to use macro for my work as follows :I have selected a range to copy to another range as follows:1. move cursor down, there may 2, 3, 4 or some rows. For example 5 times to moving down cursor.2. At this cell I have to paste the selected range by hit enter3. Then, the next step is waiting my respon to moving down cursor4. select a cell and hit enter to paste.Thank you so much

Answer:Need Excel Macro To Copy & Paste

When posting in a help forum such as, please use a subject line that is relevant to the contents of your post.If everyone used a generic subject such "Macro in 2010? we wouldn't be able to tell one question from another.I have edited your subject line to make it more relevant.DerbyDad03Office Forum ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 71.34%

Hi,Please help with the following...Using a command button, I would like to have a macro copy the entire row of a selected cell, including formulas, insert the new row directly above the selected cell and clear the contents(not the formula). I also need a message box to prompt the user if they wish to proceed with adding the new row....msg "Add new Row?", if yes then run macro, if no then exit.New to VB, Any help is Greatly Appreciated!

Answer:Excel Macro to copy and insert row

Thanks for your help Derby.This is what I ended up with.Sub CommandButton1_Click()
Dim Msg, Style, Title, Response
Msg = "Add New Project?"
Style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "Add New Project"
Response = MsgBox(Msg, Style, Title)
On Error GoTo noconstants
If Response = vbYes Then
With ActiveCell.EntireRow
.Insert Shift:=xlDown
End With
Exit Sub
End If
Application.CutCopyMode = False
Exit Sub

End Sub

9 more replies
Relevance 71.34%

I am looking to create a macro to copy cell contents in A1 and paste in cell A2 without losing A2's contents. Then move to B1 copy different contents, then A1 and paste in cell B2 without losing B2's contents. Every group has different content, I need this to continue for a specific row range...


Answer:Excel Macro: Copy/Paste

that's called concatenate.
You could record a macro that does that for one line so you can see what it does, then edit it for the range you require.

BTW welcome to the forum

1 more replies
Relevance 71.34%

This is my first time using an Excel macro, so I'm a total novice. I created the macro below. It seems to work just fine, but just as it's done I get a Microsoft Visual Basic error that says:

"Run-time error '1004':
Application-defined or object-defined error"

My choices in error-window at that point are "End", "Debug" or "Help".

I need help to get the macro working without the error.

The spreadsheet contains a product number in column 1 followed by an unlimited number of colors in column 2. Each 2-character color is separated by a comma. The purpose of the macro is to copy the row as many times as needed (1 for each color) and append each color code from col 2 to the product number in col 1.

The macro follows:

Sub FixModel()

x = ActiveCell.Row
y = x + 1
cursorpos = 3
addrow = 0

Do While Cells(x, 1).Value <> " "

Do While Mid(Cells(x, 2).Value, cursorpos, 1) = ","
Rows(x).Copy Rows
Cells(y, 1).Value = Cells(y, 1).Value + Mid(Cells(y, 2).Value, cursorpos + 1, 2)
y = y + 1
cursorpos = cursorpos + 3
addrow = addrow + 1

Cells(x, 1).Value = Cells(x, 1).Value + Left(Cells(x, 2).Value, 2)

x = x + addrow + 1
y = x + 1
cursorpos = 3
addrow = 0


End Sub

Thank you for any help anyone can provide!

Answer:Excel Macro to Copy a Row in a Spreadsheet

9 more replies
Relevance 71.34%

Quick question, should be easy but I have been struggling with it.
What I am trying to do is, copy the data from Row 2 and copy it into J1, and continue down the list until there are not more records.


1 ab ab ab ab ab ab ab
2 ba ba ba ba ba ba ba ba

Then delete the 2nd row, and continue on down.

1 ab ab ab ab ab ab ab ba ba ba ba ba ba ba ba

and continue down the list until the last record.

I have attached a sample sheet.

Please help!

Answer:Excel - Copy/Paste Macro

6 more replies
Relevance 71.34%

I am trying to come up with a macro that once executed will go to a defined name, insert a row, copy what is in row one and then paste the copy in the row that was just inserted.

Answer:Solved: excel macro copy

7 more replies