Inserted Columns and Automatic Entries

Originally Posted by TjBoiler

I have a macro set up that when the workbook opens an input box asks for the end of week date. After you enter it in, two columns are inserted beside to other columns. The to columns next to the new columns have range names, but How do I reference the new columns added? I want to automatically add formulas and formatting to the new inserted cells.

For Instance, L9:L24 and M9:M24 have Range Names "BOB" and "TOM". If I insert to new columns to the left of "BOB" and "TOM" how could I automatically put a value in the new K9 and J9 Cells, and I want to name the two new ranges? Is this possible?

Thanks,

TJB

Hi,

the simple way to determine this is to record a macro whilst naming a range, then, after the column inserts note that the ranges now occupy new area so new range-names can be defined with something like

Please Login or Register  to view this content.

of course, 'newbob' and 'newtom' will need to be variables, probably gained from user requested input.

hth
---

I am running into a problem with the code below. I think it has something to do with the ARCHIVEHOURSPF in line bold. I am getting a run time error 1004 "Application-Defined or object defined error" Can you help? Also, I think I still going to have a problem when I go to insert another set of columns after this one. Thanks for all the help.

'Add New Columns for Weekly Hour Entry and LF/Qty Entry
Range("ARCHIVEWEEKS").Select
Selection.Insert Shift:=xlToRight

ARCHIVEHOURSPF = InputBox(prompt:="When was your last Entry?")


'Name Ranges for New Archive Lines

Range("ARCHIVEWEEKSPF").Select
ActiveWorkbook.Names.Add Name:="ArchiveHoursPF", RefersToR1C1:= _
"=Pipe FeedBack!R8C9:R32C10"


'Paste Contents from Last Week Into Archives
Range("PREVHOURS", "PERLF").Select
Selection.Copy
Range("ARCHIVEHOURSPF").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
Selection.EntireColumn.Hidden = True

Originally Posted by TjBoiler

I am running into a problem with the code below. I think it has something to do with the ARCHIVEHOURSPF in line bold. I am getting a run time error 1004 "Application-Defined or object defined error" Can you help? Also, I think I still going to have a problem when I go to insert another set of columns after this one. Thanks for all the help.

'Add New Columns for Weekly Hour Entry and LF/Qty Entry
Range("ARCHIVEWEEKS").Select
Selection.Insert Shift:=xlToRight

ARCHIVEHOURSPF = InputBox(prompt:="When was your last Entry?")

'Name Ranges for New Archive Lines

Range("ARCHIVEWEEKSPF").Select
ActiveWorkbook.Names.Add Name:="ArchiveHoursPF", RefersToR1C1:= _
"=Pipe FeedBack!R8C9:R32C10"


'Paste Contents from Last Week Into Archives
Range("PREVHOURS", "PERLF").Select
Selection.Copy
Range("ARCHIVEHOURSPF").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
Selection.EntireColumn.Hidden = True

Hi,

you have a variable 'ARCHIVEHOURSPF' and a range name 'ArchiveHoursPF' and you are trying to select the variable?

Was this intentional?
---