Help to finish my vba code (export sheet and etc)

SimbadS

New Member
Joined
Feb 13, 2020
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hi again!

Im working on a code and i have come far but i want to perfect it so the code does the work from start to end.
So far what i do is unhide a sheet, copy it to a new woorkbook, then select all cells in the sheet (in new workbook) and eliminate formulas.
So far so good. It does the job.
here is the code
VBA Code:
Sub Copy_to_CSV()
'
' Copy_to_CSV Makro
'

'
    Sheets("Mainpage").Select
    Sheets("mysheet").Visible = True
    Sheets("mysheet").Select
    Sheets("mysheet").Copy
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.AutoFilter
    Selection.AutoFilter
    Rows("1:1").Select
    Selection.AutoFilter
    Range("B9").Select
    Windows("Mywoorkbook.xlsm").Activate
    Sheets("mysheet").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Mainpage").Select
End Sub

This is the whole job that i want done:
unhide sheet, copy it to a new workbook and ASK "where to save?"(this can come later or before, does not matter when)
Then, kill all formulas.
Next step is collum "AG:AG" to change from "," to "." .
Last step is in collum "A:A" to delete empty rows.
Last step here is because i have empty slots in example row A5, A10, A30, etc, IF "empty" delete entire row, if "X" stay.

Thank you!
 
Hi!

I try to use this code but im not sure where to put in what.
Im good at formulas but have almost no knowledge with vba..
Here is it and it fails
VBA Code:
Sub Copy_to_CSV2()
'
' Copy_to_CSV Makro
Dim savDest As String
    Sheets("mysheet").Visible = True
    Sheets("mysheet").Copy
    With ActiveSheet.UsedRange
        .Value = .Value
        .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Application.Goto Reference:=Worksheets("mysheet").Range("AG"), _
    Scroll:=True
    Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False


    Windows("Mywoorkbook.xlsm").Activate
    Sheets("mysheet").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Homepage").Select
    
End With
End Sub

It fails from "Application.Goto Reference:=Worksheets....
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Syntax problem: Change
Code:
Range("AG")
to
Code:
Columns("AG")
Or
Code:
Range("AG:AG")

But this is no different than my original 'Replace' statement.
 
Upvote 0
Hi again!

I have tried this option but still "AG:AG" stays intact with "," and has not been changes to "."
AG contains numbers like this 1255,45 and 54887,65 etc. and i need it to bee 1255.45 and 54887.65

VBA Code:
Sub Copy_to_CSV2()
'
' Copy_to_CSV Makro
Dim savDest As String
    Sheets("Sheet").Visible = True
    Sheets("Sheet").Copy
    With ActiveSheet.UsedRange
        .Value = .Value
        .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
    
Range("AG:AG").Select

Selection.Copy
Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


    Windows("Mybook.xlsm").Activate
    Sheets("Sheet").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Mainpage").Select
  
End Sub
 
Upvote 0
I eliminated my typos and tested this. See if it now does what you want.

Code:
Sub Copy_to_CSV()
'
' Copy_to_CSV Makro
Dim savDest As String
    Sheets("mysheet").Visible = True
    Sheets("mysheet").Copy
    With ActiveSheet.UsedRange
        .Value = .Value
        .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
    Columns("AG").Replace ",", "."
    savDest = InputBox("Enter the Directory Path to save to.", "PATH TO SAVE")
    'ActiveWorkbook.SaveAs(savDest & "<enter file name and extension here>" )
    Windows("Mywoorkbook.xlsm").Activate
    Sheets("mysheet").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Mainpage").Select
End Sub

You will have to put in a file name and extension where you see
<enter file name and extension here>
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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