copying cells to another worksheet in next available cell per column - Visual Basic Discussion Boards

I don't understand , dateofactivation variable I made it , date.
dim dateofactivation as date like this. is it correct or what else I want to do? please tell me sir.

Then you would do it like this:

cmd.Parameters.AddWithValue("@Date", DateOfActivation)

without the quotes around it. The quotes make it a string, not a variable.

There are only 10 types of people in the world, those who understand binary and those who don't.

all the data are showing, but it is not showing by date wise.

I don't understand what you are saying. You should post a new question if this is a new question.

There are only 10 types of people in the world, those who understand binary and those who don't.

it is not showing by date instead all data are showing

You'll have to debug it and make sure the date is actually being passed in like you think it is.

There are only 10 types of people in the world, those who understand binary and those who don't.

You are passing a STRING that contains the letters "DateOfActivation". You are NOT passing in an actual DateTime value. That's why you're getting the error. Remove the quotes!!

cmd.Parameters.AddWithValue("@Date", dataofactivation)

A guide to posting questions on CodeProject

How to debug small programs

Dave Kreskowiak

I need to copy multiple cells from one worksheet (Sheet 1) and paste them into another worksheet (Sheet 4). I want each cell from sheet 1 to paste in a specific column in sheet 4, to the next available cell in sheet 4. I can only get one column to correctly paste. When I change the code, it updates another column, but not the previous one.

Here is what I need:

Copy from Paste to
SHEET 1, cell D10 SHEET 4 Column B, the next available cell
SHEET 1, cell D12 SHEET 4 Column D, the next available cell
SHEET 1, cell D14 SHEET 4 Column E, the next available cell
SHEET 1, cell E22 SHEET 4 Column C, the next available cell

Here is the current code I am using:

Sub Mail_workbook_Outlook_2()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object

Set wb1 = ActiveWorkbook
If Val(Application.Version) >= 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file. There will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file as a macro-enabled (. Xlsm) and then retry the macro.", vbInformation
Exit Sub
End If
End If

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

' Make a copy of the file.
' If you want to change the file name then change only TempFileName variable.
'TempFilePath = Environ$("temp") & "\"
'TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
'FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
'Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
' Change the mail address and subject in the macro before you run this procedure.
With OutMail
.To = "ENTER EMAIL ADDRESS HERE"
.CC = ""
.BCC = ""
.Subject = "Work Order Request " & Format(Now, "dd-mmm-yy h:mm:ss")
.Body = "Please see attached maintenance work order"
.Attachments.Add wb1.FullName
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:\test.txt")
' In place of the following statement, you can use ".Display" to
' display the mail.
.Send
End With
On Error GoTo 0

'wb2.Close SaveChanges:=False

' Delete the file.
'Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True

Range("D14").Select
Selection.Copy
Sheets("Sheet4").Select
lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
Range("E" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
Range("E" & lMaxRows + 1).Select
On Error Resume Next

MsgBox "Your work order request has been sent to maintenance."
End With
End Sub

All you need to do is debug it and find out where it is wrong.

There are only 10 types of people in the world, those who understand binary and those who don't.

I did debug it and there is nothing wrong. It just isnt doing what I want it to do.

Then you should have been able to see what was wrong. If it is going in the wrong column then you are not referencing the right column.

There are only 10 types of people in the world, those who understand binary and those who don't.

If I knew enough about visual basic I would have fixed it. Thats why I'm on the message board. Your help would be appreciated if you know enough...

We can't run your code. All you have to do is put a breakpoint and then walk through your code. Also, you should only post the relevant code. You posted the email code which just clutters up the question and makes it harder for us to help.

However, you have:

SQL

Range("E" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

If you just look at it you will see that it is selecting column E every time so naturally you need to change that part to be whatever column you want.

Also note, you can record macros in Excel doing what you want and have it write the code for you (with few tweaks to get it into VB).

There are only 10 types of people in the world, those who understand binary and those who don't.

Thanks. This part below works just like I want it to, but when I change the range (i.e. when I change range "D14" to "D12", and Rande "E" to "C", it doesn't work. I'm trying to understand what I am missing. Like I said, I am new to this and this is my first time posting to a message board....ever. Thanks again for any help you can give.

Range("D14").Select
Selection.Copy
Sheets("Sheet4").Select
lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
Range("E" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=_
False, Transpose:=False
lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
Range("E" & lMaxRows + 1).Select

What do you mean it doesn't work?

There are only 10 types of people in the world, those who understand binary and those who don't.

Ok, so the first part of code below works fine...but when I want to do the same thing, just using other cells from sheet 1 and paste into other locations on sheet 4, it doesnt work. Is there something I need to be placing between codes? Yes, I do realize I sound like an idiot.

Range("D14").Select
Selection.Copy
Sheets("Sheet4").Select
lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
Range("E" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
Range("E" & lMaxRows + 1).Select
Range("D12").Select

Selection.Copy
Sheets("Sheet4").Select
lMaxRows = Cells(Rows.Count, "D").End(xlUp).Row
Range("D" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
lMaxRows = Cells(Rows.Count, "D").End(xlUp).Row
Range("D" & lMaxRows + 1).Select

It's hard to tell without being able to run it but you likely just need to selection what you want first. The second set of code does Selection.Copy so before that you need to select what you want copied.

There are only 10 types of people in the world, those who understand binary and those who don't.

I had a program which was working well. Suddenly it started giving errors while compiling on all the basic commands like MsgBox, Ucase, Len, Trim etc. I have found out the solution but I would like to have some reason why all these commands were working fine when I did not use the import Miscrosoft.visualbasic.

If anybody can enlighten me on this I will be obliged

There is an "implied" list of imports in the Project Properties. They don't show up in your code, but instead are a checkbox list in the "My Project" node, References tab, under "Imported namespaces". Microsoft.VisualBasic is usually at the top of the list if it's checked. If not, just go check it again and save.

A guide to posting questions on CodeProject

How to debug small programs

Dave Kreskowiak

Thnks Dave for the info.

Hello guys,

I need your guidance on how to send an e-mail notification to a list of recipients from a combo box.

I have a save button on a form that saves things into an sql database..I have a combo box that may contain e-mail of recipients.

I'd like to send an e-mail notification on the save event using the the listing from the combo box, without any popup window, just send the e-mail to that particular person.

Here is the scenario:

A form collects information about a complaint. I want an e-mail notification send out to the person who will look at the complaint information once the form is saved.

I am using vb.net on Ms Visual Studio 2010 with SQl.

All the examples I searched on the NET do not seem to be suitable for what I want to accomplish.

Thanks in advance.

You haven't said anything about what part of this you're having a problem with.

Sending an email is easy. There are examples of this all over the web.

Getting the selected item in a ComboBox is easy. This is just a matter of reading the documentation on the ComboBox class.

There is no "save" event, so that can't be the problem. There is a Button "click" event and really, handling a simple event like that is easy.

Soooo, what part of this is the problem?

A guide to posting questions on CodeProject

How to debug small programs

Dave Kreskowiak

I would like to know how to do a scren capture to a (png or other image format) of a panel control.

if we assume that we have a panel control and in the panel is some graphics and the background of the panel is black. I would like to capture the panel but have the back ground white.

I do want to capture everything in a specific panel control I just want it black and white.

Does anyone have a snippet that will capture the panel control in black and white

Take a look at some of these articles[^].

Thanks for the list of options. I have been looking at some of those already. I just can't seem to find a way to capture in monochrome.

I appreciate your help