Previous Topic Next Topic

Macro to copy and paste values

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
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 replies

Answer 1 / 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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.
Answer 2 / 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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
Answer 3 / 4

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
Answer 4 / 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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
Report

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
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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
Report
Posts
6
Registration date
Friday April 16, 2010
Status
Member
Last seen
April 27, 2010
It worked! Thank You So Much!!! You are a genius!! :)
Now I know what to do with all the rest as well!!!
SLKG
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
You are welcome. best of luck,
Ask a question