Computer Support Forum

Macro in Excel that copy the visible cells and paste them into a new Outlook email

Question: Macro in Excel that copy the visible cells and paste them into a new Outlook email

Hi all,

I need to know if is it possible that a macro in Excel sends an email containing the visible cells using Microsoft Outlook.
btw, I saw some code on the internet, that saves the visible cells in a new excel file and attach it in a new email using Microsoft Outlook, but I don't want to have attachments in the email.

thanks for any possible help.

More replies
Relevance 100%
Preferred Solution: Macro in Excel that copy the visible cells and paste them into a new Outlook email

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 http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Relevance 91.64%

Hi

This is very frustrating for me but probably very easy for someone with the know!

I use excel for accounts and enter the relevant details along a row; 1 row for each transaction. I want to be able to select specific cells from that row and paste them into an Excel invoice template i have.

I have used the macro record button to do this and it works fine except for one thing; it always copies the cells from the row i created the macro from. I would like it to copy the cells from the row i have selected. Hope this makes sense.

It obviously selects the cell (c4 etc) in the code but i want it to select based on the row of the cell selected if you know what i mean.

Here is the basic code i have
Sub CreateInvoice()
'
' CreateInvoice Macro
' Macro recorded 01/09/2007 by User
'
' Keyboard Shortcut: Ctrl+i
'
ChDir "X:\Accounting\Customer Accounts\Invoices"
Workbooks.Open Filename:= _
"X:\Accounting\Customer Accounts\Invoices\Blank Invoice.xls", UpdateLinks:=3
Windows("Accounts September 2007.xls").Activate
Range("B8").Select
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("B4").Select
ActiveSheet.Paste
Windows("Accounts September 2007.xls").Activate
Range("C8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.H... Read more

Answer:Excel copy & paste Macro for selected cells

16 more replies
Relevance 88.74%

Hi all,

I really need help with this, as I have tried means to get it right, but I have no experience in VBA and it seem difficult. Any help will be greatly appreciated.

I need to copy specific cells in a form (in Excel format) that is input by users and paste them into the summary workbook.

The form is fixed, but every time someone sends in a new form, I'll need to update the data in a summary workbook. So it'll be constant updating and I need to ensure that a new row in the Summary folder is used for each form that is sent in.

I'm trying to write a macro that is able to automate the data transfer.

Eg. I need to:

copy the data from D6 in the file Form to the celll A2 Summary file,
D7 in Form to B2 etc.
Attached is the form and my summary sheet.

Thanks in advance!
 

Answer:Help Needed for Excel Macro - Copy specific cells and paste into a Summary workbook

Hi all,

Below is the vba code that I have written.
But the problem is I am not sure how to define the destination workbook as a file, which is also where this macro will be stored.

It does not seem to work when I tried to put ThisWorkbook or Activeworkbook. I did not want to add a new workbook either.

Sub copyWorkbooks()
Dim MyPath As String
Dim SourceRcount As Long, FNum As Long
Dim mybook As Workbook, DestWks As Workbook
Dim sourceRange As Range, destrange As Range
Dim rnum As Long, CalcMode As Long
Dim SaveDriveDir As String
Dim FName As Variant

' Set application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
SaveDriveDir = CurDir
' Change this to the path\folder location of the files.
ChDirNet "C:\Documents and Settings\chinba\Desktop\ASL Exception\"
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
MultiSelect:=True)
On Error Resume Next

