Add Filename VBA

vbachanger

New Member
Joined
Sep 15, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi Guys, in the code below, I want to add /FileA before the file extension. [Filename/FileA.csv]

VBA Code:
Sub rename()

    Dim strFile As String

    strFile = ThisWorkbook.FullName
    strFile = Left(strFile, InStrRev(strFile, ".")) & "csv"

        Range("A1:C6").Copy
        Workbooks.Add xlWBATWorksheet
        ActiveSheet.Range("A1").PasteSpecial xlPasteValues
        On Error Resume Next
        ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlCSV
End Sub

In the code below, I want to copy only values from wsCopy to wsDest. The following part is not working currently: [PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False]

VBA Code:
Sub Copy_Paste_Below_Last_Cell()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

 Workbooks.Open "C:\Users\currentuser\Data.xlsx"

  Set wsCopy = Workbooks("Data.xlsx").Worksheets("Export")
  Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
    
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
    
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

  wsCopy.Range("A2:D" & lCopyLastRow).Copy _
  wsDest.Range("A1")[B].[/B]PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
  wsDest.Activate
  
  Workbooks("Data.xlsx").Close SaveChanges:=True
End Sub

ThankU!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
1. "\" is an illegal character for Windows filenames, perhaps use an underscore "_"?

VBA Code:
strFile = Left(strFile, InStrRev(strFile, ".")-1) & "_FileA.csv"

2. When PasteSpecial, do it in 2 lines

VBA Code:
wsCopy.Range("A2:D" & lCopyLastRow).Copy
wsDest.Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Edit: See Naming Files, Paths, and Namespaces - Win32 apps

Edit 2: Application.CutCopyMode = False after the paste won't hurt either
 
Last edited:
Upvote 0
1. "\" is an illegal character for Windows filenames, perhaps use an underscore "_"?

VBA Code:
strFile = Left(strFile, InStrRev(strFile, ".")-1) & "_FileA.csv"

2. When PasteSpecial, do it in 2 lines

VBA Code:
wsCopy.Range("A2:D" & lCopyLastRow).Copy
wsDest.Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Edit: See Naming Files, Paths, and Namespaces - Win32 apps

Edit 2: Application.CutCopyMode = False after the paste won't hurt either

VBA Code:
strFile = Left(strFile, InStrRev(strFile, ".")-1) & "_FileA.csv"
This one is working - Thank you!

VBA Code:
  wsCopy.Range("A2:D" & lCopyLastRow).Copy
  wsDest.Range("A2").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False

Here, it is not working - Pasting no values.
 
Upvote 0
Sorry, misread the first post. You do want values, I just used the code. This will paste only values.

VBA Code:
wsDest.Range("A2").PasteSpecial Paste:=xlPasteValues
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top