Macro to copy and paste values Solved/Closed

Question

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

rizvisa1 · Answer

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.

rizvisa1 · Answer

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

SLKG · Answer

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

rizvisa1 · Answer

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

Macro to copy and paste values

4 responses

Similar discussions

Subscribe To Our Newsletter!