VBA to copy values between sheets skipping blanks

[cam89]

Hello everyone!

So just so everyone knows, I am a VBA newb, self-taught through extremely helpful forums like these. This is my first post and haven't even set up my profile fully. Lol This request will help me and my accounting team here at my job out tremendously! So, thank you for taking your time to help and I will explain as best as I can on what I am needing help on. I have tried to build it myself, but I can't seem to figure it out and haven't found a direct solution online.

My Workbook Definitions:

• All within Same Workbook
• Worksheets: "Sheet1" & "Sheet2" (for simplicity)
• "Sheet1" is the source; source columns in this forum are "O" as Billing_Code, "A" as Invoice, and "Q" as Monthly_Fee; Headers start on Row6.
• "Sheet2" is the destination; destination columns in this forum are "E" = Billing_Code, "C" = Invoice, and "J" = Monthly_Fee; Headers start on Row1.

My Request:

I am needing a script to do the following in order:

1. Loop though the first column (for my worksheet - Sheets("Sheet1").Column("O") ) and skip any Blank cells.
2. For every value found in Column("O"), copy and paste the found value as well as select values from the same row [Column("A") & Column("Q")] over into another existing sheet, Sheets("Sheet2")
3. Once Column("O") has been looped through and all found/related data has been moved to "Sheet2", then I need to be able to have this script loop back through "Sheet1", but with a different source column other than "O" and return related row data and insert into "Sheet2" as it did for the first loop and have the new data follow after the first data set in "Sheet2".

For Example) "Sheet1" contains rows of multiple invoices whereas the columns contain multiple Billing_Codes and associated Monthly_Fees, so the Idea is that the script will loop through the first set of Billing_Codes (given example, Column("O")) and return all the needed data into "Sheet2" within a table set up to map into our Accounting Software and then once Columne("O") is done, it will do the same for the next set of Billing_Codes and associated Monthly_Fees, keeping the Invoice data the same, and loop through the next until all defined Billing_Code columns have been satisfied. I gave only one Billing_Code example, but if you could show how I would add it continue to the next set, I would greatly appreciated it!

Below is the code that I have tried to build myself but hasn't worked for me. Keep getting the Error: "Run-time error '9': Subscript out of Range"

Thank you for everyone's help in advance! Like I said this would help us out so much and cut down so much time.

Thank you!
Cam89

Private Sub CommandButton1_Click()
Dim ws, ws2 As Worksheets
Dim i As Long
Dim MY_LAST_ROW As Long


Set ws = ThisWorkbook.Worksheets("OnDemand")
Set ws2 = ThisWorkbook.Worksheets("IN Detail Test")


MY_LAST_ROW = ws2.UsedRange.Rows.Count + 1


Application.ScreenUpdating = False


    With Sheets("OnDemand")


        For i = 2 To .Cells(ws.Rows.Count, "O").End(xlUp).row
            If Not IsEmpty(ws.Cells(ws.Rows.Count, "O").Value) Then
                Billing_Code = .Range("O" & i).Value
                Invoice = .Range("A" & i).Value
                Monthly_Fee = .Range("Q" & i).Value
                
                With Sheets("IN Detail Test")
                    ws2.Cells(MY_LAST_ROW, 5).Value = Billing_Code
                    ws2.Cells(MY_LAST_ROW, 3).Value = Invoice
                    ws2.Cells(MY_LAST_ROW, 10).Value = Monthly_Fee
                End With
            End If
        Next i
    End With
Application.ScreenUpdating = True


End Sub

 

[cam89]

Sorry Everyone, my code above has the actual sheet names, but in my request I kept it to "Sheet1" & "Sheet2"....."Sheet1" = "OnDemand" and "Sheet2" = "IN Detail Test"...... Sorry for the confusion, meant to change it prior to uploading.

 

[B4andafter]

I modified your code with corrections, for now try the following code for items that refer to Column O. I could not complete the rest because the other columns are not known.
To keep it simple I would create another macro and copy the code below and update with whatever Columns you are needed to copy to sheet 2.

Private Sub abc1()


Dim i As Long
Dim lastrow1 As Long
Dim MY_LAST_ROW As Long


lastrow1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row


Application.ScreenUpdating = False


Sheets("sheet1").Activate


        For i = 1 To lastrow1
            If Not IsEmpty(Cells(i, "O")) Then
                Billing_Code = Sheet1.Cells(i, 15).Value
                Invoice = Sheet1.Cells(i, 1).Value
                Monthly_Fee = Sheet1.Cells(i, 17).Value
                MY_LAST_ROW = Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
                                
                With Sheets("sheet2")
                    Sheets("sheet2").Cells(MY_LAST_ROW, 5).Value = Billing_Code
                    Sheets("sheet2").Cells(MY_LAST_ROW, 3).Value = Invoice
                    Sheets("sheet2").Cells(MY_LAST_ROW, 10).Value = Monthly_Fee
                End With
            End If
        Next i
    
Application.ScreenUpdating = True


End Sub

MS Office Pro 2016

 

[cam89]

Hey B4andafter, Thank you for the corrections. The code below is being shown on the debugger and giving me the error again for "Run-time error '9': Subscript out of Range". Do you know maybe why?

lastrow1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

 

[Fluff]

You need to change the sheet names to match your actual sheet names.

 

[cam89]

I did Fluff, I had just replied back with the example names used in the forum to keep consistency. Thanks.

 

[Michael M]

check your sheet names for EXACT text, with no leading / trailling spaces.
Error 9 generally means you have a sheet named incorrectly OR a sheet with that name doesn't exist

 

[cam89]

I had a proceeding blank, thank you all! Seems to be working perfectly. I will add to it for the additional columns and see if I have any issues, If I do, I will post back here to see if y'all can help. Thank you again!