.ActiveSheet.[B16].Resize(i + 1, IcolNulTemp2) = Ary Use varible for [B16] flexible

JoeMonte · Nov 28th 2016

I want to change the .activesheet.[b16] to a varaible where users can change if nessary on input sheet. I like to insert "sStartdatacell" variable but i get errors. what is the syntext since this has brackets

put this sStartDatacell in for [b16] ----- sStartDatacell = ActiveSheet.Range("c10").Value

Code

With ActiveWorkbook
                .ActiveSheet.Name = Split(Key, "/")(0)
                .ActiveSheet.Cells(iTerrRow, iTerrCol).Value = Key
                .ActiveSheet[COLOR=#ff0000].[B16][/COLOR].Resize(i + 1, IcolNulTemp2) = Ary  
                .Sheets(1).Select
                .SaveAs Filename:=ThisWorkbook.Path & "" & sOutputFileNm & "_" & sTerr & "_" & Split(Key, "/")(0) & ".xlsx" 
                .Close
            End With

**Kenneth Hobson** · Nov 29th 2016

Re: .ActiveSheet.[B16].Resize(i + 1, IcolNulTemp2) = Ary Use varible for [B16] flex

Code

With ActiveSheet
    .Name = Split(Key, "/")(0)
    .Cells(iTerrRow, iTerrCol).Value = Key
    sStartDatacell = .Range("C10").Value
    .Range(sStartDatacell).Resize(i + 1, IcolNulTemp2) = Ary
    ActiveWorkbook.SaveAs Filename:= _
      ThisWorkbook.Path & "\" & sOutputFileNm & "_" & _
      sTerr & "_" & .Name & ".xlsx"
    .Close
  End With
  Sheets(1).Select

Display More

I did not know if sOutputFileNm has a prefix of "" so I added it after ThisWorkbook.Path.

JoeMonte · Nov 29th 2016

Re: .ActiveSheet.[B16].Resize(i + 1, IcolNulTemp2) = Ary Use varible for [B16] flex

Thanks for the help

.ActiveSheet.[B16].Resize(i + 1, IcolNulTemp2) = Ary Use varible for [B16] flexible

Participate now!

Share