Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Pasting into new Sheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Pasting into new Sheets
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting Values between sheets in VBA | Excel Worksheet Functions | |||
Pasting on Filtered Data Sheets without pasting onto hidden cells | Excel Discussion (Misc queries) | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions | |||
Copy and pasting sheets with merged cells | Excel Programming | |||
HELP: Copying and pasting to Sheets... | Excel Programming |