#1
|
|||
|
|||
Skip to next row
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |