Copy and paste

[Veeru106]

Hi,


I am trying to write a code. Where I am trying to copy certain file from my documents folder and paste in one sheet of my workbook.


It is working fine but I want to paste it one specific sheet “Raw Sheet” from number of sheets I have in my workbook.


It is giving my error “Script out of Range”


Below is the code:-


Sub Copy()

Dim vFile As Variant

Dim wbCopyTo As Workbook

Dim wsCopyTo As Worksheet

Dim wbCopyFrom As Workbook

Dim wsCopyFrom As Worksheet


Set wbCopyTo = ActiveWorkbook

Set wsCopyTo = ActiveSheet


'-------------------------------------------------------------

'Open file with data to be copied



vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _

"*.xl*", 1, "Select Excel File", "Open", False)



'If Cancel then Exit

If TypeName(vFile) = "Boolean" Then

Exit Sub

Else

Set wbCopyFrom = Workbooks.Open(vFile)

Set wsCopyFrom = wbCopyFrom.Worksheets(1)

End If



'--------------------------------------------------------------

'Copy Range



wsCopyFrom.Range("A2:N5000").Copy

Sheets("Raw Sheet").Activate

wsCopyTo.Range("A1").PasteSpecial Paste:=xlPasteValues, _

Operation:=xlNone, SkipBlanks:=False, Transpose:=False



'Close file that was opened

wbCopyFrom.Close SaveChanges:=False


End Sub


Please suggest any idea on this.


Thanks

 

[SirJB7]

Hi, Veeru106!
When posting code please do it between the proper tags "[ Code ]" and "[ /Code ]" (unquoted and without spaces), also found in the response toolbar, 5th icon from the right. Thank you.
Regards!

 

[Veeru106]

Sure I will take care of the same....above issue is little bit urgent, if any body can revert...will really appreciate it...Thanks

 

[Monty]

Script out of range error...When sheet name different or not existing.
Just CHK the sheet name.

 

[Monty]

Sheets("Raw Sheet").Activate

I guess sheet name changed or not existing...

 

[Veeru106]

I guess I am using the correct sheet name.

I am attaching the actual sheet, where I am working on

Raw Sheet is my Master sheet, I have command button in 2nd tab. Termination employee sheet is the workbook from where I want to copy and paste data in "Auto Sheet"tab.. if we check code , we can see I have mentioned Auto sheet to be activated but some how it is not?

Please have a look

 

[Nebu]

Hi:

May be the following code:

Sub test()
Dim rsCon As Object
Dim rsData As Object

i& = Me.Cells(Rows.Count, 1).End(xlUp).Row + 1

SourceFile$ = ThisWorkbook.Path & "\Terminate Emplyee.xlsx"

szConnect$ = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=No"";"

szSQL$ = "SELECT * FROM [Page1_1$]"

Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")

rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1
Me.Range("A" & i).CopyFromRecordset rsData
Me.Rows(i).Delete
End Sub

Note:

You do not have to open the workbook from where you have to copy data from. also save the all workbooks in the same folder.

Thanks

 

[Veeru106]

It showing as Compile error- Invalid use of Me Keyword

 

[Nebu]

Hi
Change me in the code to sheet name where u want to paste if u are pasting the code in a module .you are getting the error because u r pasteing the code in a module.i have written the code in the vba editor of the sheet in which you want to paste

 

[Veeru106]

I have change it to Autosheet , where I want to paste it but still showing error..
it says Object required in
i& = Autosheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

 

[SirJB7]

Hi, Veeru106!
ActiveSheet instead of Autosheet.
Regards!

 

[Veeru106]

ok but I don't want to use Active sheet...what if I have one specific sheet..and my command button to run Macro is at some other sheet?

 

[SirJB7]

I have change it to Autosheet , where I want to paste it but still showing error..

Hi, Veeru106!
Hadn't opened your workbook and thought that you said Autosheet instead of ActiveSheet. Now that I did open it, the worksheet name is not "Autosheet" but "Auto Sheet", isn't it?
The .Cells property applies to a range object. Being the case of a worksheet, you can refer to the worksheet range object as:
ActiveSheet
Worksheets("Auto Sheet")
Sheets("Auto Sheet")
Regards!

 

[Veeru106]

ok yes now it is working ......thanks a ton..

 

[SirJB7]

Hi, Veeru106!
Glad you solved it.
Regards!