stirlingmw1
Board Regular
- Joined
- Jun 17, 2016
- Messages
- 53
- Office Version
- 2016
- 2013
- 2010
- 2007
- Platform
- Windows
Afternoon all
I hope I explain this right.
I have a worksheet that contains data from multiple shops and imported into the workbook to the worksheet "ImportData". This data then copied from "ImportData" into every other column of the worksheet "Main" using following code. The first 3 columns of the "Main" worksheet have "ID number", "category" and "item description" for multiple items, there are then 2 blank columns followed by the data from "importData" into every other column. This data is the qty of each item listed in the 1st 3 columns that is available across multiple stores with each store name in the first row of each imported column.
The data is added as mentioned into every other column from F to BE leaving the blank column for me to annotate an qty of the items that I require to be moved from the store listed in the first row to another. I am trying to put some code together that if I have annotated a qty in any of the cells the blank columns the code copies the "ID number", "category" and "item description" into the first 3 columns of worksheet "Supplied_from" followed by the Qty I have annotated and then the Store Name from the top of the column which the Qty is annotated in, the code should loop through all of the rows from row 4 to Lastrow and copy data each time it encounters a Qty in one of the blank columns. If there are multiple Qtys in a row all Qtys and Store names should be annotated in the "Supplied_from" worksheet.
I know this is a big ask but hope you can help.
regards
Steve
I hope I explain this right.
I have a worksheet that contains data from multiple shops and imported into the workbook to the worksheet "ImportData". This data then copied from "ImportData" into every other column of the worksheet "Main" using following code. The first 3 columns of the "Main" worksheet have "ID number", "category" and "item description" for multiple items, there are then 2 blank columns followed by the data from "importData" into every other column. This data is the qty of each item listed in the 1st 3 columns that is available across multiple stores with each store name in the first row of each imported column.
VBA Code:
Sub AddDataToMain()
Dim ImpLRow As Long, MLRow As Long, i As Long
Dim wsI As Worksheet, wsM As Worksheet
Dim Ary As Variant
Set wsM = Worksheets("Main")
Set wsI = Worksheets("ImportData")
'Adds NSN ADAC and Description to Main page
ImpLRow = wsI.cells(Rows.Count, 1).End(xlUp).Row
MLRow = wsM.cells(Rows.Count, 1).End(xlUp).Row
Ary = wsI.Range("A4:BE" & ImpLRow).Value2
wsM.Range("A4:C" & ImpLRow).Value = Application.Index(Ary, Evaluate("row(1:" & ImpLRow - 3 & ")"), Array(1, 2, 3))
For i = 4 To UBound(Ary, 2)
wsM.Range("E4:E" & ImpLRow).Offset(, (i - 3) * 2 - 1).Value = Application.Index(Ary, , i)
Next i
End Sub
The data is added as mentioned into every other column from F to BE leaving the blank column for me to annotate an qty of the items that I require to be moved from the store listed in the first row to another. I am trying to put some code together that if I have annotated a qty in any of the cells the blank columns the code copies the "ID number", "category" and "item description" into the first 3 columns of worksheet "Supplied_from" followed by the Qty I have annotated and then the Store Name from the top of the column which the Qty is annotated in, the code should loop through all of the rows from row 4 to Lastrow and copy data each time it encounters a Qty in one of the blank columns. If there are multiple Qtys in a row all Qtys and Store names should be annotated in the "Supplied_from" worksheet.
I know this is a big ask but hope you can help.
regards
Steve