1

Looking for some help please. I'm not a coder and i've tried a couple of different youtube videos but i can't get them to work. I have a spreadsheet where I store information on my business working hours, prices etc. I'd like to be able to have data pulled from the main sheet ("income" sheet1) based on the Invoice number (the same invoice number appears on multiple rows) and put into an invoice which is also formatted in excel.

So if the "invoice" spreadsheet (sheet 5), if cell G4 (for example is I111) matches any of the data in the first column of the "income" spreadsheet (sheet 1) (starting at row 6) then the corresponding data in columns 3, 8, 4 & 9 will copy over to the "invoice" spreadsheet in columns 2, 3, 4 & 7 (starting at row 13).

Then just because i like to make life really difficult, is there a way to press a button and the invoice spreadsheet to pop into a new workbook ready to be sent to customers.

Many thanks for any help you may be able to give. This is the code i have to start. If i could get it to work to copy data from just one column to another i was going to repeat the code separatly for each of the other columns.

Sub finddata()
Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim invoice As String
Dim finalrow As Integer
Dim i As Integer

Set datasheet = Sheet1
Set reportsheet = Sheet5
invoice = reportsheet.Range("g4").Value

reportsheet.Range("B13:b27").ClearContents

datasheet.Select
finalrow = Cells(Rows.Count, 1).End(x1up).Row


For i = 1 To finalrow
If Cells(i, 1) = invoice Then
(Cells(i, 3)).Copy

reportsheet.Select
Range("b27").End(x1up).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End If
juststuff123
  • 15
  • 2

1 Answers1

0

Try the following code for your problem:

Sub FindAndCopyData()
    Dim shData As Worksheet, shReport As Worksheet
    Set shData = Sheet1
    Set shReport = Sheet5
    
    Dim strInvoceNumber As String
    strInvoceNumber = shReport.Cells(4, "G").Value
    
    Dim intLastRow As Integer
    intLastRow = shData.Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim intReportRow As Integer
    intReportRow = 13
    
    shReport.Range("B13:B27").ClearContents
    
    Dim i As Integer
    For i = 1 To intLastRow
        If shData.Cells(i, 1).Value2 = strInvoceNumber Then
            shReport.Cells(intReportRow, 2).Value2 = shData.Cells(i, 3).Value2
            shReport.Cells(intReportRow, 3).Value2 = shData.Cells(i, 8).Value2
            shReport.Cells(intReportRow, 4).Value2 = shData.Cells(i, 4).Value2
            shReport.Cells(intReportRow, 7).Value2 = shData.Cells(i, 9).Value2
            
            intReportRow = intReportRow + 1
        End If
    Next i
    
End Sub

Please take a look at variable naming and proper indentation to make the code more readable. Also take a look at How to avoid using Select in Excel VBA, you almost never need to use .Select or .Activate in Excel VBA, it's faster and less error-prone to address objects directly, like it's done here shReport.Cells(intReportRow, 2).Value2.

You should also take a look at the line shReport.Range("B13:B27").ClearContents. Depending on your use case, you mightwant to extend this range to column G, and change row 27 to the last row of data.

Other than that the code should be pretty straightforward (some essential logic was already present in your code too), feel free to ask for clarification on specific parts if necessary.

It's of course possible to create a new workbook with only your invoice sheet in it, but there are already many resources to find on this site and others. If you get stuck, feel free to ask another question here. I usually like to save the current workbook to the new desired path and then making the necessary changes (e.g. deleting unwanted sheets), I find this to be easier than creating an entirely new workbook and moving sheets there.

riskypenguin
  • 2,139
  • 1
  • 10
  • 22