Copy / Paste Specifc Row based on Input Box Value

[Guest]

Hi there,

I would like to copy and paste a specific row from Sheet 1, to row 50 of
Sheet 2, based on a date provided by the user in an input box. Each date
represents a specific row in Sheet 1.

Can any one help me?

 

[Guest]

I'm assuming you know how to get the row # you want. Here is how to copy the
values from sheet 1 to sheet 2

Sheets("Sheet2").Rows(10).Value = Sheets("Sheet1").Rows(iRpw).Value

If you need assistance with the row #, let me know.

 

[Guest]

Assuming the dates are in Column A of sheet one and sheet one is the active
sheet:

Sub cpynPst()
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
myDate = InputBox("Enter a date", "User Date")
myDate = Format(myDate, "m/d/yyyy")
With Worksheets(1).Range("$A$1:A" & lastRow)
Set c = .Find(myDate, After:=Range("A" & lastRow), LookIn:=xlValues)
If Not c Is Nothing Then
pRng = c.Address
.Range(pRng).EntireRow.Copy Worksheets(2).Range("$A$50")
End If
End With
End Sub

The only problem with this, Carlee, is that the user must use conventional
date formats for it to be converted to the default m/d/yyyy format.

 

[Guest]

Hi htere,

I don't actually. What i am trying to figure out is this:
1. User selects a date using a userform
2. System takes date and searches the 'Reading Date' column of the 'Master
Log' - there is one row for each date
3. The system copies the found row, to 'Sheet 2', Row 50

Any idea on how to accomplish this?

 

[Guest]

Hi
My date column is column B, so I changed the code to the following, but
nothing is happening. Worksheet 1 = 'Daily Reading Master Log' and Worksheet
2 = 'Customer1 Daily'. What am I doing wrong?

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
myDate = InputBox("Enter a date", "User Date")
myDate = Format(myDate, "m/d/yyyy")
With Worksheets("Daily Reading Master Log").Range("$B$1:B" & lastRow)
Set c = .Find(myDate, After:=Range("B" & lastRow), LookIn:=xlValues)
If Not c Is Nothing Then
pRng = c.Address
.Range(pRng).EntireRow.Copy Worksheets("Customer1
Daily").Range("$A$50")
End If
End With

 

[Guest]

If your date format in column B is not m/d/yyyy then the code will fail. You
will need to change this line in the code I gave you:

myDate = Format(myDate, "m/d/yyyy")

To reflect the date format that you are using. If you have a problem, just
post a sample of the date format you are using and I will give you a new line
to replace this one.

 

[Guest]

Hi there,

1. my date format is as follows: 15/03/2007
2. I would like the user to be able to click a button on the 'Report
Options' form; however, it appears that this code will only work if the
worksheet is active. Can this be changes?

Many thanks in advance,

 

[Guest]

If your date format is 15/03/2007 the it is either "d/mm/yyyy" or "dd/mm/yyyy".
I am assuming that since there is an 03 for the month that you also use the
01 thru 09 or the day, so change this line:

myDate = Format(myDate, "m/d/yyyy")

to this:

myDate = Format(myDate, "dd/mm/yyyy")

If that don't work use:

myDate = Format(myDate, "d/mm/yyyy")

As to the command button. The code I posted was to answer your immediate
question. You can modify the code to adapt it for use with a control click
event. The lastRow variable will have to include the 'Worksheets("Daily
Reading Master Log")' object and will have to be set as an object variable so
the search and copy will work right. Why don't you try it and if you can't
get it to work do a new posting for help with the control code.