View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Martin Fishlock[_3_] Martin Fishlock[_3_] is offline
external usenet poster
 
Posts: 59
Default Copy and Pasting into new Sheets

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


" 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