Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-26-2019, 04:52 AM
pollofeliz32 pollofeliz32 is offline Windows 7 32bit Office 2010
Novice
 
Join Date: Apr 2019
Posts: 3
pollofeliz32 is on a distinguished road
Default 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
Thanks!
Reply With Quote
  #2  
Old 04-26-2019, 06:18 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 685
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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
Reply With Quote
  #3  
Old 04-26-2019, 07:04 AM
pollofeliz32 pollofeliz32 is offline Windows 7 32bit Office 2010
Novice
 
Join Date: Apr 2019
Posts: 3
pollofeliz32 is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 10:38 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft