yes the one work book (book "a") pulls from the other(source book) in the fome of linked formulas; so the vba will be only on the work book ( book "a"). Uploading is not an option at this time. it is excel 2010 and on row 11 i have dates for cy 2013. so when the user needs a single date (e.g. 1/15/13) the information from column 1/15/13 row 12 through row 312 will past to sheet 2.
my bad; button to cause the prompt.
fyi copy the entire range 12 to 312 and paste the value to column, lets say b1, to 300 on the other sheet.
are you there? Are you working on it?
Hello Below is a link to a spreadsheet that shows the Macro you need:
Below is the Code or the actual Macro with Comments. Please let me know if you are satisfied with this code and if so please provide positive ratings, otherwise. Please reply with and needed modifications.
Sub CopyCol()Dim StCol As RangeDim CpyRng As RangeDim UsrDt As DateDim x As Integer
'Change Range Row to correct column if dates do not start in column A, ALSO Sheet names may need to be changedSheets("Sheet1").SelectSet StCol = Sheets("Sheet1").Range("A11")
UsrDt = InputBox("Please enter date")If Not (IsDate(UsrDt)) ThenMsgBox ("Please enter a correct date format")Exit SubEnd Ifx = 0Do While Not (IsEmpty(StCol))If StCol = UsrDt Then
Set CpyRng = Range(StCol.Offset(1, 0).Address & ":" & StCol.Offset(301, 0).Address)
CpyRng.SelectApplication.CutCopyMode = FalseSelection.Copy
'Below May need to Change Sheet to correct Sheet Name to be pastedSheets("Sheet2").Select
'Below May need to Change Range to correct cell to be pastedActiveSheet.Range("A1").Select'Below Pastes Values only to remove those formulasSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=Falsex = x + 1Else
End IfSet StCol = StCol.Offset(0, 1)Loop
If x <> 1 ThenMsgBox ("Date not Found")End If