Exporting Data to a CSV File and Retrieving a File Name from the Source Workbook

advertisements

I currently have this code where I am trying to export data into a CSV file , and I want to display the workbook name and worksheet from the source workbook where I exported from on the CSV file itself. So far I have this following code:

Sub CopyPasteBetween2Books()

  Dim wb As Workbook
  ThisWorkbook.Sheets(1).Range("B7:E26,B39:E138").Copy
  Selection.Copy
  ActiveSheet.Paste
  wbname2 = ActiveWorkbook.Name
  wsname2 = ActiveSheet.Name
  Workbooks.Add
  ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A1")
  wbname = "Y:\Data.csv"
  ActiveWorkbook.SaveAs wbname
  Set wb = Workbooks.Open("Y:\SQCData.csv")
  ActiveCell.Offset(0, 8).Value = wbname2
  ActiveCell.Offset(0, 9).Value = wsname2

  Application.CutCopyMode = False
End Sub

So far this code only gets the Workbook name and worksheet name from where I exported the data from. However, the data that was specified from the range is not copied into the CSV file. Any ideas on how to get the data to be exported as well as retrieve the name of the workbook and the name of the worksheet from the source workbbok?


You can specify the range where you want to put your filename and worksheet name

 Sub CopyPasteBetween2Books()

  Dim wb As Workbook
  ThisWorkbook.Sheets(1).Range("B7:E26,B39:E138").Copy
  Selection.Copy
  ActiveSheet.Paste
  wbname2 = ActiveWorkbook.Name
  wsname2 = ActiveSheet.Name
  Workbooks.Add
  ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A1")
  Worksheets("Sheet1").Range("A8").value = wbname2
  Worksheets("Sheet1").Range("A9").value = wsname2
  wbname = "Y:\Data.csv"
  ActiveWorkbook.SaveAs wbname
  Set wb = Workbooks.Open("Y:\SQCData.csv")

  Application.CutCopyMode = False
End Sub