need macro code for data

August 23, 2011 at 13:56:16
Specs: Windows 7
Hi
I am trying to create a macro that once I copy an email into a worksheet it takes that data and brings it to another worksheet within the same excel file. I am able to do this by recording a macro and copying and pasting the formula BUT when I go to run the next days data the macro replaces the old data with the new data. I want the new data to go to the next line. What is the macro code for doing this?!?!?

See More: need macro code for data

Report •


#1
August 23, 2011 at 16:52:16
A line similar to this will return the Row number of the last cell that contains data in e.g. Column A:

lastRw = Range("A" & Rows.Count).End(xlup).Row 

The next empty Row would be:

nextRw = Range("A" & Rows.Count).End(xlup).Row + 1

Therefore, you might want to paste your data using one of these, depending on which of the above lines you choose to use:

Range("A" & lastRw + 1)

or

Range("A" & nextRw)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 24, 2011 at 06:17:06
Thank you for getting back to me.

This is the macro I have so far and it is not working... I am not sure where I would put the go to the next row with the data that it collects from this other sheet.

Does seeing this potentially help answer the questions?

Sub CSData()
'
' CSData Macro
'
' Keyboard Shortcut: Ctrl+g
'
Sheets("CS Sheet").Select
' Find the last row of data
Sheets("Data Dump").Select
Range("B24").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Next.Select
Range("B9").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("C24").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Next.Select
Range("C9").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Range("D24").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Next.Select
Range("D9").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Range("E24").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("F24").Select
ActiveSheet.Next.Select
Range("E9").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Next.Select
Range("F9").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Range("G24").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Next.Select
Range("G9").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Range("H24").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Next.Select
Range("H9").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Range("I24").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Next.Select
ActiveSheet.Previous.Select
Range("K23").Select
Application.CutCopyMode = False
Selection.Copy
Range("K24").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub


Report •

#3
August 24, 2011 at 10:28:16
First, I'll assume that you didn't read the last line of my previous post and read the instructions on how to post VBA code in this forum.

Second, VBA Code created via the Macro Recorder is typically very bloated and inefficient, just like the code you posted. If you are going to be using VBA you should learn how to condense the code into something more managable.

For example, let's look at the first 5 lines of the code:

Sheets("CS Sheet").Select
' Find the last row of data
Sheets("Data Dump").Select
Range("B24").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

2 key points:

1 - You have extraneous lines throughout your code that just waste resources and confuse the reader. For example, look at the first 2 instructions:

Your code Selects a sheet named "CS Sheet" but doesn't do anything with it. It then Selects a sheet named "Data Dump".

There is no need for the "CS Sheet" to be selected by the code, so you should eliminate it. You should go through your code and delete all of the lines that don't serve any purpose except to waste resources.

2 - Rarely do you have to Select an object in VBA to perform an action on it. Within VBA you can perform the action directly.

For example, these three lines of code:

Sheets("Data Dump").Select
Range("B24").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

can be condensed into a single line:

Sheets("Data Dump").Range("B24").PasteSpecial Paste:=xlPasteFormulas

Before I can even begin to help you modify your code, you'll need to go through it and clean it up. For one thing, it will be a great learning experience for you. From my side of the issue, I don't have the time or energy to try and decipher all the extraneous instructions and wasted code to figure out how to resolve your issue.

Once you've cleaned up the code, you might even figure out where the lastRw or nextRw instructions should go.

If not, post the cleaned up version and I'll see what I can do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 25, 2011 at 10:22:05
Thank you! That was very helpful. I am sorry I missed your last line the first time I read it. I was able to figure it out.

Do you know how to take information from an excel sheet and have it send to outlook and open an email? I don't want to make it an attachment I want to make it within the email.

Thanks!


Report •

#5
August 25, 2011 at 11:26:30
Alright I actually got the email to work and everything. The only thing that I am having trouble with is that when the email sends before the data below the email says "This is a sample Worksheet" Do you know how to get rid of that?

Sub Send_Range()

'Select the range of cells on the active worksheet.
ActiveSheet.Range("A4:F27").Select

'Show the envelope on the Email.
ActiveWorkbook.EnvelopeVisible = True

'It also sets
'the To and Subject lines. Finally the message
'is sent.
With ActiveSheet.MailEnvelope
.Item.To = "xxxx.xxxx@xxxxx.xxx"
.Item.Subject = "SRP " & Format$(Date, "mm-dd-yy")
.Item.Send
End With
End Sub


Report •

#6
August 25, 2011 at 16:53:45
Since this "email" question is not related to your original question, it should be posted in its own thread.

...and once again you have posted VBA code without following the instructions found via the link in my signature line.

I have also edited out the plain-text email address you posted. Never post a email address in a public forum. We don't really care if the person whose address is posted get a bunch of spam, but we don't want this site to become known as a place where email addresses can be harvested. Once the bad guys start hanging around, we'll be open to all sorts of security problems.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question