Previous Topic Next Topic

Macro to copy and paste values

Solved/Closed
SLKG - 15 Apr 2010 à 20:11
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 17 Apr 2010 à 06:01
Hi
I am a complete novice when it comes to macros. What I am looking for is a macro that when run looks in Cell Q7 on Sheet 1 to see what number is in that cell (number changes everytime the book is opened) then copys ranges B17:B27, I17:I27, Q17:Q27 then looks on sheet 2 for all the numbers in colum A that match Q7 on sheet 1 (there are 11 consective rows that will have matching numbers - numbers start at 700001 and go up to 700451), then will Paste Special/Values along side those rows with the matching numbers in columns B, C, D. I have tried recording the macro but can't work it out. I am using excel 2003
Any help would be greatly appreciated.
Thanks
SLKG

Update
thanks for the response rizvisa1
here is the link
https://authentification.site/files/21966057/Product_Invoice.xls

4 responses

Réponse 1 / 4
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
15 Apr 2010 à 21:00
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
Réponse 2 / 4
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
16 Apr 2010 à 14:13
Could you please put up and updated file with some data. The date does not need to be true, but should be good enough for looking at the issue
Réponse 3 / 4
SLKG
16 Apr 2010 à 15:32
https://authentification.site/files/21976194/Product_Invoice.xls
Sorry I forgot I hadn't taken the original vlookups off on the first one.
Thx
SLKG
Réponse 4 / 4
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
16 Apr 2010 à 16:11
Based on your book, this should do it 
Sub moveData()
Dim PI_Sheet As String
Dim DC_Sheet As String
Dim invoiceLoc As Long
Dim invoice As Variant

    PI_Sheet = "Product Invoice"
    DC_Sheet = "Data Collection"

    Sheets(DC_Sheet).Select
    
    invoice = Sheets(PI_Sheet).Range("M7")
    
    invoiceLoc = 0
    On Error Resume Next
        invoiceLoc = Application.WorksheetFunction.Match(invoice, Range("A:A"), 0)
    
    On Error GoTo 0
    
    If (invoiceLoc > 0) Then
    
        With Sheets(PI_Sheet)
        
            Range("B" & invoiceLoc & ":B" & (invoiceLoc + 10)) = .Range("B17:B27").Value
            Range("C" & invoiceLoc & ":C" & (invoiceLoc + 10)) = .Range("G17:G27").Value
            Range("D" & invoiceLoc & ":D" & (invoiceLoc + 10)) = .Range("M17:M27").Value
            
        End With
    
    End If
    
End Sub
SLKG
16 Apr 2010 à 21:55
Thanks rizvisa1
but it simply pasted the values into columns B, C, D, on PI_Sheet not into columns B, C, D, on the DC_Sheet
SLKG
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
17 Apr 2010 à 05:46
I just tested again, For me it does correctly. Try with this modification and if it still does not work, you have to upload your test file with macro.

With Sheets(PI_Sheet)

Sheets(DC_Sheet).Range("B" & invoiceLoc & ":B" & (invoiceLoc + 10)) =Sheets(PI_Sheet).Range("B17:B27").Value

Sheets(DC_Sheet).Range("C" & invoiceLoc & ":C" & (invoiceLoc + 10)) = Sheets(PI_Sheet).Range("G17:G27").Value

Sheets(DC_Sheet).Range("D" & invoiceLoc & ":D" & (invoiceLoc + 10)) = Sheets(PI_Sheet).Range("M17:M27").Value

End With
SLKG Posts 6 Registration date Friday 16 April 2010 Status Member Last seen 27 April 2010
17 Apr 2010 à 05:58
It worked! Thank You So Much!!! You are a genius!! :)
Now I know what to do with all the rest as well!!!
SLKG
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
17 Apr 2010 à 06:01
You are welcome. best of luck,