Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Visual basic in Excel


  • Please log in to reply
2 replies to this topic

#1 donuteh

donuteh

  • Members
  • 25 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:100 Mile House
  • Local time:09:24 AM

Posted 04 March 2011 - 01:46 PM

Hi, I'm new to VB programming (otherwise this would probably be easy) and I'm trying to create a macro that will only copy certain rows.

I have two work sheets. "Sheet 1" and "sheet 2" (easy names)
Sheet 1 contains the raw data, Column A has an if formula that determines what rows are needed. (1=yes 0=no)
I need my macro to copy all the rows marked with a 1 and paste them onto sheet 2.

Any help on this would be greatly appreciated.

Thank you
The speed of sound is defined by the distance from door to computer divided by the time interval needed to close the media player and pull up your pants when your mom shouts "OH MY GOD WHAT ARE YOU DOING!!!"

BC AdBot (Login to Remove)

 


#2 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,568 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:01:24 PM

Posted 08 March 2011 - 08:23 PM

You can make a macro, but do you really need it?

You can do a quick manual copy like this:
On Sheet1 insert a header row with field names such as Flag, data1, data2 ... if not already there
Use menu Data > Autofilter
Click on the filter downarrow and Select 1
Select the filtered rows
Copy, go to the other sheet, paste. Done.

You can actually record this activity as a macro and in VBA just tweak the fixed locations to something like selection, to be totally flexible for any range of source as well as destination.

The best way to learn how to write macros is to let Excel record and then edit the code for flexibility, IMO. After that, navigate through VBA helps for objects, methods, properties. Don't forget you can click F1 on any part of what you see in the VBA code to get the full syntax and links to related commands. Such as ActiveSheet, ActiveCell, Range, .row(), .column(), .address, .columns.count() etc.

Edited by tos226, 08 March 2011 - 09:20 PM.


#3 donuteh

donuteh
  • Topic Starter

  • Members
  • 25 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:100 Mile House
  • Local time:09:24 AM

Posted 09 March 2011 - 09:23 AM

Thank you, I hadn't thought of trying to record a macro that would use filters. I figured out how to do it though. this is what I came up with. it seems to work for what I need it for.

Thank you for your help.
Dirk

Sub Sort()

'copy the information from the template and paste the values in the sorted data sheet

Dim c As Range, strSearch As String

'Select Sorted sheet & position the first pasted row

[b]
Worksheets("Sheet2").Select
Range("A2").Select

Do
    'Moves selection down the spreadsheet in the first column

    If IsEmpty(ActiveCell) = False Then
    ActiveCell.Offset(1, 0).Select

    End If

    'moves selection to first clear cell on far left side of worksheet

    Loop Until IsEmpty(ActiveCell) = True
    
'Move back to Template sheet

Worksheets("sheet1").Select

'search column A for number 1 in values

strSearch = 1
With Worksheets("sheet1").Range("A:A")
    Set c = .Find(strSearch, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            'Copy Row

            c.EntireRow.copy
                        
            'Select target sheet (sorted), paste, move to next row, select source sheet (Template)

            [b]
            Worksheets("sheet2").Select
            Selection.PasteSpecial Paste:=xlPasteValues
            ActiveCell.Offset(1, 0).Select
            Worksheets("sheet1").Select
            
            'find next instance

            Set c = .FindNext(c)
            'loop until no more instances of number 1 is found

        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

End Sub


Edited by donuteh, 09 March 2011 - 09:24 AM.

The speed of sound is defined by the distance from door to computer divided by the time interval needed to close the media player and pull up your pants when your mom shouts "OH MY GOD WHAT ARE YOU DOING!!!"




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users