'find the last row
RDB_Last = rng.Find(What:="*", _
after:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
If IsArray(FName) Then
'Loop through all files in the myFiles array.
For FNum = LBound(FName) To UBound(FName)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(FName(FNum))
On Error GoTo 0
If Not mybook Is Nothing Then
On Error Resu... Read more

1 more replies
Relevance 78.59%

Hi there,

may somebdoy please advise (I am pure newbie at Macros):
*I want to create button (I can dot that) and assign Macro to it:
*Copy selected cells (C3:N3)
*Paste values against respective product code (product code can be changed manually in A2)

It happens in the same working sheet (or not a problem if haapens in another sheet)

Many thanks in advance (this would save me some time and efforet)

SM
 

Answer:Solved: Copy selected cells to chosen cells in Excel with Macro

14 more replies
Relevance 104.55%

I've developed a Timesheet for my staff to use and I've acquired some VBA to copy the worksheet within a workbook and paste it into a new worksheet so that we can look back from one pay period to the next. I would like to include in my VBA to copy and paste to also move a staff members New balance for Sick Time, Comp Time, Vacation Time, etc... to the Previous Balance column. Basically, in the process of copy & paste, i'd like to move the date in G25 to D25, G26 to D26 and so forth. Here is the VBA that I'm using to copy and paste the worksheet. Sub Test()Dim ws1 As WorksheetSet ws1 = ThisWorkbook.Worksheets("Timesheet")ws1.Copy ThisWorkbook.Sheets(Sheets.Count)End SubI would appreciate and assistance you can provide.

Answer:How can i copy and paste cells using VBA in excel

Sub Test()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Timesheet")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
ws1.Range("G25:G30").Copy
ThisWorkbook.Sheets(Sheets.Count - 1).Range("D25:D30").PasteSpecial _
Paste:=xlValues
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

14 more replies
Relevance 103.73%

I have a bunch of Excel cells that are spread out across several columns.

Is there a quick way to "move" these cells so they are all in the same column, with different Rows instead?
 

Answer:Solved: copy/paste excel cells

9 more replies
Relevance 102.91%

Scenario:
Excel spreadsheet 1 with 'X' number of lines in it.

Goal:
Create a macro in sheet 2 that will copy a formula from line 2 (in this same spreadsheet) to line 'X'. I am simply doing a reformat in sheet 1 using some simple formulas so I can get this into a format to import.

Example:
First, determine number of rows in spreadsheet 1. I think this can be done with the counta function. I am trying to set a variable up that holds this value. This is what I have in the macro currently to do this:
Dim x
x = "=COUNTA(CSEXPORT!C:C)-1"

I am hoping that this set x = 10.

Second, I need to copy line 2 in spreadsheet 2 down 'x' rows.

I have tried relative positioning thinking that would work, but the macro still specifies specific cells. I thought I could use EndDown, EndUp to do what I needed, but it still puts in specific cell values.

The only thing that I need to know is how to use the variable in the VBA script. I don't know how to tell it to go to cell A(x). I have attached a spreadsheet with a rough example. The macro within this sheet is not completed.

Thanks
 

Answer:Excel Macro that will copy formula 'X' cells

6 more replies
Relevance 101.68%

Need some help sorting a large data output to different sheets based on Column A value, seems like this should be easier than what I am doing but I can't get it to workData coming in example
A C H I
4 Dept Product Code Sch # Prod #
5 DeptA
6 DeptA 101 12 12
7 DeptA 102 34 33
8 DeptB
9 DeptB 201 10 11
10 DeptB 103 23 25
11 DeptB 301 45 40
I want to copy this data to Existing Sheet Named "DeptA"
A C E
3 Prod Code Sch# Prod#
4 101 12 12
5 102 34 33
and then same thing for "DeptB" sheet
A C E
3 Prod Code Sch# Prod#
4 201 10 11
5 103 23 25
6 301 45 40

They can be kept in current order, if Product Code is blank skip to next line, if Sch # is blank skip to the next line, copy Prod# whether it is empty or notThanks

Answer:Excel Macro to copy cells in a col based on another cell

Please click on the following line and then repost your example data after reading the instructions found via that link.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 101.68%

Hey all,

Here's my situation, any help is greatly appreciated:

I have a list of 13 items in one column. A couple of the cells will be hidden by the macro, but then what I need the macro to do is copy the first 10 unhidden items onto the clipboard.

What row(s) will be hidden will vary from week-to-week. If I were to simply have the macro copy a cell range, there would be a chance of it copying a hidden cell. I believe what I need is a way to have the macro do a count of how many unhidden cells it has selected, and stop when it reaches 10.

Forgive my ignorance but I am literally just picking up Visual Basic as of this week.

EDIT: Just to clarify, I have the hiding of the cells taken care of. I just need some help figuring how to copy the top 10 cells that aren't hidden.
EDIT 2: Found that I could delete the hidden cells. Worked perfectly.
 

More replies
Relevance 100.45%

I have searched for a similar issue but was unsuccessful. I need to create a macro to do what will surely be a miserably repetitive job of transposing data for a whle bunch of files. I need to get into each excel file and copy certain data fields from column B (B5-B8 turquoise, B13-B15 bright greeen, B23-B27 bklue, B29-B30 pink, and B35 tan) into sequential cells in another file into a single row under a heading A2 - O2. I will need to do this several hundred times, open a file, copy the fields and all the files selected data from a column will wind up in a single excel data sheet in columns.

I thought I would just do a careful record macro, but cannot get 2007 to record beyond my first cell copy. I have named both files xlsm and changed settings to no avail.

The source file is attached and called Source and Detsination is called destination. Any help or a macro would be greatly appreciated!!attached the source file (Source ) I collor codes the field to be copied in the source as indicated aboue with the header fields in the destination if that helps. Thank you VERY much!!
 

More replies
Relevance 100.04%

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.

ie.

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 100.04%

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...

Thanks!!!
 

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 100.04%

Is it possible to make a macro that copy predefined cells in a selected row into new row/cell ?
For example : I select row 4 push the 'macro button' and it copies A4, B4, C4, D4 and E5 to D21, D22, D23, D24 and D25.

If this is possible , could someone please write me an example to this and if not write and example how this could be done ?

In advance , thanks!

-risanger
 

Answer:Excel copy and paste macro

Hi, welcome to the forum.
In principle, everything is possible with a macro as long as the code is correct.
You could start by recording a macro yourself and follow the steps that you have explained above.
Once you're finished you stop the macro recording and take a look at the code.
This will show you more or less what it could look like, the only difference being that recoding a macro translates the coordinates to an offset R1C1 or similar instead of select Cells(2,3) (that is row 2 column 3) which is the same as Range("C2")

Try this out and see if you can work it our from there.
One important thing you should remember that you should always mention which version of Excel you're using,just a question of good habbit and complete information.

Just holler if you get stuck
 

1 more replies
Relevance 100.04%

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 computing.net, 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 99.63%

I widened some cells in sheet1. I highlighted the cells I wanted to be pasted onto sheet2, but the cells became the default size (before I widened them in sheet1). How do I copy and paste EXACTLY the SAME appearance meaning the size of the cells from sheet1 to sheet 2?

Thanks
 

Answer:Solved: copy and paste cells from one sheet to another with exactly same outlook

6 more replies
Relevance 98.81%

Hello guys!
I have a problem, and although I thought the solution was simple, it turned out to be not at al.

I have an excelfile, it contains multiple worksheets, (approx 20)
All these worksheets have a different layout, so the columns have a different size.
When I make a report I have to copy every single worksheet in excel and then have to paste them one by one as a picture in word. This takes a lot of time ofcourse..

So I thought let's make a macro.
I started to paste all the data of the different worksheets into one worksheet and then I could use 1 simple paste and copy into word and voila..
But this didn't worked out because the columns have a different size, so some of the text became unreadible.

Then I made a macro which copies one worksheet and then puts it into a new document in word and saves it, that worked out, but I don't know how to write the code in order to make the macro, after it has copied the first worksheet and pasted it into word, to make it copy the second worksheet and paste it into word and so on for all the worksheets

This is the code to make it copy ONE single worksheet and paste it into word,
but could someone please help me with the code so that it also copies the second worksheet and pastes it into the same word file?
Code:
Sub proWord()
Dim varDoc As Object
Set varDoc = CreateObject("Word.Application")
varDoc.Visible = True
Sheets("Rapportgegevens").Range("... Read more

Answer:(Macro?) Copy Paste Excel to Word

9 more replies
Relevance 98.81%

Hello,

I know nothing about programing an excel Macro but I need help createing a macro to do the following:

file 1
col a ffffcol b
1 ffffffffff48
2ffffffffff 26
3ffffffffff 53
4 ffffffffff8

File 2
col c fffffcol d
1
1
2
3
3
3
4

I want to fill the rows in col d in such a way that 1 coresdponds to 48 and so forth.

Also, the file has over 7000 rows of information so doing it by hand is not a viable option
 

Answer:Solved: Excel Copy paste macro

6 more replies
Relevance 98.81%

I am trying to format a play list for a flash player. The first sheet is a list of songs from 1960. The 2nd sheet is a list of commercials/drops. After every 4 songs, I want to insert a drop from the drop worksheet. I need a macro that insert the row and paste the drop row. Once it has cycled through the drop list, it needs to start over from the first drop. The number of songs and drops vary per year.
The only thing I have found is a way of inserting the rows. I have no clue how to format the rest and hope someone does. It will save hours from having to do it manually.
Do While Not IsEmpty(ActiveCell)
ActiveCell.EntireRow.Insert
ActiveCell.Offset(5, 0).Select
Loop
 

Answer:Excel Macro Insert/Copy Paste

11 more replies
Relevance 98.81%

I have created several spreadsheets representing a dog's pedigree and statistics. What I would like to do now is be able to choose 2 dog's spreadsheets, and then copy and paste both of their pedigrees/data into a new spreadsheet to evaluate a test breeding between the 2 dogs. I No calculations are needed, I just need a way to create an organized and temporary way to present the data for both dogs.All the worksheets are identical in structure. The data is arranged in blocks. I know where I would like each block of data to be positioned on the new 'Test Breeding' worksheet. Unfortunately, I have no clue how to do this. Any assistance would be greatly appreciated!

Answer:Need Excel Macro: Copy/Paste to new Worksheet

Keep in mind that we can't see your spreadsheets from where we're sitting, so it's kind of hard to give a specific answer.It sounds as if VLOOKUP would work, except the VLOOKUP doesn't work across multiple sheets. However, there is User Defined Function at the Ozgrid site that will do VLOOKUP's across all sheets in a workbook.Are you ready for a interesting coincidence? The example they use is:=VLOOKAllSheets("Dog", C1:E20, 2, FALSE) As I said, not knowing the layout of your sheets, but using the fact that you said that all sheets are laid out the same, I'm thinking that you might be able to use this UDF to accomplish your task.Let us know if that helps.

3 more replies
Relevance 98.81%

I am very new to VBA and have a scenario that I could use some assistance with.

At work, I have 300 individual excel files, and I need to copy certain cells from each of those files and paste into a summary file. Since it is extremely time-consuming to do this manually, I was wondering if there was a macro that could open the file, refresh pivot tables (I have formulas in the 300 workbooks which are linked to pivot tables in the summary file), copy cells and paste values in the summary file (in the next available row, not to over-write previous data) and save workbook. Then repeat this process for the rest of the files in the folder. I would prefer not to have to write specific file names, as there are 300 of them and growing.

I am using Excel 2007.

I have attached the Summary file and 2 property files as examples. I need to make sure that the formulas in the Qtr1 & Qtr 2 workbooks (column D) are updated with the pivot tables in the Summary file. Then I need to copy cells B2727 from 'WS' worksheet and paste values to the next blank row in the range P2:R4 in the Summary file, and also copy cells B3131 and paste values to the next blank row in the range S2:U4 in the Summary file. Then save property file and close. Then open next file in the folder and repeat.

Please let me know if you need more info. I appreciate any help or feedback!
 

Answer:Help with Macro to copy & paste between Excel workbooks

Hi, welcome to the forum.
The Qtr1 & Qtr2 workbooks column D are unclear to me, but the B2727 and B3131 okay.

I put in a vba module with a macro and a funtion the macro Update will ask for confirmation of the Excel sheet and if you answre yes it updates the date.

Nothing with the pivot tables just the two rows into P-R and S-V

test and tell me if this is a beginning of what you need.
 

1 more replies
Relevance 98.81%

This is my 1st time. I am trying to obtain the macro code for my spreadsheet. I want data to be copied from worksheet TASKS to worksheet COMPLETED based on data entered in a cell. Both worksheets have the exact same layout.Utilizing Rows A-H. I would like for all data in row to be deleted from TASKS tab and moved to the COMPLETED tab (under the same section) if Column F equals 100%. See example of layout below (have to use .... to seperate).Tab = TASKS (to be copied & deleted from)Task..................................Start Date.........Proj Compl.........Actual Compl..........%........Resource Req.............Notes Section = MEETINGS Name of Meeting..............02/08/10...............02/09/10.............02/10/10.............100%.......None.......................NoneTab = Completed (data to be moved to - under the same section) Section = MEETINGSAlso...If I need to insert rows in the future for new tasks, will the macro apply to these?Hopefully this makes sense.

More replies
Relevance 98.81%

Hello,I am trying to do the following - I have a data sheet with about 100 rows, and would like to write a macro where it copies a selected row and inserts /pastes directly underneath it a specified number of times.e.g. user selects row 5 and wants that data to be repeated 17 times underneath it.Any help you can give would be great,thanks, Ricky

Answer:Macro to copy, paste, insert in Excel

Sub InsertRows()
'Make sure user has selected 1 entire row
If Selection.Cells.Count <> Selection.Resize(1).EntireRow.Cells.Count Then
MsgBox "Please select 1 entire row, then rerun macro."
Exit Sub
End If
'Get number of rows to insert from user
getNum:
myCopyNum = Application.InputBox("How Many Times Do You Want the Row Copied", Type:=1, Default:=0)
'Make sure sneaky users can only enter integer values
If Int(myCopyNum) <> myCopyNum Then
MsgBox "Enter Integer Value or Click Cancel"
GoTo getNum
End If
'Quit if Cancelled
If myCopyNum = False Then Exit Sub 'Copy and Insert Rows
Selection.Copy
Rows(Selection.Row + 1 & ":" & Selection.Row + myCopyNum).Insert Shift:=xlDown
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 98.81%

Hi

I'm really new to writing code and I'm trying to develop some stuff in Excel 2007.

I want to record a macro for a command button that when clicked, will copy the data from Cell A6, and past it with formatting and everything into whatever cell I selected.

I can get it to copy and paste, but only into a certain cell.

Help required!

Thanks

C
 

Answer:Solved: Excel Copy and Paste Macro

MacShand, welcome to the forum.

Try This:
Code:

Range("A6").Copy
ActiveCell.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False

What the code does is it copies the contents in cell A6 to whatever cell you have selected at the present time. So, make sure before you run the code the correct cell is selected or you may copy over needed data.
 

3 more replies
Relevance 98.81%

Referencing cells: I have a spreadsheet with sales tax in D1, net amount in D2 and the total in C3 and the name in B3. What I am trying to do is copy and paste the contents of C3 to C1 & C2 and repeat for contents of C6 to C4 & C5 and so on until all rows are processed
 

Answer:Copy, Paste, Repeat macro in Excel

Sub test()
For Each Cell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If Cell = "" Then
Cell.Value = Range("C" & Cell.End(xlDown).Row)
End If
Next Cell
End Sub
 

1 more replies
Relevance 98.81%

Hello,

I am totally a beginner with VBA so I hope you guys could help me!

I have a collection of data in excel in which I should search for a certain "name". Once the name is found, the macro should be able to select the entire row of the match and then copy to another sheet (could be in the same workbook).
I try to use the code described on this topic (already closed): http://forums.techguy.org/business-applications/703562-search-macro-excel-2003-a.html

However, my problem when using this code is that it is only copying the first "occuring" of the "name" and in a sheet I have, for example, 42 occurences for a name "example" and I want to copy all of them, not only the first.

So, could you help me please?
I am using winXP with excel 2003.

Thank you,
Agatha
 

Answer:Find, copy and paste macro in Excel

16 more replies
Relevance 97.99%

update on the below question:I think something is wrong when I opened the .csv file.If I leave the file open then run the macro, it works ok. If I open the file within the macro, I get the format problem.Any advice?Thanks!Hi,

Please can someone give me some advice on this?

I am writing a vba code in Excel 2003 to copy from a source .csv file and paste into xls file.

First, I tried this using macro recording, it worked fine. so I recorded the macro and tried to apply it. Something strange happened.

One of the columns is date, and originally is format Date (*01/01/01). If I manually copy and paste, the format is fine. If I use the macro, some (and only some) of the cells in this column will change format to General and align to the right, while the others keep the original Date format and align to the left.

How did this happen?

I viewed the .csv file using Notepad, and cannot see any difference between these dates that end up in different format.
And if I manually copy/paste, there will be no format issue at all.

Please can someone help?

Thanks!
 

Answer:copy/paste in excel macro changes cell format

upon a closer look, i think I found some clue, but still far from a solution, here is what I observed.

for date 09/07/2006, excel interpret it as Sept 07 06
for date 20/06/2012, excel interpret it as June 20 2012

please note the year format is different!

any advice pls?
 

1 more replies
Relevance 97.99%

Hi everyone!So I was wondering if there was a way to do the following. Saying I want to make monthly reports for a country (Italy) and two product categories (Juice & Water) but the worksheet containing the data is massive and I have to do that every week. The rows look something as following:Country - Product - OtherstuffWould there be a macro in the way "If Italy and Juice" cut paste whole row in a different workbook? Cheers!message edited by Odysseus

Answer:Excel Macro for conditional Copy/Paste for whole rows

Since you still haven't told me how you want to pass the country names to the macro (even though I've asked twice) I once again have make an assumption. This code will work for the criteria that you posted in Response #8 since I assume that you want the country names hard coded into the macro.The code creates an Array with the county names that you provided and then loops through the array searching for each element.(Note: This code still copies the rows to Sheet2 of the same workbook. I'll leave it up to you to modify it so that it copies the rows to your other workbook. Practice is good.)
Option Explicit
Sub CopyData()
Dim c As Range
Dim firstAddress As String
Dim nxtRw As Long
Dim cNum As Integer
Dim Country_Arr() As Variant

'Build array of Country names
Country_Arr = Array("Italy", "Slovakia", "Switzerland")

'Loop through Country names Array
For cNum = 0 To 2

'Search Sheet 1 Column A for array elements.
With Sheets(1).Columns(1)
Set c = .Find(Country_Arr(cNum), LookIn:=xlValues)

'If found, Check Sheet 1 Column B for Juice or Water
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Offset(0, 1) = "Juice" Or _
c.Offset(0, 1) = "Water" Then

'If True, determine next empty Row in Sheet2, copy Row Values from Sheet1
nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
c.EntireRow.Copy
Sheets(2).Range("A" & nxtRw).PasteSpecial Paste:=xlValues
End If... Read more

14 more replies
Relevance 97.99%

Hi,

I would like to be able to input a number into a cell B3 on sheet 1 - 'Head Count'. Then get the macro to look up the number that was input and copy and paste all the rows relating to it from sheet 2 - 'Data' and paste them on 'Head Count' next to the input cell.

I am having trouble with getting the macro to copy and paste depending on the target cell. This is what i have so far:

Sheets("Data").Select
'Copy cells of Data from rows containing cost centre in col G of the active worksheet (source sheet) to cols
'of Head count (destination sheet)
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Head Count")

Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
sCount = 0
dRow = 1
For sRow = 1 To Range("G65536").End(xlUp).Row
'use pattern matching to find "Significant" anywhere in cell
If Cells(sRow, "G") Like "Worksheets("Head Count")Range("B3")" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols

' With Range("A14:AA14", Range("A65536").End(xlUp))
'.Paste

Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
Cells(sRow, "B").Copy Destination:=DestSheet.Cells(dRow, "B")
Cells(sRow, "C").Copy Destination:=DestSheet.Cells(dRow, "C")
Cells(sRow, "D").Copy Des... Read more

Answer:Excel macro copy and paste depending on a cell

Can you upload a workbook with dummy data so we can see how you've structured the data.
 

1 more replies
Relevance 97.99%

Hello everybody,I would be really happy if someone helps me with this macro. I have 4 columns: A, B, K, L.A and B are full of data (thousands of rows) in K there are key words (about 10 but this number vary). I need macro, which will check word in K (for example K3) and then it will run through column A and if there is match (e.g. A200) then it will copy data from cell next to it (B200) and then it will paste it to L column (L3). Afterward it checks K4, K5....This would save me literally hundreds hours of my time. Thanks a lot!

Answer:Excel macro - copy/paste if there is match in text

Have you tried a =VLOOKUP() function?MIKEhttp://www.skeptic.com/

5 more replies
Relevance 97.99%

Hi. I am trying to run a macro to copy items on 1 sheet and have it paste them on the 2nd and 3rd sheet. I am trying but can't quite seem to get the excel code to do what I want. I can't post a copy of it because I accidentally deleted it.

Can anyone help me?
 

Answer:Solved: Excel: Copy/Paste Macro Code Help

9 more replies
Relevance 97.99%

Hi,
I am trying to sort the colored rows, copy and paste it in particular format.
I have attached a sample excel file.
1. A cell has values which are Purple in color.
2. B cell has values, yellow, Blue and White in color.
3. I want concatenate A-Purple, B Yellow and paste it in next work sheet "Result"- Col2
4. Blue rows need to be copied and pasted to Col2
5. White row need to get pasted to Col4
6. Want to repeat this for whole worksheet.
Can you please suggest a macro for this?
Thanks,
 

Answer:Need excel Macro for copy paste colored rows

7 more replies
Relevance 96.76%

I have two worksheets within the same workbook. WkSheet "Data" as my source and wksheet "Form" that I need to populate from the source.

I need to be able copy the quantity from my "Data" wksheet along column A, starting with A5 to my "From" wksheet in column A, starting with A9.

I need the quantity from the 'Data" wksheet to be pasted to the next open cell along the column A in my "Form" wksheet.

Can someone please help!!! Thanks.
 

More replies
Relevance 95.94%

Hello Everyone,

First time user of the forum here and it does seem everyone is very helpful! I did a search and could not find search macro for a list of values so I'm adding a new post. My apologies if this was answered before but hopefully someone can point me in the right direction.

Here's what I have:

I have 3 worksheets:
1. List of Search Criteria (List of 100 or so countries in column A)
2. Huge Data Set (Sales Data; column K, L, or M will contain country name)
3. Blank Output sheet

My goal is create a search button that will look in the Huge Data Set for sales transactions occuring in the list of countries specified on Sheet #1. The country info could be in any of the 3 rows (K, L, M) on Sheet #2. I would then like any row on the Huge Data Set with a matching country to be copied to the 3rd worksheet.

I hope my explanation makes sense.

Any help?

Simon
 

Answer:Solved: Excel Macro - Search List of Values, Copy, and Paste

13 more replies
Relevance 94.71%

Hi all,

I'm having trouble writing a macro that'll help me automate this process. In the attached, I have a list of countries in the Countries tab, and 10 line items in the Data Set tab. I want to look for each of the countries in columns K to M in the Data Set tab, and for each line that hits, I want the macro to copy that line and paste it in the Output Sheet tab. Then the macro should go down the list of countries and repeat this process until it finishes looking for the last country in the Countries tab.

I'm thinking some kind of For loop is required, but any help would be greatly appreciated! Thanks!
 

Answer:Solved: Urgent: Excel Macro - Search List of Values, Copy, and Paste

9 more replies
Relevance 91.02%

Hi all,
This is a great board-- I have received a lot of help from here!
This is my next project.
Open the attached file and, on the right of the form, there are six cells with text.
I need to concatenate these with one space in between each one, then copy the group of them and use the clipboard to paste so I can open the "save as" dialog box and paste it as the filename. We need this in our environment to be able to create the file name quickly, paste it and save the file.
I want to be able to do this with a button that activates a macro.
You can see the button on the right side when you open the file. The macro assigned to it called "createfilename". The code is in module 1.
I got it started, but I know it needs help.
Thanks!
 

Answer:Solved: Excel macro to concatenate and copy cells for pasting to "save as" dialog box

7 more replies
Relevance 90.2%

Hi

I am wondering if there is a simple macro that would allow me to format cells throughout a workbook based on whether they contain an input (ie number/text etc) vs. a calculation (anything with a formula).

Basically I'm looking for an automated way to colour my inputs in blue font and calculations in blank font across all worksheets in a workbook.

Any ideas would be great.

Thanks
 

Answer:Excel macro to format input cells vs calculation cells

I wanted to point out that this can be done without using a macro. In Excel you can click EDIT >> GOTO >> SPECIAL and put a check in the option labeled "Formulas." After clicking OK all your formula cells should be automatically selected on the sheet. Once all cells have been selected you can change the font, background color, etc. for all the selected cells at once. If you prefer to use a macro instead you can try the code I provided below.

Code:
Sub FindFormulas()

For Each vcell In ActiveSheet.UsedRange

If vcell.HasFormula = False Then

vcell.Font.ColorIndex = 5

Else

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Replace with your code to execute if cell contains formula
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End If

Next vcell

End Sub

Regards,
Rollin
 

1 more replies
Relevance 90.2%

Hi ,

I have the code for triggering an outlook email, if i click on a button . I have attached the code for this in the bottom of this message . I would like to add one more functionality for this code . I want to copy several columns in the excel file and paste it in the email body . The excel file is attached for your reference. I would like to know , how to copy the contents of the excel worksheet and paste it in email body. please help.
Sub GenerateEmail()
Dim olApp As Object, olMail As Object
Dim ws As Worksheet
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If
Set olMail = olApp.CreateItem(0)
olMail.To = "[email protected]"
olMail.Cc = "[email protected]"
olMail.Subject = "Macro for mailing"
olMail.Body = "Test Macro" & vbCrLf & vbCrLf & "See our double space?"
'olMail.Attachments.Add strFullPathNameHere
olMail.Display

End Sub
 

Answer:How to copy the contents of excel and paste it to email body ?

6 more replies
Relevance 88.15%

I am trying to copy and paste some email addresses from an email I received. This person did a mass email (and did not use bcc). I would like to add the same addresses to my address book. Thanks!
 

Answer:Outlook - Copy and Paste Email Addresses?

As long as the addresses are showing up as links in the original email, you should be able to right click on them, one at a time, and choose to "add to address book".

Hope this helps!
 

1 more replies
Relevance 87.74%

I am in excel and have an incident record number that I want to search on in Outlook. I click on the cell in excel that has the record number. I go up to the formula bar and copy (hit control C), then I go to Outlook in the folder I want to search in. I go to the search bar and hit control V or try to right click and hit paste. Either way, nothing appears.

Answer:I can't copy & paste text from Excel into Outlook search

While you are waiting for further advice, with the same reference please confirm that you can copy it into Notepad or Word using Control V.Always pop back and let us know the outcome - thanks

6 more replies
Relevance 87.33%

I am using the MSN Stock Quote addin with MS Excel 2003. Prior knowledge of this should not be necessary, but I just thought I'd provide full context to my issue. I wrote a macro that essentially records daily stock prices. Because I don't want to store old quotes into perpetuity, i need to ensure that the update quotes button has been hit. I use sendkey() to accomplish this, and then the macro copies the quotes and pastes them elsewhere. My main goal is to have the task scheduler open this file daily so i dont ever have to think about this again, and i'm so close. Essentially my problem is that it takes a few seconds after the update button is hit for the add-in to fetch the new prices from the internet, and this process appears to be delayed until the macro is finished. This is a problem because the macro ends up recording un-updated quotes. I have tried the Application.Wait procedure, and I've tried time consuming loops, do while loops, etc. All of these approaches result in the same thing: Can someone please help me with this? I'm too lazy to hand update this spreadsheet every trading day for the rest of my life (or until i quit). Thank you in advance!
 

Answer:Excel Macro - Allow cells to update query before macro completes

Can't you just put the update macro code in a module of it's own and call it from the main macro?

Regards,
Rollin
 

1 more replies
Relevance 87.33%

I've seen this a few places on the web but have fond no answer.


XP, MS Outlook and Word 2003

Dell Laptop Inspiron ET505


I list a name and address n the TO field, ie, john doe <[email protected]>

I copy the whole john doe <[email protected]>

I paste it into the text box or a word doc and get only "john doe"


I've been told that if I make my Outlook display name = john doe <[email protected]> it would then copy the whole thing (including email address). It doesn't.



I once read that Outlook doesn't recognize < >. I use Yahoo for personal mail and I often need to transfer there, so prefer to keep my emails in this format.



I know you can go to "properties" and gather the email address (all the posts say this), but if I click on the name in the To field, the "Email Properties" gives me a display name that does NOT include the email address--though the display name in the address book does. I like to copy both the name and the address at once and this takes two steps. If I go to the address book, hit Properties, then, yes, I can get the full john doe <[email protected]>. That is obviously cumbersome for multiple names and I find this an unacceptable solution.


Surely some computer whiz has bypassed this asinine malfunction and created a program that allows you to copy an email address in a mail program!! To say it's like creating a car w/o a starter motor ... Read more

More replies
Relevance 86.92%

Hi there,

I have to prepare several daily emails which are prepared using a range of data (for example B3122) from Excel 2003 and then have to be sent using Outlook 2003. Unfortunately, due to various management requirements I am unable to merely attach the Excel file as an attachment or paste the Excel data into the email as a straight paste - I have to paste as Unformatted text and then manually change various lines of the email to Bold and Underlined text.
I am trying to write a macro that will do this automatically but am unable to get the Excel data pasted into Outlook (as unformatted text or otherwise). I am assuming that for the font changes to bold I will have to use a Find function in the macro.

The code that I have got so far is as follows:

Range("B3122").Select
selection.Copy

Dim olApp As Object, olMail As Object
Set olApp = CreateObject("Outlook.Application")

Set olMail = olApp.CreateItem(0)
olMail.To = "[email protected]"
olMail.Subject = Range("B3")
olMail.Display

I am new to VBA and so I may be missing a really obvious solution but after trawling the net I have been unable to find an answer.

Many thanks to anyone that can help and make my work a lot less tedious!
 

More replies
Relevance 86.51%

I'm working in a ticketing system that requires me to copy and paste emails along with headers and whatnot to tickets. Right now I click "reply" and copy and paste and close the 2 windows. I was wondering if there's a way that copies all the data of an email into the clipboard to save myself a considerable amount of time on a day to day basis.

Answer:Outlook 2010 macro to copy email to clipboard

Copy email to the clipboard with Outlook VBATony

5 more replies
Relevance 85.69%

Hi , i am working in finance and mostly with numbers .. i have two sheets the 1st sheet includes two columns [order number] and [value] order no. aren't sequentially and each order no. has unique value and both columns already filled... and the 2nd sheet have same columns but only contains a randomly group of [Order Value] and [value] column is empty and i need to fill each value of order no. from 1st sheet ...

Answer:Excel : How to copy specific cells if 2 others cells matched

Without knowing how your spreadsheet is arraigned, Column Letter & Row Numbers,the best I can offer is a general suggestion of using one of the =LOOKUP() functions,like =VLOOKUP() Somthing like:=IF(VLOOKUP(A1,SheetName!A1:B10,2,FALSE)Where cell A1 is your unique value.MIKEhttp://www.skeptic.com/

3 more replies
Relevance 84.87%

Is it possible to get the maximum value of data of only the visible cells on a worksheet?
 

Answer:Excel MAX and Visible Cells only question

13 more replies
Relevance 84.46%

I would like to ask for an Excel macro which can send an automate email on a certain date every Month.

The dates would be as follows:

1st Monday: Email should be sent to [email protected] automatically
2nd Wednesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically

Subject line: ABC123D5679_Data transfer expected today

Details of the body of the text message:

"Dear All,
This is to remind you that we are expecting the data transfer to be posted in Novartis system today . This is just an advanced intimation.

Kindly let us know if you foresee a delay in the data transfer.

Kind regards,
Rohit"
Many thanks in advance for your help guys. This means a lot.
 

More replies
Relevance 84.46%

I have a workbook with several worksheets. One is called BaseBid. I need a macro which will look at the values in columns J and K. If there is an "x" in column J, I want to copy the values from columns C and D of that row into the last row of a sheet called SL. If there is an "x" in column K, then I want to copy the values from columns C and G of that row into the last row of the SL worksheet. The data on the BaseBid sheet starts in row 6.I have two macros which almost do what I want. The first copies the values from the right cells on BaseBid to SL. But it does not go through the whole BaseBid sheet, which I need it to do. The second one uses a loop, and it does go through the whole spreadsheet, but it is copying the entire line instead of just the cells I need. Can someone please help? I am pasting the code for both macros below.Sub InsertSL()Dim LastRow As Long Dim rng As RangeLastRow = Last(1, rng)rng.Parent.Cells(LastRow + 1, 2).Value = "=BaseBid!RC[1]" rng.Parent.Cells(LastRow + 1, 4).Value = "=SUM(BaseBid!RC[2],BaseBid!RC[5])"End SubSub test2() Set a = Sheets("BaseBid")Set b = Sheets("SL")Dim xDim zx = 1z = 6Do Until IsEmpty(a.Range("I" & z))If a.Range("J" & z) = "x" Thenx = x + 1b.Rows(x).Value = a.Rows(z).ValueElseIf a.Range("K" & z) = "x" Thenx = x + 1b.Rows(x).Value = a.Rows(z).ValueEnd IfEnd Ifz = z + 1LoopEnd Sub

Answer:Macro to copy certain cells in row

There must be something you aren't telling us.What is this Last function that you are using?Where are you telling VBA what rng is?LastRow = Last(1, rng)

9 more replies
Relevance 84.46%

Pls tell me how to copy and paste the text of multiple cells in excel which are in regular intervals in one instance. for eg. i want to copy the text found in cells A5, A14, A23, A32, A41, A50, A59 etc message edited by Rinno

Answer:how to copy and paste multiple cells

I think we need a little more information related to what you are trying to do.You can simply hold down the Ctrl key as you select your cells and then Copy/Paste them. However, for some reason I doubt that you are looking for that simple an answer.What exactly are you trying to accomplish?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

4 more replies
Relevance 84.46%

is there a quick and dirty macro/formula to select a named range (i named them), copy it and then paste to a specific location?

thank you ahead of time...
Version of the program - excel

What you want it to do - look at cell e4, goto tab "info" and find correct named range of cells, paste range starting at cell e6

Cell references, bookmark names, column letters, row numbers, worksheets, styles, whatever pertains to the information at hand

Sample data - insurance commision info, based on product
 

Answer:Copy/Paste Range of Cells

11 more replies
Relevance 83.64%

I have a worksheet with cells that may contain text that sometimes exceeds the width and height of the cell. If I don't notice it and adjust the row height, the text shows up as cutoff. Does anyone have a VBA snippet or tool that can locate these cells so I can adjust them? Thanks!

Answer:Excel Find Cells Where Text not Visible

Hi, Try this:Select all the cells on the worksheet.Right-click and select Format cells...Select the Alignment tab.Check the 'wrap text' box.The row height should adjust for changes in the amount of text in the cells.Regards

3 more replies
Relevance 83.64%

EXCEL Question:

I am needing to copy cells from one tab to another (and only copy cells with data) using a Macro.

Tab "Checkout" has data starting in cell A3, B3 and C3. This data could have one or more rows/columns.

I am wanting to copy this data into the "Database" tab. If there is something already entered on row, I need it to go to the next to insert the copied data.

Any and all help is greatly appreciated!
 

Answer:Copy only cells with data into another tab using a Macro

Hi, welcome to the forum.
Do you have some knowledge of VBA? If so it's quite simple.
Record a macro to copy one row from one sheet to the other.
Stop recording after that.
Open the vba editor Alt+F11 and take a look at the macro code.
If you do not have vba knowledge, then it's another matter.
Question: what happens with duplicate numbers in column B?
I don't think you just want to copy everything from sheet 1 to sheet 2?
Maybe a little more background information?
 

1 more replies
Relevance 83.64%

Hello everyone,

I am new here and I can't find the right answer to my question.

I am making a sheet where people can enter their medicin costs.
Each medicin costs overview has 13 rows.

Now I would like to add the function, that when there aren't enough of these overviews, people can add these 13 rows in this page. So beneath a new overview should be inserted.

Now I tried several things, but every time the cells are pasted in the same cell (for example A42:H55)

What kind of code do I need so the cells are pasted underneath the last cells? Is this possible or not?

Thanks in advance
Willem

Ps. sorry if my English writing contains errors.
 

Answer:Solved: Copy cells via a macro

16 more replies
Relevance 83.64%

I'm lookign for help to create a macro that:1) identifies when a change has occurred to a cell/row within a named range ("Codes") sitting in column A within just one worksheet in the book (ie Sheet 1)2) identifies and stores the row value (say i) where this changes has occurred3) Copies the following cells within this row,, anely (i,5) (i,9) (i,19-20)4) Pastes them to a set of respective cells within Sheet 2. The destination cells remain fixed5) Recalculates Sheet2

Answer:Macro to copy certain cells in a changed row

Hi,First a few points:1. There is no need to include an instruction to recalculate, unless you have Calculation set to Manual2. I do not understand what cells you want to copy. Your named range "Codes" is said to be in column A and you refer to a row value as i - did you mean 1. Then you say copy cells such as (i,5). Is this Row 1 and column E or column I and row 5.3. You haven't said what cells to copy to on Sheet2To get the result you want you can use the Change Event on Sheet1Whenever the data in a cell on Sheet1 changes, the On change event is triggered.You can capture this and test if the cell that changed was within your named range. If it was within the named range, then you can copy cells on the same row as the changed cell to specified cells on Sheet2.The following code does this, although you will have to rework the copy cell address depending on what you actually want - (I assumed it was cells on the same row as the changed cell in columns E, I and O to S),and change the destination cell addresses to what you need.Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'disable events, so that changes made by this code
'do not re-trigger it
Application.EnableEvents = False

On Error GoTo ErrHnd

Dim rngIsect As Range
'test if change occurred in a cell in the named range
Set rngIsect = Intersect(Target, Range("Codes"))
If Not rngIsect Is Nothing Then
'copy and paste
With ActiveSheet
.Cells(Target.Row, 5).Copy _
Destination... Read more

21 more replies
Relevance 83.23%

I have 6 rows and only one has a number. I'd like to copy and paste additional rows but when i do it adds 6 to the row number. Example: The A1 cell is =item!A2 then there is 5 blank lines. When I copy and paste the next set of rows I get =item!A8. What I need is =item!A3

Answer:Ignore blank cells in copy and paste

Enter this in A1, then copy your group of 6 cells and paste them in A7, and so on.=OFFSET(item!$A$1,(ROW()-1)/6+1,0)Since the ROW function will return the Row number that the formula resides in,(ROW()-1)/6+1 will return 1 in A1, 2 in A7, 3 in A13, etc.For example:In A1:ROW() = 1ROW()-1 = 00/6 = 00+1 = 1In A7:ROW() = 7 7 - 1 = 66/6 = 11 + 1 = 2and so on.That value will then be used as the Row offset from item!A1. With a 0 Column offset, you will see the values from item!A2, item!A3, item!A4, etc.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 82.41%

Hello all and thanks for taking the time to help.I am trying to build a macro button that will when clicked:1. Insert a new row at a specific point (say under row 20),2. Copy values from cells B4,B5,B6,B7 etz to the new row (B4 to A20, B5 to B20, B5 to C20, etz)3. Clear the values from cells B4,B5,B6 etz.I have been able to find/build the first and 3rd part but cannot seem to combine it with the With .Copy code i am using for the second. Regards, and thanks again for the help

Answer:Button Macro to copy multiple cells to new row

It would help if you posted what you already have so we can see if it simply needs a minor modification.Please click on the following line before you post your code and read the instructions on how to post code in this forum.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 82.41%

I have a table that includes a column for "Date of Death". In many cases, the date is either unknown or only partially known. I created another column entitled "Date of Death1". DOD1 is mm/dd/yyyy and DOD is text. I copy and pasted all the data from DOD to DOD1 and DOD1 returned a listing of all of the data it could recognize as dates in proper mm/dd/yyyy format. I'm about to convert DOD1 to text so that I can then fill in the remaining missing data (i.e. "1884"; "12/??/1908"; "?? Mar 198?"; "Apr 1779" etc.). The point is to convert everything to a more uniform, condensed format (because some dates were listed as "February 27th, 1893", and some as "6 Nov 1955" and some as "13/aug/1964" etc.). By filtering I can easily sort out all the dates in DOD that are year only ("yyyy"), however the only way it seems to transfer the remaining data from DOD to DOD1 is to type it in manually as I cannot copy and paste multiple cells by highlighting or using CTRL + or SHIFT +, other than to highlight the entire column. Total of about 8000 records, roughly 1200 more dates to transfer, and roughly a half dozen more columns to repeat this process (Date of Birth, Date of Burial, Date of Purchase, etc.). After that is all said and done I will need a way to sort according to date, given that not all of the information is known and that thousands of entries are going to be year only and hundred... Read more

Answer:Copy and paste multiple cells in access 2003

6 more replies
Relevance 82%

Hello everyone,

I am having a problem in my computer. Initially when I start my computer I don't see the taskbar. Windows key don't work either but after a while taskbar do show up.

The real problem is that copy/paste or drag/drop aren't working in my windows. i can't move files using these fuctions. I can copy and cut but paste function just remain grayed out. Can't drag files at all...

i have noticed a file "dumprep 0 -k" in the startup programs list. Not sure if this has something to do with all this. Please help me solve this problem

Thx..
Tahir
 

More replies
Relevance 81.18%

Hi I am trying to cut multiple cells in a worksheet and paste them into a different worksheet. I get an error message for both cut and copy saying The command you chose can not be used for multiple selections.Does anyone know a way around this?Cheers,Sara

Answer:Cut and Paste Multiple Cells in Excel

I assume that by "multiple cells" you mean noncontiguous multiple cells - i.e. cells that do not border each other. As you have found, Excel will not allow you to do that.Here is one way around it:http://j-walk.com/ss/excel/tips/tip...Here is a "oddity" that details situations where it actually does work.http://spreadsheetpage.com/index.ph...Google the issue for more hits.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 80.77%

Hi, I need a macro that will insert rows based on if cells in rows contain data. So, if rows I-P contain data I need rows insert below based on how many are occupied. For instance, I8-M8 contain data I need 5 rows inserted below them. If I8-P8 contain data I need 9 rows. Then I also need rows A-G and Q-T copied down. I would greatly appreciate any help. Thanks

Answer:Macro to insert rows if cells are occupied and copy data

re: " So, if rows I-P contain data I need rows insert below based on how many are occupied"I:P are columns, not rows.re: "Then I also need rows A-G and Q-T copied down"A:G and Q:T are columns, not rows.re: "For instance, I8-M8 contain data I need 5 rows inserted below them."You used Row 8 in both of your examples. Is Row 8 the only row that needs to be dealt with or do other rows need to be dealt with?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

8 more replies
Relevance 80.77%

Hi, I need a macro that will insert rows based on if cells in rows contain data and copy them over to newly created rows. So, if rows B:D have values then I need rows inserted below based on how many are occupied and the data from C:D copied in the newly inserted rows in B (kind of Transposing the value).Screencast (http://screencast.com/t/NTLcqKU1UO3) needs to be become screencast (http://screencast.com/t/gdOkhrgIei4Z)I would greatly appreciate any help. Thanks

Answer:Macro To Insert Rows If Cells Are Occupied And Copy Data

B:D are Columns, not Rows, therefore I'm not really sure what you are trying to do.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

4 more replies
Relevance 80.77%

I have been using Microsoft Office Excel (2007) and the copy paste, copy Special paste functions have worked fined. Recently Copy paste does not work every time, the cell that I am copying from does not highlight and when selecting paste, nothing is pasted. When selecting Special Paset, I don't get all the options for copying All, Formulas, Values etc, I only get the option to Paste as Unicode Text or Text.

I tried the copy paste function in Word, this works but does not copy the format, only text is copied. I have restarted the computer, but this does not change anything. These functions have always worked in the past, this has only become an issue after I installed Firefox on my computer. I don't know if the two are related or just a coincident.

Before I re install MS Office, I would like to see if this can be resolved

Any support with this is appreciated.

Thanks
vhappy

Answer:Copy & Paste and Special Paste function in Excel does not work

I have moved this thread to the Office Forum for better results.

1 more replies
Relevance 80.36%

Hi,I'm trying to find a macro to cut cells from columns J-P if occupied and transpose them below to column I. For example, If I1, J1, K1, L1, M1, and N1, are all occupied then I need J1-N1 cut and transposed to I2, I3, I4, I5, and I6. If a row only has column I occupied then it should do nothing. Also note, the other columns such as A-H and Q-U may or may not be occupied. I have workbooks with thousands of rows containing data, so doing this by hand is tedious and I would like to remove as much human error as possible. I greatly appreciate any help! Thanks!

More replies
Relevance 80.36%

Hi,

I am using the following macro to search for text in an excel column. The macro works by searching for text then when it finds the matching text it copies the entire row associated with the selected column into a pre-determined row in sheet 2.

The macro works fine however it will only copy text when it finds an exact match, for example if the text in column A is: 'The quick brown fox jupmed over the lazy black dog'. Then the macro will only copy if the exact sentence is inputted into the search box.

I would like to modify the macro so that it will copy the row if regardless if the entire sentence is entered. For example, say I enter the word fox, then it will copy the entire row(s) where the word fox appears in column A.

Any help in modifying the code would be most appreciated. Thanks in advance.

MacroBuddy
PHP:

Sub Button1_Click()
   
Dim LSearchRow As Integer
   Dim LCopyToRow 
As Integer
   Dim LSearchValue 
As String
 
   On Error 
GoT... Read more

Answer:Copy & Paste Macro Help

12 more replies
Relevance 80.36%
Question: Copy Paste Macro

Hi guys,I would love it if someone could help me with this:I have a form that is made up of two combobox's, a vlookup cell (based on the input from one of the comboboxs) and a couple of other cells that just have regular number data entered into them. What I am attempting to do is have a macro attached to a command button that would copy the data in the cells:$A$5:$B$9$:$D$9$:$E$9$:$F$9$:$B$15$:$F$15and paste just the data (not any of the codes or formulas) into the "calls" sheet in the same workbook.I am having issues getting the new data to be copied to the next available line in the calls sheet. I just need the calls sheet to be filled horizontally across the next available row. ANY help at all would be excellent!I also thought about having a pop up window to let me know when the data has been pasted into the calls sheet, any thoughts?

Answer:Copy Paste Macro

Hi,Here is a macro that will copy the cells you specified to the next available row on a worksheet named 'Calls'I have called the source worksheet 'Source', so either change the name of the source worksheet or change the name in the macro to match what you are using.To run the macro, I suggest you add a button to your source worksheetFrom the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)In Developer - Controls select Insert and choose the button icon.Draw the button on the worksheetIn the 'Assign Macro' dialog box select 'New'In the code window that opens enter this:Option Explicit
Sub Button1_Click()
Dim rngDest As Range

On Error GoTo ErrHnd:

Application.ScreenUpdating = False
'setup the destination range - column A in next empty row
Set rngDest = Worksheets("Calls").Range("A" & CStr(Application.Rows.Count)) _
.End(xlUp).Offset(1, 0)

With Worksheets("Source")
'copy and paste as values
Range("A5").Copy
rngDest.PasteSpecial Paste:=xlPasteValues
Range("F15").Copy
rngDest.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
Range("D9:F9").Copy
rngDest.Offset(0, 2).PasteSpecial Paste:=xlPasteValues
Range("B15").Copy
rngDest.Offset(0, 5).PasteSpecial Paste:=xlPasteValues
Range("F15").Copy
rngDest.Offset(0, 6).PasteSpecial Paste:=xlPasteValues
End With
Application.ScreenUpdating = True
E... Read more

4 more replies
Relevance 79.95%

Dear Friends,I work for a software/hardware distributor company. I have a raw data of my customers in excel sheet with their phone numbers and email ids in seperate coloumns.I want to extract only email ids and put to word or in my outlook (in BCC group) to inform them about a promotion going on in my company.Please help me to save my time and energy in copying and pasting email ids once at a time.Your help and guidance will be highly apprecited :)Raz

Answer:copy email ids from excel to word or outlook

coolraz, seems to me you just need to create a csv file to create a list. Perhaps a list in your mail handler would be even easier for your purposes? You can edit a list to add, subtract members and send the same promo to each member of the list, Use BCC to send the mail and none of the others in the list appear.Creating the original list may involve C/P, dunno. Are they currently in your address book? If so, you can simply click on them to put them into a list.HTH.Ed in Texas.

2 more replies
Relevance 79.54%

Hello,
I am having some issues trying to figure out how to select multiple cells using macros. My goal is to be able to highlight a certain name in a column of names, and this name can appear multiple times and not always in a set range.

Example:
John
Larry
Shane
Larry
Larry
John
Larry

(The blue font indicates a highlighted cell)

Is their a way for a marco to look at all of the names in column A until "Do Until IsEmpty()" has been completed and have the specified name in the macro "Larry" stay highlighted to view individual rows of information. Also, these names will not have blank cells inbetween them.

Thanks for all the help!!!
 

Answer:Excel Macro Selecting Multiple Cells

6 more replies
Relevance 79.54%

two problems involving writing macros i would like to solve,

1st - want to write a macro to move to another worksheet, then edit a cell in that worksheet so that the value increases by one

2nd - would like write a macro - which again needs to move to another sheet, then copy a formula in one cell to another, to give two cells with same formula, and then fix one one of those cells (1st) so that it is no longer a formula - just a number
 

Answer:excel problem - macro to edit cells

You don't need to switch sheets in either case.

Sub Macro1()
Sheets("Sheet2").Range("A1") = Sheets("Sheet2").Range("A1") + 1
End Sub

Sub Macro2()
Sheets("Sheet2").Range("A1").Copy Sheets("Sheet2").Range("A2")
Sheets("Sheet2").Range("A1").Value = Sheets("Sheet2").Range("A1").Value
End Sub
 

1 more replies
Relevance 79.54%

Hi,

Im currently having trouble, i have some code to search for a value within 3 spreadsheets but what id like is for it to also return a value on the same row.

e.g
Dave 111111
Claire 112233

so instead of returning just dave, id like the number returned also.
 

Answer:Excel macro: Return values from 2 cells on same row

9 more replies
Relevance 79.54%

Hi there,
I know this question gets asked in various forms time and time again but I'm too much of a novice to make those solutions work for me.
I have a list of data but each item is spread over two rows making it unsortable.

ie
_ A B C D E F
1 a b _ _ _
2 _ c d e f g
3 a b _ _ _
4 _ c d e f g

b and c are both in colum B
whereas i would like to have

_ A B C D E F G
1 a b c d e f g
2
3 a b c d e f g
4

or even without the empty rows but even I can fix that.

If its not too much trouble I'd appreciate an explaination with any code so that I can try and change it for similar problems in the future.
I hope this is clear. I thank you very much for your time.
 

Answer:Solved: Macro to move excel cells

9 more replies
Relevance 79.54%

Hello, I need to figure out a macro to select certain ranges of cells depending on what is in cel 'A1'.

I have a column of dates in column 'B' and two columns of numbers in 'H'. I want to be able to enter a date into cell 'A1' and have it find that date and then select all cells in column 'H' that correspond to all dates before and including the date entered in cell 'A1'.

eg.

A1 = 1/1/2010

B2 = 11/12/2009 H2 = 2.56
B3 = 15/12/2009 H3 = 30.99
B4 = 20/12/2009 H4 = 32.54
B5 = 25/12/2009 H5 = 5.65
B6 = 31/12/2009 H6 = 3.54
B7 = 1/1/2010 H7 = 6.87
B8 = 20/1/2010 H8 = 1.25

Since A1 = 1/1/2010 I want to select all cells from H2 to H7

I do not need this to be done automatically as I will push a button to activate the macro I just need the macro to select the correct cells when i push the button.

Can someone please help me figre this out I am a complete newbie when it comes to Macro coding, but I do understand the rest of Excel very well.

Thanks.
 

Answer:Excel Macro to select specific cells

7 more replies
Relevance 79.54%

I know this is probably very simple, and I am going to hit myself once the answer is found, but I cannot seem to think of the solution for the question below.

I need to highlight all the cells in a column that does not contain a zero. How would I go about doing this with code?
 

Answer:Solved: Excel Macro - Highlight Certain Cells

9 more replies
Relevance 79.54%

Does anyone have a macro that will delete row that have duplicate cells in same row other columns?

Answer:Excel Macro to delete row with duplicate cells

Hi

Are you looking to delete rows that have duplicate data but the duplicate data is in different columns?
Can you give a small example?

If the duplicate values are in the same column then you can use the 'remove duplicates' tool.
Select the rows/columns then go to DATA and you should see the REMOVE DUPLICATES tool.

James

5 more replies
Relevance 79.54%

How, if a condition is met, to write macro to go to a cell and copy contents
that remains active so that one can copy cell contents to another program.

(going to the other program in this case means mozilla firefox and paste into search engine and can
be done manually rather than with coding)

auto copy in excel > manually go to another program >manually paste cell contents
(then delete cell contents to stop macro looping?)
I know how to do this from one excel sheet to another sheet
but code i have does not work for the purpose as described above as the copied cell does not remain
active (dancing ants)
 

Answer:copy and paste to different program using macro

If you know how the commanline options are in Firefox to trigger a search based upon a certain string, then all you have to do is trigger Firefox from Excel to do this.
It's something like this (example is iexplorer.exe
Code:

IEpath = "C:\program files\internet explorer\iexplore.exe"
Shell IEpath & " " & filename, vbNormalFocus

Whre filename could be a html file to open, you could substitute this to parameters to serach, you press Help in Feirefox I expect
 

1 more replies
Relevance 79.54%

Hello,I have several hundreds of files to process. Each of them contains the following lineFilename is ABCDE.I need to replace the ABCDE string with the actual filename.Is there a macro I could use to copy the filename, search for ABCDE and replace with the filename?Thank you kindly.

Answer:Macro to copy and paste filename

Is ABCDE located in the same place in every file?If so, do you know where it is or do you really have to search for it?If you have to search for it, do you even know which sheet it's on or do you have to search every sheet?re: "the actual filename"Does that mean just the filename or does it mean the full path along with the filename?BTW...you can't write to a closed file, so the code is going to have to open it, change the string, save it and close it. The files don't have to be visible, but opening, saving and closing "several hundreds of files" is going to take some time.

3 more replies
Relevance 79.54%

Hi. At work for each client we keep a folder where there are several (~10) .doc files that state date and time, the employees currently attending to the customer and then the customers personal data. I wonder if it is possible for a macro command to automatically copy and paste the employee and customer data, once typed at the 1st .doc to the specific location within each of the next ones

Answer:office macro for copy paste

re: I wonder if it is possible...It's probably possible, but you would need to supply some specific details as to what you are trying to accomplish.

3 more replies
Relevance 79.54%

Can anyone help me?I have a macro that copies and pastes one piece of data at a time Sub PasteText()ActiveSheet.PasteSpecial Format:="Text", Link:=False, _DisplayAsIcon:=FalseEnd SubBut I wish to use a macro that will copy multiple pieces of data (can vary from 2 to 20 pieces) then paste on Microsoft office Excel starting from different locations down columb G with 8 rows separating each piece of dataThanks.

Answer:Windows 7 Copy and Paste macro

Your question is probably best asked in the Office Software forum.MIKEhttp://www.skeptic.com/

2 more replies
Relevance 79.54%

I need a macro that will run from sheet 2, select sheet1, unprotect it, copy the contents of Sheet1 N17, unprotect sheet 2 and PasteSpecial Values into cell A27 on sheet 2. The next time the macro runs it has to do the same thing except put the Sheet1 N17 value into the next empty cell to the right of N27. That has to happen until Y27 has something in it. Then the next time the macro runs, it needs to delete all the values in N17 thru Y17 and start over in N17. Each sheet has to be protected after each time the macro is run.

I just canít get the value to go into the next empty cell. I will appreciate any help I can get on this.

This is what I have so far.
Sub Macro6()
'
' Macro6 Macro
'
'
Sheets("Sheet1").Select
ActiveSheet.Unprotect
Range("N17").Select
Selection.Copy
Sheets("Sheet2").Select

Range("A27").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Sheet1").Select
Range("D13").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Sheet2").Select
Range("D3").Select
End Sub
 

Answer:Solved: copy/paste macro

14 more replies
Relevance 79.54%

Seek for help !!!

I do not have any VBA knowledge, but i know Macro can solve my below problem.
Hope you are able to help me in this.

1. Data in IA sheet will change from time to time.
2. I would like macro to Copy all data in IA sheet & paste to INV Sheet (Blue cell).
If row in Blue Cell are not enought, then it will auto insert row until all the data is filled in.
3. Freight Charges will always in last rows of data.

I really hope & appreciate your help.

I have attached my file here for easy understanding.
 

Answer:Copy & paste data using VB macro

Try this...
Code:
Public Sub CopyRows1()

' Dec 23 , Ziggy

Dim wb As Workbook
Dim wsc, wsp As Worksheet
Dim CR As Long 'copy row
Dim PR As Long 'Paste row
Dim iLastRow As Long

Dim X As String

'Set wb = ThisWorkbook
Set wsp = Sheets("INV")
Set wsc = Sheets("IA")

X = "A" ' just assigned a dummy value
PR = 25 ' assumed the Data Heading always starts on Row 25

Do Until X = ""

X = Cells(PR, 1)
PR = PR + 1

Loop

PR = PR - 1

'The last row of the sheet copying from
iLastRow = wsc.Cells(wsc.Rows.Count, 1).End(xlUp).Row



For CR = 1 To iLastRow



Sheets(wsc.Name).Range("A" & CR).EntireRow.Copy Sheets(wsp.Name).Range("A" & PR)

' colors the cells blue, but the INSERT also automatically does it
Range("A" & PR & ":I" & PR).Interior.Color = (16777164)

PR = PR + 1

' inserts a blank line
Rows(PR & ":" & PR).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove



Next CR


End Sub

 

1 more replies
Relevance 79.54%

How do I copy a fixed range of cells to a unfixed area using a macro. I tried to use the "record" button with and without relative reference. The copied cells allways end up in the same cell where I ran "record". I gave the cells to be copied a range name and the cell where I want it to be pasted a range name. I then put in the macro to delete the pasted range name and create a different cell with a new range name. The macro is still pasteing in the original spot, even thou a new range has been created. I'm using excel 2007 and trying to convert from Lotus.
 

Answer:Solved: copy and paste macro

16 more replies
Relevance 79.54%

At the end of the day, Hi there, Please help me on following matter
On sheet2, I have some data (extracted by using an array formula from my database sheet) from column A to column P. I want to copy and paste it on sheet3 with following conditions.

If Sheet2!A2 >0 then copy the Sheet2!A2:I2 and paste it in sheet3!A2:I2 otherwise donít copy
If Sheet2!A3 >0 then copy the Sheet2!A3:I3 and paste it in sheet3!A3:I3 otherwise donít copy
If Sheet2!A4 >0 then copy the Sheet2!A4:I4 and paste it in sheet3!A4:I4 otherwise donít copy
And so on till row number 100

Iíll run the macro and data will paste in sheet3.
Next day, the data of sheet 2 will be different. So, at the end of the next day, when Iíll run the macro It should not overwrite on cells A2:H2 of sheet3. It should paste the data beneath the existing data in sheet3 (without disturbing the existing data). So that I may able to see the data of previous days on sheet 3. I mean the data on sheet3 should grow up everyday. Sorry for my bad English. I am not a native speaker. Hope you understand what I want to say.
 

Answer:Solved: Need help for copy paste macro

6 more replies
Relevance 79.54%

I have recorded the following macro:

Sub temp12()
'
' temp12 Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Workbooks.Open Filename:= _
"C:\Users\Kurts work c\Desktop\Jeff's stuff\1c\Sales01.csv"
Application.Goto Reference:="R1C2:R258C2"
Selection.Copy
Windows("MASTER REPORT DATA.xlsx").Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Windows("Sales01.csv").Activate
ActiveWorkbook.Close (True)
End Sub
If I use the assigned shortcut of Ctrl+Shift+M, all it does is the first step of opening "C:\Users\Kurts work c\Desktop\Jeff's stuff\1c\Sales01.csv" and nothing more. However, if I run the macro from view macros window it complete the process as desired. Does anyone understand why the shortcut is not working?
ALSO-- I have a list of 62 "Sales" reports that I would like to have the macro run on. They are Sales01.csv, Sales02.csv,.......Sales31.csv along with Sales01A.csv, Sales02A.csv,........Sales31A.csv. I know that I could just copy and paste those steps into the macro 61 times and change the reference on each pasted copy, but I am wonding if there is a way EITHER to set up some kind of loop to go throught the process 62 times, OR to have it run the process on all files within that "C:\Users\Kurts work c\Deskt... Read more

Answer:Trouble with Macro to Copy/Paste

16 more replies
Relevance 78.72%

Hello, Can anyone help me. I need to add a macro that can iterate through a number of worksheets and delete all the data from cells that are unprotected.

I have tried a few things but being unable to find an diagram of the excel object model with properies and fields has slowed me down, if anyone can solve my query and point me to such a document that would be excellent.
 

Answer:Excel macro, change delete from unprotected cells.

You can use the cells "locked" property to determine if the cell is protected or not.

You can can use VBA to select the cells or you can manually select your range of cells and use a simple loop to take appropriate action. How many workbooks and/or sheets are we talking about running this code on? If you provide more details about your process we can give you more code showing how to loop through each sheet in the workbook or even how to loop through an entire directory of workbooks.

Code:

For each vCell in Selection.Cells
If vCell.Locked then
'Your code here
End If
If Not vCell.Locked then
'Your code here
End If
Next

Rollin
 

3 more replies
Relevance 78.72%

Hello everyone,

I am wondering if anyone one would be able to help me. I have only just started getting into advanced excel use of macros and formulas for Excel 07. I have done ok so far except for the following macros formula needed tp change the background colour of a range of cells with more then the three options that conditional formatting allows.
I have a column of cells from C6:C80 which I have made all drop down selections from a list of four text items:
ItemA
ItemB
ItemC
ItemD
I was trying to get a macro that would colour the cells in C6:C80 automatically on selection of the drop down list based upon the four different selections available. And another macro to colour that whole row that has data in it based upon that same selection. The reason why two different ones being that I am unsure of what will work the best.

I know this is on the easy side of macros, but I am hoping someone would be able to throw me a bone none the less

Thanks for any help all.

Ads
 

Answer:Solved: Excel 07 macro to colour cells in a range

Ok turns out I didn't need macro and it can all be done with conditional formatting using the "Highlight Cell Rules". I was under the impression that you could only use three conditions but that was incorrect. Thanks for the help from Sweep at another forum.
 

1 more replies
Relevance 78.72%

I have a macro from MrExcel.com that parses each row from an Excel spreadsheet into a separate word document and saves each document to My Documents as "File-Row #.doc". For example, row 2 is saved as: File2.doc, row 3 as File3.doc, etc. to the last row with data. The macro starts on row 2 to skip the column headers.I would like to modify the macro to have the file name equal the concatenated values from the first two cells in each row (Columns A and B from row 2 to the last row with data).An example of a simplified version of the spreadsheet: A B C1 Date as Text Record # Record Status2 2012-05-28 APF-2012-1940 Closed3 2012-05-29 ICM-2012-1987 In Process 4 2012-06-02 PAT-2012-2317 Awaiting ClosureOnce the macro is run, the results from the above would be 3 word documents with the file names as below:Row 2 file name is: 2012-05-28 APF-2012-1940.docRow 3 file name is: 2012-05-29 ICM-2012-1987.docRow 4 file name is: 2012-06-02 PAT-2012-2317.docThe macro as it currently exists:Sub ControlWord() Dim appWD As Word.Application Set appWD = CreateObject("Word.Application.8") appWD.Visible = True Sheets("Data").Select FinalRow = Range("A9999").End(xlUp).Row For i = 2 To FinalRow Sheets("Data").Select Range("A" & i & ":N" & i).Copy Sheets("Template").Select Range("A" & i & ":N" & i).PasteSpecial Tr... Read more

Answer:Modify macro to use values in two Excel cells as file name

In the future, if you are going to post data or code in this forum, please click on the blue line at the end of this post and read the instructions on how to post example data and VBA code in this forum.As far as your current issue, this syntax is probably not converting to what you want it to be:Range ("A" & "B" & i)For i = 1, this would seen to VBA as:Range("AB1")I would try something like this within the loop:tmpFilename = Range("A" & i) & " " & Range("B" & i)...
..
...
appWD.ActiveDocument.SaveAs Filename:=tmpFilenameThis will build the Filename from the values in A1 and B1 with a space in between the 2 values.You should also be aware that you might run into problems with the dates in Column A.If VBA picks up the dates as 05/28/2012, then your code will fail because slashes can not be used in a filename.It will depend on how the default dates are set up on your system.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 78.72%

Requirements.Need to prepare time sheet with protection.1. User should not enter time manually between A1:B5, it has to be done by clicking macro button(Macro already written for that). ' Button1_Click Macro'' Keyboard Shortcut: Ctrl+q' ActiveCell.FormulaR1C1 = Now End Sub2. Need to protect cell A1:B5 with password XXX.User should not edit those cell range manually. It should be done by only pressing Macro button.

More replies
Relevance 78.72%

I have a worksheet where column A uses data validation, from row 1 to row 50, to allow the user to select from two options. I need a macro that, when option A is seelected in say cell A1, cells B1, C1, D1, E1 and F1 will be protected and if Option B is selected it will unlock them again. I need it to be able to lock the cells in the row depending which row the Option is selected in.
I have been trying something with an event change but I can only get it to lock for one one row.
Hope this make sense.

Thanks,

Timbo2000
 

Answer:Excel Macro to protect cells in row if a certain word is selected

Try out the sample Code below:


Code:

Sub Macro1()
'
' Macro1 Macro
'

Dim wrkSheet As Worksheet
Dim xrow As Long, xcol As Long
Dim opt

ActiveSheet.Unprotect

Set wrkSheet = ActiveSheet
Range("A1:A100").Select
Selection.Locked = False
Range("A1").Select

xrow = ActiveCell.Row
xcol = ActiveCell.Column
opt = Range("A" & xrow).Value

If opt = "B" Then
Range("B" & xrow & ":" & "F" & xrow).Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Else
Range("B" & xrow & ":" & "F" & xrow).Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub
 

3 more replies
Relevance 78.72%

hey,
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 78.72%

Hello,

I have a workbook that has two sheets. I need macro to save the data from sheet 1 to sheet 2, in sheet 1 i have data i have copied from a web page from where i need to find some specific text and numbers and get pasted in sheet 2 in a specific cell.

below are the items header i want to copy from the web page:

Company Code
A/C
Descripiton
QTY
EMP(Deliver to)
Requestor name
Email ID
Serial #
SCS String
Cost center
Building#
Vendor
Supplier address
Ship to address
PR #
Part#
ORDER #
PR value amount

I would just need a macro which can find copy the above details and paste in sheet 2 below the headers
I really appreciate any help provided!!

Thanks in advance
 

More replies
Relevance 78.72%

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 _
Destination:=Sheets("test").Range("B1")
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 78.72%

Hello,

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 78.72%

Workbook1 sheet1 has data. column A is dates from a1 to a1554. Workbook2 sheet1 is destination. dates are in g14 to g51. I want a macro to copy cells from column b in workbook1 to column b in workbook2 according to matching dates in column A. I cannot use vlookup formula on destination workbook2 it is protected. it has to be a macro run from workbook1 sheet1

Answer:macro for vlookup then copy and paste to another workbook

First, it is customary (and polite) to ask for help as opposed to just telling us what you want and what the solution has to be.Second, if you (as a user) can't use a VLOOKUP in workbook 2 due to its protection status, then a macro can't write data into the workbook either. If a macro could override protection so easily, then workbook/worksheet protection wouldn't be worth anything.Yes, a macro can unprotect a workbook, even it if has a password, but that password has to written into the code. If the workbook isn't password protected, then your statement "I cannot use vlookup formula on destination workbook2 it is protected" doesn't make sense. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies