how to copy data from non-consecutive columns and paste in another sheet using vba

[bebe1279]

Hi everyone

I'm trying to get this to work but I just don't have a lot of experience with VBA.
What I'm trying to do is to get data from "sheet3", columns 1, 2 and 5 and paste them on the "summary" sheet starting at row A7.

The workbook that I'm working with has 37 sheets, 35 of those sheets contains data in columns 1, 2 & 5 (in all sheets) that I'd like to display on the "summary" sheet. the summary sheet is divided into to "charts"; the first is from A7 to D13 and the second is from A22 to D49. if anyone could help with fixing the below code, that would be great. Also, if anyone has a better way of doing it I'd really appreciate it. I was going to copy/paste the code 30+x and simply change the sheet name etc.

Application.ScreenUpdating = 0

'With Worksheets("1235 Law 11-8a")
With Sheet3 'using index,shorter neam

 ' Find last row of data
  lr = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

  x = 7
  
        For i = 2 To lr
            If Range("A" & i) <> "" Then
                .Range(.Cells(i, 1), .Cells(i, 2), .Cells(i, 5)).Copy
               With Worksheets("SUMMARY")
                    .Cells(x, 1).PasteSpecial xlPasteFormulasAndNumberFormats
               End With
            End If
        Next i

End With
 
With Application
        .CutCopyMode = False
        .ScreenUpdating = 1
    End With

End Sub

 

[tlowry]

Here is a start. The code copies the three columns from each non "summary" sheet into "summary" sheet. It's not clear if you want the three columns in "summary" to be A,B and C, but that's what I did. In addition, I can't grok what you want with the charts.

Sub CopySomeCols()
    Const rngAdd = "A:A,B:B,E:E"
    Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Sheets
        Select Case sh.Name
            Case "summary"
            Case Else
                With Sheets(sh.Name)
                    On Error Resume Next
                    lr = .Range(rngAdd).SpecialCells(XlCellType.xlCellTypeLastCell).Row
                    If Err.Number <> 0 Then Exit For
                    Application.Intersect(Range(.Cells(2, 1), .Cells(lr, 5)), .Range(rngAdd)).Copy
                End With
                With Sheets("summary")
                    lr = .Cells(Rows.Count, 1).End(xlUp).Row
                    If lr < 7 Then lr = 6
                    .Range("A1").Offset(lr, 0).PasteSpecial xlAll
                End With
        End Select
    Next sh
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

 

[bebe1279]

Hi tlowry, thank you for your reply. Let me explain myself (hopefully better) to see if what you so kindly provided would indeed work for my needs. I don't have the file with me and can't try your solution at the moment.

So here is what I want to do. I have a workbook with about 37 sheets (for now, will more than likely change, grow). The first sheet is a summary sheet, the second is a directory sheet. From sheet three on, all sheets contain columns for say; address, total price, balance, payment & pending balance. I need to get the last row of data (which will be the most recent data) and copy the address, total price & pending balance over to the summary sheet (into columns A, B & C respectively).
However, as the workbook stands now; sheet 3-9 belong to say, vendor A and this data (last row, columns A, B & E) needs to be entered into summary sheet but starting at cell A7 (due to headers and company info and such). From sheet 10-37, I need the same thing. the difference here is that it belongs to a different vendor and the starting cell would be A22.

Since sheets for both vendors will more than likely be added (with the same columns of data and therefore the same operations as above will be preformed), I'd like to find a way to be able to add these sheets without having to make heavy edits to the code. so I was thinking of creating two arrays, one for each vendor. this way the new sheets could be added to the arrays and much of the rest of the code would be unchanged. what do you think about this?

 

[tlowry]

When you do give it a go, post the results. Please put in some data and examples of what you want done.

 

[bebe1279]

Hi tlowry

Here is my attempt at using an array to store my sheets and then cycling through those specified sheet (within the array) to pull out the data that I need.

Sub test()

