Hello,
I'm new to VBA and was hoping that I could receive some assistance from this board. I've been reviewing previous posts to see if there was already a discussion on another thread that would provide me with some guidance, but I haven't been able to find anything.
I'm currently working on a project that will hopefully allow a user to be able to create custom price lists. Using Excel 2007, I have created a spreadsheet in a workbook called "InternationalPricingListBook". This spreadsheet consists of rows of information for each product that my company manufactures, located in columns A-G, including the prices of that product at different profit margins (e.g., 30%, 40%, 50%, and 60%) located in columns H-K. The idea is that, through an Input Box, a user will be able to select multiple margin prices for each of the different products that the user would like to appear in a price list (e.g., for product 222 located in row 107 the user can select a 40% margin in column I, for product 333 located in row 231 the user can select a 60% margin in column H, etc.). These selected margin prices, along with certain product information for each of the selected items (located in columns B-E in the InternationalPricingListBook) would then be copied and pasted to a new worksheet in a new workbook (entitled NewBook). In the NewBook, the product information for each product would be pasted into columns A-D beginning in row 6. The selected margin price for each of the different products would be pasted into Column E in the same row as the product it was selected for beginning in row 6.
As of now, I am able to copy and paste all of the selected margins into the NewBook. However, I am only able to select margins from one column(e.g., I can only select 30% margins in column H, or 50% margins in column J, but not margins from both column H and column J).
Furthermore, I am only able to copy and paste into the NewBook the product information (originally located in the InternationalPricingListBook in rows B through E) for the first price margin that was selected. So, for example, if I want to have items 111, 222, 333, and 444 on a price list, I'll click on the margins that I want for each product, but while the margins will be copied and pasted correctly, only the product information for item 111 will copy and paste into the NewBook.
Are any of you aware of a way in which I could have a user select different cells (the margins) in different columns (columns H-K in the InternationalPricingListBook) and have these cells be pasted into one column (column E) in the NewBook? Also, and most importantly, is there a way in which I can copy the product information (in columns B-E) for all of the selected cells (the margins) - and not just the first one - and paste that information into columns A-D in the NewBook?
Here is the relevant code that I have been working on:
Dim InternationalPricingListBook As Workbook, NewBook As Workbook
Dim MySelection As Range
Dim MyRange As Range
'Copying and pasting the selected margin
InternationalPricingListBook.Activate
Set MySelection = Application.InputBox(prompt:="Select a listed margin.", Type:=8)
MySelection.Select
Application.Selection.Copy
NewBook.Activate
Application.Goto ActiveWorkbook.Worksheets(1).Range("E6")
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Transpose:=False
'Copying and pasting the product information
InternationalPricingListBook.Activate
Set MyRange = Range("B" & MySelection.Row & ":E" & MySelection.Row)
MyRange.Select
Application.Selection.Copy
NewBook.Activate
Application.Goto ActiveWorkbook.Worksheets(1).Range("A6")
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Transpose:=False
Again, I'm new to both VBA and the board, so if I've done something wrong in regard to posting or if I'm making a ridiculous mistake with my VBA code, please don't hesitate to let me know.
Thanks in advance for the help and the time that you took to read this post!
I'm new to VBA and was hoping that I could receive some assistance from this board. I've been reviewing previous posts to see if there was already a discussion on another thread that would provide me with some guidance, but I haven't been able to find anything.
I'm currently working on a project that will hopefully allow a user to be able to create custom price lists. Using Excel 2007, I have created a spreadsheet in a workbook called "InternationalPricingListBook". This spreadsheet consists of rows of information for each product that my company manufactures, located in columns A-G, including the prices of that product at different profit margins (e.g., 30%, 40%, 50%, and 60%) located in columns H-K. The idea is that, through an Input Box, a user will be able to select multiple margin prices for each of the different products that the user would like to appear in a price list (e.g., for product 222 located in row 107 the user can select a 40% margin in column I, for product 333 located in row 231 the user can select a 60% margin in column H, etc.). These selected margin prices, along with certain product information for each of the selected items (located in columns B-E in the InternationalPricingListBook) would then be copied and pasted to a new worksheet in a new workbook (entitled NewBook). In the NewBook, the product information for each product would be pasted into columns A-D beginning in row 6. The selected margin price for each of the different products would be pasted into Column E in the same row as the product it was selected for beginning in row 6.
As of now, I am able to copy and paste all of the selected margins into the NewBook. However, I am only able to select margins from one column(e.g., I can only select 30% margins in column H, or 50% margins in column J, but not margins from both column H and column J).
Furthermore, I am only able to copy and paste into the NewBook the product information (originally located in the InternationalPricingListBook in rows B through E) for the first price margin that was selected. So, for example, if I want to have items 111, 222, 333, and 444 on a price list, I'll click on the margins that I want for each product, but while the margins will be copied and pasted correctly, only the product information for item 111 will copy and paste into the NewBook.
Are any of you aware of a way in which I could have a user select different cells (the margins) in different columns (columns H-K in the InternationalPricingListBook) and have these cells be pasted into one column (column E) in the NewBook? Also, and most importantly, is there a way in which I can copy the product information (in columns B-E) for all of the selected cells (the margins) - and not just the first one - and paste that information into columns A-D in the NewBook?
Here is the relevant code that I have been working on:
Dim InternationalPricingListBook As Workbook, NewBook As Workbook
Dim MySelection As Range
Dim MyRange As Range
'Copying and pasting the selected margin
InternationalPricingListBook.Activate
Set MySelection = Application.InputBox(prompt:="Select a listed margin.", Type:=8)
MySelection.Select
Application.Selection.Copy
NewBook.Activate
Application.Goto ActiveWorkbook.Worksheets(1).Range("E6")
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Transpose:=False
'Copying and pasting the product information
InternationalPricingListBook.Activate
Set MyRange = Range("B" & MySelection.Row & ":E" & MySelection.Row)
MyRange.Select
Application.Selection.Copy
NewBook.Activate
Application.Goto ActiveWorkbook.Worksheets(1).Range("A6")
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Transpose:=False
Again, I'm new to both VBA and the board, so if I've done something wrong in regard to posting or if I'm making a ridiculous mistake with my VBA code, please don't hesitate to let me know.
Thanks in advance for the help and the time that you took to read this post!
