GolfingTitan116
New Member
- Joined
- Dec 3, 2021
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Hello all, long time lurker, new time poster. I've learned a lot from these forums, but I'm not able to find an answer for this.
I have a spreadsheet where the user fills out a form then hits a button. The VBA then takes that form and places the data in the correct columns on the next tab so we can assign formulas, formatting, calculations, etc.
What I would like to have happen is, when they hit the button, the VBA code adds the word "model" after each item it copies out of one column from the form, and adds the word "drawing" to each item it copies from another column on the form.
The below code works, but it only works on the first item it copies and pastes. I don't know how to make it copy down for ALL items it copies.
I'm learning VBA, so I may be off base here, but I think it has to do with the + 1 on the 2nd copies. I've tried different variations of everything I could think of, and didn't get anything to work.
Here's the Code:
Any help would be greatly appreciated
I have a spreadsheet where the user fills out a form then hits a button. The VBA then takes that form and places the data in the correct columns on the next tab so we can assign formulas, formatting, calculations, etc.
What I would like to have happen is, when they hit the button, the VBA code adds the word "model" after each item it copies out of one column from the form, and adds the word "drawing" to each item it copies from another column on the form.
The below code works, but it only works on the first item it copies and pastes. I don't know how to make it copy down for ALL items it copies.
I'm learning VBA, so I may be off base here, but I think it has to do with the + 1 on the 2nd copies. I've tried different variations of everything I could think of, and didn't get anything to work.
Here's the Code:
VBA Code:
'get first row
n = .Range("D:X").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If n = 3 Then n = 4 Else n = n + 2
If Model.Rows.Count > 1 Then
Model.Columns(1).SpecialCells(xlCellTypeConstants).Copy
.Cells(n + 1, "E").PasteSpecial xlPasteValues
.Cells(n + 1, "E").Value = .Cells(n + 1, "E").Value & " Model" 'this is where it adds model, but only for the first item
Model.Columns(2).SpecialCells(xlCellTypeConstants).Copy
.Cells(n + 1, "Q").PasteSpecial xlPasteValues
Else
Model.Columns(1).Copy
.Cells(n + 1, "E").PasteSpecial xlPasteValues
.Cells(n + 1, "E").Value = .Cells(n + 1, "E").Value & " Model" 'I thought this was where it copied the remaining cells, but I can't get it to add Model
Model.Columns(2).Copy
.Cells(n + 1, "Q").PasteSpecial xlPasteValues
End If
If Drawing.Rows.Count > 1 Then
Drawing.Columns(1).SpecialCells(xlCellTypeConstants).Copy
.Cells(n + Model.Rows.Count + 1, "F").PasteSpecial xlPasteValues
.Cells(n + Model.Rows.Count + 1, "F").Value = .Cells(n + Model.Rows.Count + 1, "F").Value & " Drawing" 'ditto for Drawing
Drawing.Columns(2).SpecialCells(xlCellTypeConstants).Copy
.Cells(n + Model.Rows.Count + 1, "R").PasteSpecial xlPasteValues
Else
Drawing.Columns(1).Copy
.Cells(n + Model.Rows.Count + 1, "F").PasteSpecial xlPasteValues
.Cells(n + Model.Rows.Count + 1, "F").Value = .Cells(n + Model.Rows.Count + 1, "F").Value & " Drawing" 'ditto for Drawing
Drawing.Columns(2).Copy
.Cells(n + Model.Rows.Count + 1, "R").PasteSpecial xlPasteValues
End If
'get last row after inserting data
m = .Range("D:X").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Any help would be greatly appreciated