Dim yws As Worksheet
Dim lr As Long
Dim adr As String
Dim jobt As Long
Dim bal As Long

'Loop through worksheets of array
For Each yws In Sheets(Array(Sheets(3).Name, Sheets(4).Name, Sheets(5).Name, Sheets(6).Name, Sheets(7).Name, Sheets(8).Name, Sheets(9).Name))
    With yws
    'find last row of data
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    
    'add values to variables
    adr = .Cells(lr, 1).Value
    jobt = .Cells(lr, 2).Value
    bal = .Cells(lr, 5).Value
    End With
        'dump data into summary sheet
        With Worksheets("SUMMARY")
        'start at row 7
        x = 7
            'assign values to cells
            .Cells(x, 1).Value = adr
            .Cells(x, 2).Value = jobt
            .Cells(x, 3).Value = bal
        'move to next row
        x = x + 1
        End With
Next 
End Sub

This code runs without errors but for some reason that I can't see, it adds the data to the summary sheet row 7; replacing the previous data. So when the code finishes I end up with the data that I want but only from "sheet(9).Name" on the 7th row instead of the 13th row.
Any ideas as to what I'm doing wrong?

 

[tlowry]

I need data! I still don't know what you want. What was the outcome of trying my code?

I can't fix your code until I know what it is meant to do.

 

[bebe1279]

Your code generated results starting at A22 but it also seemed to output triple the rows. Thanks for trying without any data or idea of what I was trying to do.

Ok, here is what I’m trying to do. I have a bunch of open invoices from 2 different vendors; say vendor A & B. Vendor A has seven open invoices with balances of $1,000 – $7,000. Vendor B has 28 open invoices with balances of $1,000 – $28,000. My workbook consists of one sheet for every open invoice these two vendors have (the worksheets themselves all consist of five columns; address, invoice total, balance, payment & new balance) plus a summary sheet (which consists of four columns A-D and Vendor A & B’s info. The range for vendor A is A1-D13 and the range for vendor B is A16-D49) and a directory sheet (which is just link to get to the individual invoice sheets).
What I’d like to do is pull/copy the address (col A), invoice total (col B) and new balance (col E) for each sheet, last row (because it is the most recent data) and pass/paste it to the summary sheet. For vendor A this means start the pass/paste at A7 and for vendor B it would be A22.

The code I put up earlier is just for vendor A. I figured once I have that working properly, it would just be a matter of copy/paste into a new module and adjusting the array and ranges.

Does this help?

 

[bebe1279]

Forgot to include this

An example of vendor A summary would be something like:

123 main street (A7), $10,000 (B7), $1,000 (C7)
456 main street (A8), $12,000 (B8), $2,000 (C8)
789 main street (A9), $13,000 (B9), $3,000 (C9)
123 state street (A10), $14,000 (B10), $4,000 (C10)
456 state street (A11), $15,000 (B11), $5,000 (C11)
789 state street (A12), $16,000 (B12), $6,000 (C12)
123 corner street (A13), $17,000 (B13), $7,000 (C13)

hope this helps because I can't figure out what I did wrong in the code
Thank you for taking the time to help me out

 

[oriwitt]

Your information provided to tlowry and possibly others might be assisted by using http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970 MrExcel's own HTML maker add-in, installation instructions provided on that link.

 

[tlowry]

Ok, I got what you are going for. (I hope)

I made the code sorter and, I hope, faster.

Sub test()
    Dim yws As Worksheet
    Dim x As Long
    For Each yws In Sheets(Array(Sheets(3).Name, Sheets(4).Name)) 'you fill in the rest...
        With yws
            x = Application.WorksheetFunction.Max(7, Sheets("SUMMARY"). _
                                    Cells(Rows.Count, 1).End(xlUp).Row + 1)
            Application.Intersect(.Range("A:A,B:B,E:E"), _
                .Range("A1").CurrentRegion).Copy Sheets("SUMMARY").Cells(x, 1)
        End With
    Next
End Sub