Need help with VBA coding

[TannerLittle44]

I cant figure out whats wrong with my coding. I get an error message every time the code reaches the Bold Red txt shown below, If any of you can see something that i am not catching please let me know. The error message states "1004" Run Time Error, Application-defined Object-defined Error.

Thanks,




Sub Update_DC_Inventory()




Application.ScreenUpdating = False 'Code eliminates screen flashing. Macro will run in backgroud
Dim wbTarget As Workbook 'workbook where the data is to be pasted
Dim wbThis As Workbook 'workbook from where the data is to copied
Dim strName As String 'name of the source sheet/ target workbook
'On Error GoTo ErrorHandler
'set to the current active workbook (the source book)
Set wbThis = ActiveWorkbook


'open a workbook that has same name as the sheet name
Set wbTarget = Workbooks.Open("G:\1Marketing\_Marketing Event Orders\Inventory 2015.xlsm")

'select cell A1 on the target book
wbTarget.Sheets("Promo").Activate
'The code below is for after paste file has been opened.
Range("H1").End(xlToRight).Offset(0, 1).Select
'activate the source book
wbThis.Activate

'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False
wbThis.Sheets("Data").Select
'copy the range from source book
wbThis.Sheets("Data").Range("T1:T86").Select
Selection.Copy


wbTarget.Activate

Selection.PasteSpecial xlPasteValuesAndNumberFormats
Selection.PasteSpecial xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wbThis.Activate

wbThis.Sheets("Event Order Form").Select

Call Submit_Order_Form

MsgBox "Event Order Form has been submitted to DC Marketing"

wbTarget.Activate

'wbTarget.Sheets("Reorder Email Template").Select

Call Find_First



End Sub

 

[Norie]

What data is on row 1?

What are you trying to do here anyway?

Range("H1").End(xlToRight).Offset(0, 1).Select

I think you are trying to find the last column in row 1 but you don't seem to do anything other than that.

 

[AlphaFrog]

Try this...

Cells(1,Columns.Count).End(xlToLeft).Offset(0, 1).Select

 

[Kamolga]

It looks like Range("H1").End(xlToRight) puts you on last cell of line 1 and you try then to do an offset so error. Can it be that sometimes you have nothing at the right of H1? or you have data in last cell of row 1?

 

[TannerLittle44]

.

 

[TannerLittle44]

Try this...

Cells(1,Columns.Count).End(xlToLeft).Offset(0, 1).Select

Worked Perfect. You sir! are a gentleman and a scholar! Thanks!