Skip to next row

pollofeliz32 · #1 04-26-2019, 04:52 AM

Hello all (I might have posted this in the wrong forum),

Currently I have two Excel Sheets, one named "CHECKSHEET" and the other "WorkRequestInfo". In the CHECKSHEET, the user enters some information that is copied onto WorkRequestInfo after a button is pressed. My only issue now is i cannot seem to be able to have the code skip to the next row after the data is entered. I want the code to skip to the next row for when the next user enters information for the next data entry and not overwritte the previous entry

Code:

Sub SubmitWRData()
Dim refTable As Variant, trans As Variant
refTable = Array("B = D1", "C = D2", "D=D3", "E=D4", "F=D5", "K=B6", "L=D6")
Dim Row As Long
Row = Worksheets("WorkRequestInfo").UsedRange.Rows.Count + 1
For Each trans In refTable
    Dim Dest As String, Field As String
    Dest = Trim(Left(trans, InStr(1, trans, "=") - 1)) & Row
    Field = Trim(Right(trans, Len(trans) - InStr(1, trans, "=")))
    Worksheets("WorkRequestInfo").Range(Dest).Value = Worksheets("CHECKSHEET").Range(Field).Value
Next

End Sub

Thanks!

NoSparks · #2 04-26-2019, 06:18 AM

Row is a key word in VBA and should not be used as a variable name.
Try this

Code:

Sub SubmitWRData()
    Dim refTable As Variant, trans As Variant
    Dim writeRow As Long
    Dim Dest As String, Field As String

refTable = Array("B = D1", "C = D2", "D=D3", "E=D4", "F=D5", "K=B6", "L=D6")
           
writeRow = Worksheets("WorkRequestInfo").Range("B" & Rows.Count).End(xlUp).Row + 1

For Each trans In refTable
    Dest = Trim(Left(trans, InStr(1, trans, "=") - 1)) & writeRow
    Field = Trim(Right(trans, Len(trans) - InStr(1, trans, "=")))
    Worksheets("WorkRequestInfo").Range(Dest).Value = Worksheets("CHECKSHEET").Range(Field).Value
Next

End Sub

pollofeliz32 · #3 04-26-2019, 07:04 AM

Thank you so so much!!!! It works now

I haven't programmed in so long (it's been close to 10 years) & had never done anything in Excel, I am hoping into getting back into it so that I can put together some tools for my company

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Skip to next row after data is entered	pollofeliz32	Excel	1	04-26-2019 05:49 AM
Using skip record as well as if, then, else	azza	Mail Merge	5	10-11-2014 11:25 PM
Transpose cells and skip?	ItsjustB	Excel	1	01-09-2013 08:53 AM
skip to next sentence?	moreenz	Word	3	08-16-2012 02:41 PM
skip duplicate letters	karen h	Mail Merge	10	03-23-2011 08:37 AM