+ Reply to Thread
Results 1 to 4 of 4

How to write Macro that could cut and paste data

Hybrid View

ims0phie How to write Macro that could... 04-05-2013, 11:47 AM
AlphaFrog Re: How to write Macro that... 04-05-2013, 01:42 PM
ims0phie Re: How to write Macro that... 04-05-2013, 11:52 PM
ims0phie Re: How to write Macro that... 04-06-2013, 11:34 AM
  1. #1
    Registered User
    Join Date
    04-05-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    13

    Red face How to write Macro that could cut and paste data

    I'm ground zero at macro, and I'm just starting to learn how to use macro.
    I'm an accountant at a company, and I'm having hard time with the manual copy paste of the data.
    So then I started to look for a simple script in excel that could just do the trick, then i learned about macro.

    Is it possible to cut data on sheet2, then transfer it on sheet 1, paste beside it, if the check number and amount is the same as the one on the balance per bank?

    sample.xlsx

    my hands sore badly, while doing the manual copy and paste thing in the keyboard..
    any help would be gladly appreciated..
    Last edited by ims0phie; 04-21-2013 at 08:24 AM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: How to write Macro that could cut and paste data with a vlookup (sort of function)?

    Sub Copy_Check_Data()
        Dim Found As Range, FirstFound As String, rngCheck As Range, counter As Long
        Dim ws1 As Worksheet, ws2 As Worksheet
        Set ws1 = Sheets("Sheet1")  'Destination worksheet
        Set ws2 = Sheets("Sheet2")  'Source worksheet
        Application.ScreenUpdating = False
        For Each rngCheck In ws1.Range("C:C").SpecialCells(xlCellTypeConstants, xlNumbers)
            If IsEmpty(rngCheck.Offset(, 5)) Then
                Set Found = ws2.Range("C:C").Find(What:="Check No." & rngCheck.Value, _
                                                  LookIn:=xlValues, _
                                                  LookAt:=xlWhole, _
                                                  SearchOrder:=xlByRows, _
                                                  SearchDirection:=xlNext, _
                                                  MatchCase:=False)                                                           
                If Not Found Is Nothing Then
                    FirstFound = Found.Address
                    Do
                        If Found.Offset(, 5).Value = rngCheck.Offset(, 1).Value Then
                            Found.Offset(, -2).Resize(, 8).Copy Destination:=rngCheck.Offset(, 5)
                            counter = counter + 1
                            Exit Do
                        End If
                        Set Found = ws2.Range("C:C").FindNext(After:=Found)
                    Loop Until Found.Address = FirstFound
                End If
            End If
        Next rngCheck
        Application.ScreenUpdating = True
        MsgBox counter & " checks copied. ", vbInformation, "Copy Check Data Complete"
    End Sub
    Last edited by AlphaFrog; 04-05-2013 at 01:44 PM.

  3. #3
    Registered User
    Join Date
    04-05-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: How to write Macro that could cut and paste data with a vlookup (sort of function)?

    wow! amazing.. thank you..

    can we add function that would delete the data copied from sheet 2?
    so that i would know which one is copied and not?

  4. #4
    Registered User
    Join Date
    04-05-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: How to write Macro that could cut and paste data with a vlookup (sort of function)?

    weee.. i tried to experiment.. and it worked.. i tried to change your "copy" code to cut, and it did cut.. thank you very much for your help..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1