Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy / Paste Specifc Row based on Input Box Value
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? -- Carlee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy / Paste Specifc Row based on Input Box Value
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. "Carlee" wrote: 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? -- Carlee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy / Paste Specifc Row based on Input Box Value
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. "Carlee" wrote: 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? -- Carlee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy / Paste Specifc Row based on Input Box Value
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? -- Carlee "Barb Reinhardt" wrote: 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. "Carlee" wrote: 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? -- Carlee |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy / Paste Specifc Row based on Input Box Value
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 -- Carlee "JLGWhiz" wrote: 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. "Carlee" wrote: 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? -- Carlee |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy / Paste Specifc Row based on Input Box Value
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. "Carlee" wrote: 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 -- Carlee "JLGWhiz" wrote: 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. "Carlee" wrote: 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? -- Carlee |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy / Paste Specifc Row based on Input Box Value
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, -- Carlee "JLGWhiz" wrote: 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. "Carlee" wrote: 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 -- Carlee "JLGWhiz" wrote: 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. "Carlee" wrote: 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? -- Carlee |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy / Paste Specifc Row based on Input Box Value
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. "Carlee" wrote: 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, -- Carlee "JLGWhiz" wrote: 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. "Carlee" wrote: 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 -- Carlee "JLGWhiz" wrote: 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. "Carlee" wrote: 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? -- Carlee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste rows with specifc text in column d | Excel Programming | |||
Input box Copy Paste Range | Excel Programming | |||
Create summary based on specifc value in a different sheet | Excel Worksheet Functions | |||
Copy/Paste Worksheet to last and rename with input box for value. | Excel Programming | |||
Copy/Paste Worksheet to last and rename with input box for value. | Excel Programming |