Add Filename VBA

vbachanger

New Member
Joined
Sep 15, 2021
Messages
12
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
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:

vbachanger

New Member
Joined
Sep 15, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,748
Messages
5,742,978
Members
423,769
Latest member
LongToast

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
Top