Copy from csv range A1 to last row/last column to another xls sheet

How do I have to amend this Ron de Bruin code in order to copy from the csv "daily_figures.csv" (sheet "DailyFigures") the range from A1 to the last row/column to the xls sheet "daily_figures.xls" (sheet "DailyFigures") (and paste as values).
Sub CopyFromRowValues()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim shLast As Long
    Dim Last As Long
    DestSh.Name = "DailyFigures"
                Last = LastRow(DestSh)
                shLast = LastRow(sh)
                With sh.Range(sh.Rows(3), sh.Rows(shLast))
                    DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
                    .Columns.Count).Value = .Value
                End With
    Application.ScreenUpdating = True
End Sub

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
    On Error Resume Next
    Lastcol = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

Open in new window

stmoritzAsked:
Who is Participating?
 
TommySzalapskiConnect With a Mentor Commented:
See if this does what you want. (I fixed up your original code too to make it follow better coding practices).

Sub UpdateFxFromCsv()
'
'
    Dim srcBook As Workbook
    Dim dstBook As Workbook

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set srcBook = Workbooks.Open Filename:= _
        "c:\data\daily_figures.csv"
    Set dstBook = Workbooks("daily_figures.xls")
'To specify the sheet to copy to use this
    srcBook.Sheets("daily_figures").UsedRange.Copy dstBook.Sheets("daily figures").Range("A1")
'To copy to the active sheet in daily_figures.xls use this line instead
    srcBook.Sheets("daily_figures").UsedRange.Copy dstBook.ActiveSheet.Range("A1")
    dstBook.Save
    Application.Quit

End Sub

Open in new window


Do you need to close the other books? The csv file (in srcBook) will prompt to save. You can do
srcBook.Close(false)
and it will close without prompting (false here means "don't save")
0
 
Robberbaron (robr)Commented:
in your Q you state that the CSV "daily_figures.csv" (sheet "DailyFigures") , yet the destination is sheet DailyFigures !  Sorry but this doesnt make sense to me.

worksheets are part of workbook. the code posted seems to assume that the DailyFigures worksheet is part of the active workbook.

1/ is the cvs opened manually  and is therefore a Wookbook & Worksheet of same name ?
2/ or do you want the code to open the csv, then do the copy to DailyFigures worksheet , assumed to be a member of the activeworkbook?

3/ please post a small sample of both the csv and the destination workbook that contains DailyFigures. It helps us work out want you want the macro to do.

RobG




0
 
stmoritzAuthor Commented:
Hi Rob.

Below the currently working code which copies the entire cells of the csv to the xls. I wanted to replace it with a code that copies only the used cells and not all cells, so I thought the above code could be a solution.

Yes, you are right about the DailyFigures daily_figures confusion. I only realised after posting that the sheet name of a csv cannot be amended to a different name than the first part of the file name.

Maybe "Cells.Copy" in the below and currently working code could be replaced using parts of the above routine to define the last row/column?

Thanks in any case for any help.
Sub UpdateFxFromCsv()
'
'
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Workbooks.Open Filename:= _
        "c:\data\daily_figures.csv"
    Sheets("daily_figures").Select
    Cells.Copy
    Windows("daily_figures.xls").Activate
    Range("A1").Select
    ActiveSheet.Paste
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    ActiveWorkbook.Save
    Application.Quit

End Sub

Open in new window

0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Robberbaron (robr)Commented:
tommySz's code looks like it would do the job.

only maybe is that the UsedRange property of a worksheet does not always return the expected range. Hence the development of the LastRow & LastCol functions by Ron de Bruin.

but for a freshly opened CSV, I would expect that UsedRange property would return correct values.


in case it doesn't work, then the following code modifies tommySz with DeBruin functions...
0
 
stmoritzAuthor Commented:
@TommySzalapski:

When I paste your code into a new module, line 9 and 10 are coloured red and the error message

Compile error: Expected: end of statement

appears...

any fix?
0
 
TommySzalapskiConnect With a Mentor Commented:
Sorry, should be like this
Set srcBook = Workbooks.Open(Filename:= "c:\data\daily_figures.csv")
0
 
stmoritzAuthor Commented:
thanks guys!
0
All Courses

From novice to tech pro — start learning today.