I have code to ask the user "Type in the date", I need code to find that date, Copy that active cell on the excel spreadsheet along with three cells directly below for example: if G6 is selected as the active cell, I also need G7,G8,and G9 copied to a new spread sheet. This active cell will change with every different user. Can anyone help? Thanks!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Help with Selecting cells to be moved?
- Thread starter gjsala
- Start date
Not sure if your reference to "code" means you are looking for a VBA solution.
To give you a start on the VBA side:
Switch macro recorder on.
Copy the date the user has input
select the range containing the dates (I suggest you use a named range)
Use Edit,Find to find the date (paste in the value)
Extend selection, copy and paste
Switch off macro recorder.
Examine the recorded code and 'fine-tune' it.
Hint: Use Activecell.Offset(...) to extend the selection after the find
To give you a start on the VBA side:
Switch macro recorder on.
Copy the date the user has input
select the range containing the dates (I suggest you use a named range)
Use Edit,Find to find the date (paste in the value)
Extend selection, copy and paste
Switch off macro recorder.
Examine the recorded code and 'fine-tune' it.
Hint: Use Activecell.Offset(...) to extend the selection after the find
something like this :
mDate = (inputbox("Enter date"
set fCell = sheets("Sheetname".columns("Columnletter".find(format(mDate,"dd/mm/yy",lookin:=xlvalues,lookat:=xlwhole)
if not fCell is nothing then
'date found
range(cells(fCell.row,fCell.column),cells(fCell.row + 3,fCell.column)).copy destination:=sheets("SheetToCopyTo".range("RangeToCopyTo"
else
msgbox "Date Not Found"
end if
Rgds
Geoff
Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur
mDate = (inputbox("Enter date"
set fCell = sheets("Sheetname"
.columns("Columnletter".find(format(mDate,"dd/mm/yy",lookin:=xlvalues,lookat:=xlwhole)if not fCell is nothing then
'date found
range(cells(fCell.row,fCell.column),cells(fCell.row + 3,fCell.column)).copy destination:=sheets("SheetToCopyTo"
.range("RangeToCopyTo"else
msgbox "Date Not Found"
end if
Rgds
Geoff
Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur
- Status
Similar threads
- Locked
- Question