|
The code below works like I want it to so far, by selecting specific cells in one worksheet and pasting in specific columns on another. When I change the data is D10, D12 and D14, how do I get it to paste on the other worksheet in the next available cell for that column?
Sub Button1_Click()
lMaxRows = Sheets("Sheet4").Cells(Rows.Count, 4).End(xlUp).Row
Sheets("Sheet4").Cells(lMaxRows, 4) = Sheets("Sheet1").Range("D12")
Dim LR As Long
Sheets("Sheet4").Select
'your paste to sheet name
'Last Row in "Sheet4" - change column as you need
LR = Sheets("Sheet4").Range("D" & Rows.Count).End(xlUp).Row
Range("D" & LR).Select
'change A as you need - it selects the last row
'Paste
lMaxRows = Sheets("Sheet4").Cells(Rows.Count, 5).End(xlUp).Row
Sheets("Sheet4").Cells(lMaxRows, 5) = Sheets("Sheet1").Range("D14")
lMaxRows = Sheets("Sheet4").Cells(Rows.Count, 5).End(xlUp).Row
Sheets("Sheet4").Cells(lMaxRows, 6) = Sheets("Sheet1").Range("D10")
|
|
|
|
|
What libraries are you using? Interop with Excel, or a third-party?
As it reads, the first two lines should do the trick; you're fetching the last cell, and writing the contents of Sheet1$D12.
David Rubin wrote: how do I get it to paste on the other worksheet in the next available cell for that column?
Pasting is a lousy way of aggregating choices. Launch VB, read that file, and write a decent UI for it.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I thought it would work too, but when I change those cells it just copies over the previously pasted data.
I am very new to this, so I dont understand everything.
|
|
|
|
|
Copying/pasting is not behaviour that applications should override; the clipboard is an OS-function. Most likely it's Windows that's biting you 
Ensure that it does find the "next free row", or take the time to write a UI. A CheckedListBox sounds convenient; you show your options, the user presses "spacebar" to select an item, or cursor-down to go to the next item. Wouldn't that be a friendlier way to get a selection?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Again, I am new at this. None of what you just said makes any sense to me. Do you know how to help me figure out how to make my code work?
|
|
|
|
|
David Rubin wrote: Again, I am new at this.
All of us started that way.
David Rubin wrote: None of what you just said makes any sense to me.
Copying/pasting is something that's baked into Windows; and it's hard for applications to change it's behaviour, because it's deemed an important function.
Can you give me a brief idea of what you're trying to achieve? It looks like you want to have the user make a list of things.
David Rubin wrote: Do you know how to help me figure out how to make my code work?
I'm not on a machine with Excel atm, but I'll be running the code later. As the second line of code, insert the line below;
Sheets("Sheet1").Cells(1, 1) = lMaxRowsThat should tell you what is considered to be the "next free cell".
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Here is what I am trying to achieve.
I have one workbook, with two different sheets (Sheet1 and Sheet4). I am trying to copy the following cells on Sheet1 to specific cells/columns in Sheet4 with the click of a button.
Sheet1 D10 to Sheet4 column B in the next available cell
Sheet1 D12 to Sheet4 column D in the next available cell
Sheet1 D14 to Sheet4 column E in the next available cell
Sheet1 E22 to Sheet4 column C in the next available cell
Right now I can get it to work in terms of getting the cells to copy and paste properly to sheet4, but when I click the button again, it just replaces the information in those cells. Every time I click the button, I will need it to fill in the next available cell for the columns listed above.
Your help is appreciated.
|
|
|
|
|
Here is what I would have done (20 years ago).
Turn macro recorder on
Navigate from the source cell to the destination location using the keyboard. This will give you an idea how the macro coding works, you will need to modify it to look for the next vacant row.
Now in code get the contents of the D## cells into variables.
Navigate to the next empty row. Set the values from the variables into the required cells.
Inform the user the job is done.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
David Rubin wrote: Your help is appreciated.
Looks like you're building a macro in Excel itself; the code didn't work from a new VB.NET application. The "rows" should be prefixed with the sheet that they're coming from, and xlUp wasn't recognized.
There's also no concept of a "next available cell".
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
i have stored procedure with two parameters, one varchar and another one date format.
i am getting the error ,"Error converting data type nvarchar to datetime." like this while am running.
this is my code inside the vb,
Public Sub Selecttable()
sql.OpenDbConnection()
Dim cmd As SqlCommand = New SqlCommand("SearchRecord", sql.SqlConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Date", "DateOfActivation")
cmd.Parameters.AddWithValue("@Activation", "activation")
Dim dt As New DataTable()
Using da As New SqlDataAdapter(cmd)
da.Fill(dt)
dgvSearch.DataSource = dt
End Using
End Sub
and my storedprocudure like this...
USE [Activation]
GO
/****** Object: StoredProcedure [dbo].[SearchRecord] Script Date: 05/05/2014 21:24:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SearchRecord]
@Activation nvarchar(50),
@Date datetime
AS
BEGIN
set dateformat yyyymmdd
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from Activation.dbo.tblActivationMain WHERE DateOfActivation=@Date AND Activated=@Activation
END
please help me to find the solution.
|
|
|
|
|
Try swapping the order of your command parameters.
|
|
|
|
|
|
Because they are named, the order does not matter.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
..on top of that, rename the parameter from "Date" to "DateOfActivation"; DATE might be a reserved keyword
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
i tried this way
USE [Activation]
GO
/****** Object: StoredProcedure [dbo].[SearchRecord] Script Date: 05/05/2014 21:24:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SearchRecord]
@Activation nvarchar(50),
@DateActivation date
AS
BEGIN
set dateformat yyyymmdd
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from Activation.dbo.tblActivationMain WHERE DateOfActivation=@DateActivation AND Activated=@Activation
END
no chance
|
|
|
|
|
cmd.Parameters.AddWithValue("@Date", "DateOfActivation")Is that intentional? I assume you are putting a Date-object in there?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
that one is variable... I got one datepicker.
|
|
|
|
|
Whotsitnamed? If it's called "DateTimePicker1", try something like below;
cmd.Parameters.AddWithValue("@DateActivation ", DateTimePicker1.Value);
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
cmd.Parameters.AddWithValue("@Date", "DateOfActivation")
You are passing in the string "DateOfActivation" to a date field which is why you get the error.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
