Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

how copy reverse column when export data for new file

0

Hello

I have  this  macro  to  export  data  for  the  last  sheet  by  new  file , but  what  I  want  just  copy the  values  from column F  (BALANCE) for  file  STOCK1  to column C (STOCK)  with  clears  others column  for  new  file . I put  the  result  in  new  file REPORT how  should  be .

the  macro  is  existed in  file  STOCK 1 and will also be  in new  file   as in XLSM after  run  the  macro   because I  need  it  the  codes for  each  new  file .

Sub gg()
On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
TryAgain:
    Flname = InputBox("Enter File Name :", "Creating New File...")
    MsgBox Len(Flname)
    If Flname <> "" Then
        Set NewWkbk = Workbooks.Add
        ThisWorkbook.Sheets(3).Copy NewWkbk.Sheets(1)
        NewWkbk.SaveAs ThisWorkbook.Path & "\" & Flname, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        If Err.Number = 1004 Then
            NewWkbk.Close
            MsgBox "File Name Not Valid" & vbCrLf & vbCrLf & "Try Again."
            GoTo TryAgain
        End If
        ActiveWorkbook.Close
    End If

best regards,

Tubrak

Answer
Discuss

Answers

0
Selected Answer

Tubrak

If you need the macros in the new file, I suggest you use the .SaveCopyAs method to create a new workbook with all sheets and the macros.

In the attached file, I've renamed the macro (and assigned to your button) and chnaged your macro to use that method, check that the original file has 3 sheets and edit the file to become 1 sheet with the BALANCE data in column C with columns D:F empty.

I've put key changes in bold below and added comments so you can see what is happening:

Sub ExportSheet3WithMacros()
On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
TryAgain:
    Flname = InputBox("Enter File Name :", "Creating New File...")

    If Flname <> "" Then
        ' reuse FlName for full path of new file
        Flname = ThisWorkbook.Path & "\" & Flname & ".xlsm"
        ' if 3 sheets, copy whole workbook with macros
        If ThisWorkbook.Sheets.Count = 3 Then
            ' if 3 sheets, copy whole workbook with macros
            ThisWorkbook.SaveCopyAs Flname
            Else
            ' tell the user (since rest of macro assumes 3 sheets)
            MsgBox "Workbook should have 3 sheets. Please correct and export again."
            Exit Sub
        End If
        If Err.Number = 1004 Then
            NewWkbk.Close
            MsgBox "File Name Not Valid (or used) " & vbCrLf & vbCrLf & "Try Again."
            GoTo TryAgain
        End If
    End If

    Set NewWkbk = Workbooks.Open(Flname)
    'delete first two sheets
    Sheets(1).Delete
    Sheets(1).Delete
    ' with the 3rd sheet of original
    With Sheets(1)
        ' get last row in F
        LstRw = .Range("F" & Rows.Count).End(xlUp).Row
        ' move data from F to C
        .Range("C2:C" & LstRw).Value = .Range("F2:F" & LstRw).Value
        'clear other columns
        .Range("D2:F" & LstRw).Value = ""
    End With
    ' save the file
    NewWkbk.Close SaveChanges:=True

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    ' tell user
    MsgBox "New stock file saved as " & Flname
End Sub

It also confirms that the new file was saved. 

Hope this fixes things for you.

Discuss

Discussion

Hi John,
your addition is  valuable !
thanks  so much  for  provide  me   the  solution 
tubrak (rep: 16) Sep 7, '22 at 1:47 pm
Great; thanks for selecting my answer, Tubrak.
John_Ru (rep: 3992) Sep 7, '22 at 2:02 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login