bananatang
03-19-2010, 05:01 AM
Hi All.
I would really appreciate some help to refine a macro that i have been working on.
I am trying to copy a selected range from a row which will be determined by the user via an input box, the cell values will then be pasted to another worksheet and ultimately applying this macro to all worksheets within a workbook.
I.e The range that is to be searched will be A1:A200. I require the found cell and the following 10 cells to the right of the activecell to be copied and pasted in another worksheet.
The code i have so far is as follows. I continue to get an error relating to the search range.
Sub zzz()
Dim wks As Worksheet
'Clears the content of the report page prior to new data being pasted.
With sheet3
.Range(.Cells(2, 1), .Cells(Rows.Count, 17)).ClearContents
Dim myinput As String
myinput = InputBox("Enter No. of weeks")
For Each wks In ThisWorkbook.Worksheets
'I would include the following code, if i did not wish for certain worksheets
'to be excluded from the macro
'If Not wks Is sheet3 _
And Not wks.Name = "sheet2" _
And Not wks.Name = "sheet4" Then
'##########################################################################
'*****My error lies here*****
Range("A1:A100").Select
Selection.Find(What:=myinput, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select
'##########################################################################
wks.Range("A" & ActiveCell.Row).Resize(, 10).Copy
Sheets("Sheet3").Select
Range("a5000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
'End If
Next wks
End With
End Sub
Any assistance would be gratefully appreciated.
Thanks
BT
I would really appreciate some help to refine a macro that i have been working on.
I am trying to copy a selected range from a row which will be determined by the user via an input box, the cell values will then be pasted to another worksheet and ultimately applying this macro to all worksheets within a workbook.
I.e The range that is to be searched will be A1:A200. I require the found cell and the following 10 cells to the right of the activecell to be copied and pasted in another worksheet.
The code i have so far is as follows. I continue to get an error relating to the search range.
Sub zzz()
Dim wks As Worksheet
'Clears the content of the report page prior to new data being pasted.
With sheet3
.Range(.Cells(2, 1), .Cells(Rows.Count, 17)).ClearContents
Dim myinput As String
myinput = InputBox("Enter No. of weeks")
For Each wks In ThisWorkbook.Worksheets
'I would include the following code, if i did not wish for certain worksheets
'to be excluded from the macro
'If Not wks Is sheet3 _
And Not wks.Name = "sheet2" _
And Not wks.Name = "sheet4" Then
'##########################################################################
'*****My error lies here*****
Range("A1:A100").Select
Selection.Find(What:=myinput, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select
'##########################################################################
wks.Range("A" & ActiveCell.Row).Resize(, 10).Copy
Sheets("Sheet3").Select
Range("a5000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
'End If
Next wks
End With
End Sub
Any assistance would be gratefully appreciated.
Thanks
BT