0

I started learned about the existence of macros yesterday and I know nothing about VBA. This is about to become evident. I am trying to set up a workbook with one sheet as a chemical list and another sheet to load information into it. The list itself will be hidden away to from most people to keep it neat but if someone needs to add a chemical to it they will fill out a form and hit a button and it will load that information into the list. So I recorded the macro below. It is inelegant and has mistakes, I know, but it is just a kind of proof of concept at this stage. But when i run it, it always runs in the sheet I am working on rather than putting the information into the list. Any help?

Sub Insert_Chemical()
'
' Insert_Chemical Macro
'

'
Rows("3:3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A3").Select
ActiveCell.FormulaR1C1 = "='New Chmical'!RC[1]"
Range("B3").Select
ActiveCell.FormulaR1C1 = "='New Chmical'!RC[1]"
Range("C3").Select
ActiveCell.FormulaR1C1 = "='New Chmical'!RC[1]"
Range("C4").Select
ActiveCell.FormulaR1C1 = ""
Range("D3").Select
ActiveCell.Formula2R1C1 = "='New Chmical'!RC[1]:RC[2]"
Range("E3").Select
ActiveCell.FormulaR1C1 = "='New Chmical'!RC[2]"
Range("D3").Select
ActiveCell.FormulaR1C1 = "='New Chmical'!RC[1]"
Range("F3").Select
ActiveCell.FormulaR1C1 = "='New Chmical'!RC[2]"
Range("G3").Select
ActiveCell.FormulaR1C1 = "='New Chmical'!RC[2]"
Range("H3").Select
ActiveCell.FormulaR1C1 = "='New Chmical'!RC[2]"
Range("I3").Select
ActiveCell.FormulaR1C1 = "='New Chmical'!RC[2]"
Range("F4").Select
ActiveWorkbook.Worksheets("COSHH List").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("COSHH List").AutoFilter.Sort.SortFields.Add2 Key:= _
    Range("A1:A4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("COSHH List").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub
James
  • 1
  • 2
    Welcome to VBA. Basically, the issue is that any time you're using `Range`, you've not specified the parent worksheet for that range, and implicitly the parent is the `ActiveSheet`. If you want to work on a specific sheet, then use `Worksheets("COSHH List").Range(...)`. – BigBen Sep 22 '22 at 14:05
  • 1
    [This may also prove to be useful](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – cybernetic.nomad Sep 22 '22 at 14:16
  • That link given by cybernetic.nomad is excellent. You may also want to dim two worksheets dim wsCOSHH as Worksheet, wsNC as Worksheet Set wsCOSHH = worksheets("COSHH List") Set wsNC = Worksheets("New Chmical") – Jan Sep 22 '22 at 15:55

0 Answers0