VBA Copy Help

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,220
Please can anyone help on my problem

I have a macro that opens an input box where you insert a value, It then searches the columns for any match, after the matches are found the whole row is then copied into a seperate tab starting on cell A1. I would like the to copy into cell A7 onwards not A1.
Below is the VBA Code. Any help would be appreciated.


Dim Counter As Long
'-------------------------------------------------
'- SET SEARCH KEY
MyFind = InputBox("Please insert value to find.")
If MyFind = "" Then End
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS
On Error Resume Next
Set ws = ActiveSheet
Set FoundCell = ws.Cells.Find(what:=MyFind)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.Interior.ColorIndex = 4
FoundCell.EntireRow.Copy Destination:=Sheets("Results").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
LR = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Range("A8:A" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End If
rsp = MsgBox("Found " & Counter)
Sheets("Results").Select
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
Sheets("Sheet1").Select

End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,224,517
Messages
6,179,233
Members
452,898
Latest member
Capolavoro009

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top