Excel Macro to copy report data into new sheet DB in next available row.

Hi Experts
I have a report that gets filled by scanner, I need it to send just the data in c4 through j4 and as  many rows are filled, into a new sheet into the next available row.
send-other-sheet.JPGSorry there are spaces....in columns ..
Thanks for your help/
Chris
Book1.xlsx
chris pikeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
You say "into a new sheet into the next available row". If the sheet is new, won't that always be row 1?
0
chris pikeAuthor Commented:
It will be a "different sheet" first time only will be a new sheet as it will have only the headings.
Thanks for helping to clarify.
0
Martin LissOlder than dirtCommented:
This will transfer the data to the next available row on the DB sheet. Do you need the blank rows?
Sub SendToDB()
Dim lngNextRow As Long
Dim lngLastRow As Long
Dim lngRow As Long

lngNextRow = Sheets("DB").Cells(Rows.Count, 1).End(xlUp).Row + 1

With Sheets("Sheet1")
     lngLastRow = .Range("C1048576").End(xlUp).Row
    ' .Range("C4:J" & lngLastRow).Copy Destination:=Sheets("DB").Cells(lngNextRow, "A")
    For lngRow = 4 To lngLastRow
        If .Cells(lngRow, "C") <> "" Then
            Sheets("DB").Cells(lngNextRow, "A") = .Cells(lngRow, "C")
            Sheets("DB").Cells(lngNextRow, "B") = .Cells(lngRow, "D")
            Sheets("DB").Cells(lngNextRow, "C") = .Cells(lngRow, "E")
            Sheets("DB").Cells(lngNextRow, "D") = .Cells(lngRow, "F")
            Sheets("DB").Cells(lngNextRow, "E") = .Cells(lngRow, "G")
            Sheets("DB").Cells(lngNextRow, "F") = .Cells(lngRow, "H")
            Sheets("DB").Cells(lngNextRow, "G") = .Cells(lngRow, "J")
            lngNextRow = lngNextRow + 1
        End If
    Next
End With

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

chris pikeAuthor Commented:
No I do not need spaces, it is preferable not to have them Thanks.
I tried this code, I get an error that says "Can't execute in break mode"

This Worksheet has other code as well, but it all happens prior to this "send to db" macro. I stripped it down to make it simple for you, as this is one step at a time for me.

This main sheet will populate 20 or 30 or 40 entries, then we send all the lines to DB, then reset and start again. The next 20 or 30 or 40 will also be sent, but to bottom of the list on the DB. The Db will grow and grow until I print everything out once a week.

Another awesome expert helped with the majority of this Question, Just need a little more help with a couple small things and I think it is done.
Thanks
Chris
0
Martin LissOlder than dirtCommented:
I've attached your workbook with my updated code and when I tested it it works fine and I assume when you try it that you won't get that error. I'd have to have your actual workbook, or a stripped down one that gives the error message in order to fix it. I'd also need step by step instructions on how to reproduce the error.
0
chris pikeAuthor Commented:
HI Martin,
I get a complile error, it highlights yellow line 1 : Sub SendToDB())
And highlights End With grey as well.
Capture03.JPGI have attached the sample file just to see if we can make it work.
Thanks
I sure I am missing something in the Cut and Paste from your code that I was supposed to drop in.
My VB skills are next to none. I can however make a button and associate a simple macro that I record to it. That is a start I guess.

Thanks
Book1.xlsm
0
Martin LissOlder than dirtCommented:
Every Sub needs an End Sub so after the End With, add an End Sub.

Sub SendToDB()
Dim lngNextRow As Long
Dim lngLastRow As Long
Dim lngRow As Long

lngNextRow = Sheets("DB").Cells(Rows.Count, 1).End(xlUp).Row + 1

With Sheets("Sheet1")
     lngLastRow = .Range("C1048576").End(xlUp).Row
    ' .Range("C4:J" & lngLastRow).Copy Destination:=Sheets("DB").Cells(lngNextRow, "A")
    For lngRow = 4 To lngLastRow
        If .Cells(lngRow, "C") <> "" Then
            Sheets("DB").Cells(lngNextRow, "A") = .Cells(lngRow, "C")
            Sheets("DB").Cells(lngNextRow, "B") = .Cells(lngRow, "D")
            Sheets("DB").Cells(lngNextRow, "C") = .Cells(lngRow, "E")
            Sheets("DB").Cells(lngNextRow, "D") = .Cells(lngRow, "F")
            Sheets("DB").Cells(lngNextRow, "E") = .Cells(lngRow, "G")
            Sheets("DB").Cells(lngNextRow, "F") = .Cells(lngRow, "H")
            Sheets("DB").Cells(lngNextRow, "G") = .Cells(lngRow, "J")
            lngNextRow = lngNextRow + 1
        End If
    Next
End With
End Sub
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chris pikeAuthor Commented:
Awesome, I'm BAck in the office today. Will give it a try.
Chris
0
chris pikeAuthor Commented:
Awesome help on this question. Being a novice with VB, I appreciate the extra help explaining the fundamentals to integrate theis code into my application.

Nice work.

CPike.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.