Wedds
07-06-2016, 12:24 AM
Hi everyone,
Its been a while since I've used vba. I've written this code where the user clicks a button on a worksheet and the data in various rows are cut and pasted onto a different tab, as seen below. I have also enclosed an example of the worksheet for you to see an example. Is it possible to do the same operation with different data sets from different tabs to be copied into a new blank workbook on one tab? Is it possible for the code to be flexible depending on the user's data input and column headers to change the data - and click the button and data to be parsed onto the same workbook on the same worksheet? All the data has to be collated on the same worksheet as report to be then connected to an Oracle database. Can you amend my code to do this? You can play around with my worksheet to help me with a suggestion on how to do this? Also, you can send me resources or ideas that may support and my learning on how to do this? Maybe you have worked on a similar project and may have some dynamic ideas or code that processed without any errors. Please feel free to share, as I am relatively basic vba coder. (I have also copied the code below. Please feel free to amend the code to adjust to my requirements)
Thanks for your support.
Regards,
Andy
Option Explicit
Sub InventoryUpdate()
Dim ItemID As Long
Dim ProductName As String
Dim Delivery As Integer
Dim keepsearching As Boolean
Dim rownum As Long
ItemID = Range("A2").Value
ProductName = Range("b2").Value
Delivery = Range("C2").Value
keepsearching = True
rownum = 3
Worksheets("Inventory").Activate
Do Until keepsearching = False
If Cells(rownum, 1).Value = ItemID Then
Cells(rownum, 3).Value = Cells(rownum, 3).Value + Delivery
Cells(rownum, 4).Value = Date
keepsearching = False
ElseIf Cells(rownum, 1).Value = "" Then
Cells(rownum, 1).Value = ItemID
Cells(rownum, 2).Value = ProductName
Cells(rownum, 3).Value = Delivery
Cells(rownum, 4).Value = Date
Else
rownum = rownum + 1
End If
Loop
Worksheets("Update").Activate
Range("A2").Select
Range("A2:C2").ClearContents
MsgBox "The inventory sheet has been updated"
End Sub
Its been a while since I've used vba. I've written this code where the user clicks a button on a worksheet and the data in various rows are cut and pasted onto a different tab, as seen below. I have also enclosed an example of the worksheet for you to see an example. Is it possible to do the same operation with different data sets from different tabs to be copied into a new blank workbook on one tab? Is it possible for the code to be flexible depending on the user's data input and column headers to change the data - and click the button and data to be parsed onto the same workbook on the same worksheet? All the data has to be collated on the same worksheet as report to be then connected to an Oracle database. Can you amend my code to do this? You can play around with my worksheet to help me with a suggestion on how to do this? Also, you can send me resources or ideas that may support and my learning on how to do this? Maybe you have worked on a similar project and may have some dynamic ideas or code that processed without any errors. Please feel free to share, as I am relatively basic vba coder. (I have also copied the code below. Please feel free to amend the code to adjust to my requirements)
Thanks for your support.
Regards,
Andy
Option Explicit
Sub InventoryUpdate()
Dim ItemID As Long
Dim ProductName As String
Dim Delivery As Integer
Dim keepsearching As Boolean
Dim rownum As Long
ItemID = Range("A2").Value
ProductName = Range("b2").Value
Delivery = Range("C2").Value
keepsearching = True
rownum = 3
Worksheets("Inventory").Activate
Do Until keepsearching = False
If Cells(rownum, 1).Value = ItemID Then
Cells(rownum, 3).Value = Cells(rownum, 3).Value + Delivery
Cells(rownum, 4).Value = Date
keepsearching = False
ElseIf Cells(rownum, 1).Value = "" Then
Cells(rownum, 1).Value = ItemID
Cells(rownum, 2).Value = ProductName
Cells(rownum, 3).Value = Delivery
Cells(rownum, 4).Value = Date
Else
rownum = rownum + 1
End If
Loop
Worksheets("Update").Activate
Range("A2").Select
Range("A2:C2").ClearContents
MsgBox "The inventory sheet has been updated"
End Sub