Macro to insert data onto Sheet

ExcelNooberino

New Member
Joined
Jan 2, 2019
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
Hi guys!
Lately I've been working on some macros and I just created one where I insert the info into some fields and then transpose them onto a different sheet (lets say "History" Sheet). The problem now is I need to do some statistics with that same data but I need to add some formulas on extra columns I created in a different sheet (lets say "Data" Sheet) than the one that receives the data I insert. However, I don't want to go to all that trouble of copying and pasting info between sheets so I need it to transpose to the two sheets at the same time when I press the "Add" macro button. So far I got this:

Public Sub AddInfo()

Dim y As IntegerDim row As Integer


row = Historico.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row

y = Application.WorksheetFunction.Max(Range("B:B"))

History.Cells(row, 2).Value = y + 1 'ID
History.Cells(row, 3).Value = Range("camp_1").Value
History.Cells(row, 4).Value = Range("camp_2").Value
History.Cells(row, 5).Value = Range("camp_3").Value
History.Cells(row, 6).Value = Range("camp_4").Value
History.Cells(row, 7).Value = Range("camp_5").Value
History.Cells(row, 8).Value = Range("camp_6").Value
History.Cells(row, 9).Value = Range("camp_7").Value
History.Cells(row, 10).Value = Range("camp_8").Value
History.Cells(row, 11).Value = Range("camp_9").Value
History.Cells(row, 12).Value = Range("camp_10").Value
History.Cells(row, 13).Value = Range("camp_11").Value
History.Cells(row, 14).Value = Range("camp_12").Value

MsgBox "Info Successfully added!", vbInformation, "Add Info"

Call CleanFields

End Sub

I've tried to double that code and change the sheet and the row but I keep getting an error..
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I'll admit, I'm a little confused as to exactly what you need. What error are you getting and on what line do you get it?

Also, looking at the code it could be reduced by using a loop as your numerics are all sequential: I've done an example that reduces your code you have produced. However I can't fix the error as I don't know what it is.

Code:
Public Sub AddInfo()

Dim y As Integer, x As Integer
Dim row As Integer

row = Historico.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).row

y = Application.WorksheetFunction.Max(Range("B:B"))

History.Cells(row, 2).Value = y + 1 'ID

For x = 3 To 14
    History.Cells(row, x).Value = Range("camp_" & x - 2).Value
Next x

MsgBox "Info Successfully added!", vbInformation, "Add Info"

Call CleanFields

End Sub
 
Last edited:
Upvote 0
That's just amazing, I was able to cut some lines of code with that and for my question I was able to solve it... I just doubled that code for that to work on the two sheets at the same time once I pressed the "Add Info" button. However I'm getting stuck in a new problem now :(
I had to add some new columns to the table that receives the data that I insert on the fields of the "form" and I need to add a If formula to two of those new columns and I know I'm doing something wrong but I don't know actually what (Runtime error 1004). This is the line that get's me down:

History.Cells(row, 17).Formula = "=IF(H" & row & ";'MyPlant';'Outbound';'Inbound')"
 
Upvote 0
The issue on this line is the double quotes... and a little confusion. Always a headache.

Best way is to get it working manually, then copy that working formula. If you copied your formula in to a cell you would see it wouldn't work

Next problem is to create a string variable that contains that exact string. Sounds simple but double quotes can be troublesome


Looking at your formula there is plenty wrong with it.

What, in English, is it trying to achieve?
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top