Copy and Pasting into new Sheets

[[email protected]]

Sorry, its my 1st time working with Visual Basic i've been looking
though a lot of websites but cant find anything. i have some knowledge
with C++ and its kinda annoying not being able to write such an easy
macro.
what im trying to do is:

1. Find keyword "yyyyy" in column A
2. Find keyword "xxxx" in column A.
3. Highlight every row in between including columns A-F
4. Copy and paste in a new Sheet.
5. Start from where 2. ended and loop 1.-5. again.

thank you for reading this and hopefully help me understand how VB
works in excel. i would greatly appreciate it

[Martin Fishlock]

Richa

Try this,
'-----------------------------------------------------
Option Explicit

Const csz_s1 As String = "yyyyyy"
Const csz_s2 As String = "xxxxxx"

Sub copyrows()
'
Dim dp As Long
Dim sp As Long
Dim b_open As Boolean
Dim wss As Worksheet
Dim wsd As Worksheet
Dim wbd As Workbook
Set wss = ActiveSheet ' point to current data sheet
Set wbd = Workbooks.Add ' new workbook
Set wsd = ActiveSheet ' point to new destination sheet

dp = 1 'destination pointer
b_open = False
For sp = 1 To 65536
With wss.Range("A" & sp)
If .Value = "" Then ' end
Set wss = Nothing
Set wbd = Nothing
Set wsd = Nothing
Exit Sub
End If
If .Value = csz_s1 Then
b_open = True
End If
If b_open Then
wss.Range("A" & sp & ":F" & sp).Copy wsd.Range("A" & dp)
dp = dp + 1
End If
If .Value = csz_s2 Then
b_open = False
End If
End With
Next sp
End Sub
'------------------------------------------------------------
--
HTHs Martin


"[email protected]" wrote:

> Sorry, its my 1st time working with Visual Basic i've been looking
> though a lot of websites but cant find anything. i have some knowledge
> with C++ and its kinda annoying not being able to write such an easy
> macro.
> what im trying to do is:
>
> 1. Find keyword "yyyyy" in column A
> 2. Find keyword "xxxx" in column A.
> 3. Highlight every row in between including columns A-F
> 4. Copy and paste in a new Sheet.
> 5. Start from where 2. ended and loop 1.-5. again.
>
> thank you for reading this and hopefully help me understand how VB
> works in excel. i would greatly appreciate it
>
>

[Yngve]

Hi, richa

The best way for you to learn something like this (the same as for
everyone
- myself included) is to use the Macro recorder on the Tools...Macros
menu. (Do it manual in excel, and stop recording.)

This is always the best way to start when you don't know what to do.


click Stop on the Tools..Macros menu, and use ALT-F8 to show the Macros
dialog.
You can also search "Find " in help in VB,

regards Yngve