RunTime Error 438

Hi Guys,

I need your help, on VBA code below. My aim is to be able to copy a range from one workbook to another and I get the Run Time Error 438 on the line colored red.

Can this code be corrected or is there an easier way to get this done?

Thanks.

Sub CopytoAnotherWorkbook()

   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
    
   Set wbThis = ActiveWorkbook
   strName = ActiveSheet.name
   Set wbTarget = Workbooks.Open("C:\Users\asdf\Desktop\" & strName & ".xlsx")
   wbTarget.Activate
   wbTarget.Range("A1").Select
   wbTarget.Range("A1:C10").ClearContents
   wbThis.Activate
 Application.CutCopyMode = False
  wbThis.Range("A1:C10").Copy
  wbTarget.Range("A1").PasteSpecial
 Application.CutCopyMode = False
 wbTarget.Save
wbTarget.Close
wbThis.Activate
Set wbTarget = Nothing
   Set wbThis = Nothing
    
End Sub

wbTarget.Range("A1").Select

Should have a worksheet name.
wbTarget is a workbook, so need to add the sheet within in the workbook
Like

wbTarget.sheets("Sheet1").Range("A1").Select

Thanks Norie for taking time out, it worked perfectly.

Regards.

Hi Norie,

I modified my code further in such a way that if no file is selected, it displays a message and then exits. But I have another Runtime Error 13 "Type Mismatch on "If strFileName = False Then"

My full code is below.


Thanks.

Sub ExportToOtherLocations()
   Application.ScreenUpdating = False

   
   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
   Dim SD                  As String
   Dim strFileName         As String
   
   Set wbThis = ActiveWorkbook
   strName = ActiveSheet.name
    Set wbThis = ActiveWorkbook
    strName = ActiveSheet.name

    ChDir "C:\Users\" ' change as needed

    strFileName = Application.GetOpenFilename("Excel Files (*.xls*),*.xls*", , "Please Select the File for the Location You want to Export Data to", , False)

If strFileName = False Then
MsgBox "No file specified. You must choose a Location File to Export To", vbExclamation, "Duh!!!"
Exit Sub
Else

Set wbTarget = Workbooks.Open(strFileName)

   wbTarget.Activate
   wbTarget.Sheets("Test").Select
   Range("A1").Select
   SD = InputBox("Pls enter", "Duh")
   If SD = "" Then
   MsgBox "This Value Cannot be NULL", Title, "Duh"
   wbTarget.Close
   Exit Sub
   
   End If
   If SD = "Fresh" Then
   wbTarget.Sheets("Test").Range("A2:F100000").ClearContents
   wbThis.Activate
   Application.CutCopyMode = False
   
    wbThis.Sheets("Stock").Range("A7:F7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
   wbTarget.Sheets("Test").Range("A2").PasteSpecial Paste:=xlPasteValues
   Application.CutCopyMode = False
   Else
   wbThis.Activate
   Application.CutCopyMode = False
   
    wbThis.Sheets("Stock").Range("A7:F7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
   wbTarget.Activate
   wbTarget.Sheets("Test").Select
   Range("A" & Rows.Count).End(xlUp).Offset(1).Select
   Selection.PasteSpecial Paste:=xlPasteValues
   Application.CutCopyMode = False
   wbTarget.Sheets("Test").Range("A1").Select
   End If
   wbTarget.Save
   wbTarget.Close
   wbThis.Activate
   wbThis.Sheets("Stock").Range("A7").Select
   
   Set wbTarget = Nothing
   Set wbThis = Nothing
   End If
End Sub

Thanks Guys.

Regards.