Hi,everyone, can any help me? what's wrong with this VBA code?

[yeongkwe]

Hi, I would like to create a VBA code as I counter problem when the code to jump into another workbooks to select the range and I encounter this problem, this box come out stated "run-time error 1004 select method of range class failed" Can any nice smart people here kindly help me and tell me what's had I done wrong. Thanks. The code is below.


Sub Testing()



Workbooks("Date check").Worksheets("sheet3").Range("A1:F5").Copy Workbooks("New improved 4D JAN 20 2017 3 Mar with new add in method Gtotal and Y big count with micro").Worksheets("sheet1").Range("A97997")

Workbooks("New improved 4D JAN 20 2017 3 Mar with new add in method Gtotal and Y big count with micro").Worksheets("sheet1").Range("F97996:H97996").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F97996:H98001"), Type:=xlFillDefault
Range("F97996:H97997").Select
End Sub

 

[D3allamerican07]

yeongkwe,

Not sure why you are pasting values at the range you are, or selecting the range you are. But the below code copies and pastes to the defined workbooks. Make sure the extensions match also.

copyWB = Workbooks("Date check.xlsm").Name
pasteWB = Workbooks("New improved 4D JAN 20 2017 3 Mar with new add in method Gtotal and Y big count with micro.xlsm").Name

Workbooks(copyWB).Worksheets("Sheet3").Range("A1:F5").Copy Destination:=Workbooks(pasteWB).Worksheets("Sheet1").Range("A97997")

Workbooks(pasteWB).Worksheets("Sheet1").Range("F97996:H97996").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F97996:H98001"), Type:=xlFillDefault

Let me know if this is what you were looking for.

Bill

 

[yeongkwe]

Hi, sorry to disturb you again, I can't even go thru the first code this rime, copyWB = Workbooks("Date check.xlsm").Name as error box come out "run-time error 9 subscript out of range.




Not sure why you are pasting values at the range you are, or selecting the range you are. But the below code copies and pastes to the defined workbooks. Make sure the extensions match also.

copyWB = Workbooks("Date check.xlsm").Name
pasteWB = Workbooks("New improved 4D JAN 20 2017 3 Mar with new add in method Gtotal and Y big count with micro.xlsm").Name

Workbooks(copyWB).Worksheets("Sheet3").Range("A1:F5").Copy Destination:=Workbooks(pasteWB).Worksheets("Sheet1").Range("A97997")

Workbooks(pasteWB).Worksheets("Sheet1").Range("F97996:H97996").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F97996:H98001"), Type:=xlFillDefault

Let me know if this is what you were looking for.

Bill[/QUOTE]

 

[D3allamerican07]

Make sure the extension of the workbooks match (e.g. xlsm, xlsx, etc.)

copyWB = Workbooks("Date check.[B]xlsm[/B]").Name

 

[yeongkwe]

Hi, I had I remove the .xlsm and it works again, But the same problem still happen when come to this line of code Workbooks(pasteWB).Worksheets("Sheet1").Range("F97996:H97996").Select


Sub testing()


copyWB = Workbooks("Date check").Name
pasteWB = Workbooks("New improved 4D JAN 20 2017 3 Mar with new add in method Gtotal and Y big count with micro").Name

Workbooks(copyWB).Worksheets("Sheet3").Range("A1:F5").Copy Destination:=Workbooks(pasteWB).Worksheets("Sheet1").Range("A97997")

Workbooks(pasteWB).Worksheets("Sheet1").Range("F97996:H97996").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F97996:H98001"), Type:=xlFillDefault


End Sub

 

[D3allamerican07]

Add this line of code right before it.

Workbooks(pasteWB).Activate

 

[yeongkwe]

Hi, thanks alot. this works. Big thanks you are very good.

Add this line of code right before it.

Workbooks(pasteWB).Activate

 

[D3allamerican07]

No problem, glad I was able to help.