asked on

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

Robberbaron (robr)

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

stmoritz

ASKER

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

ASKER CERTIFIED SOLUTION

TommySzalapski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robberbaron (robr)

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...

stmoritz

ASKER

@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?

SOLUTION

TommySzalapski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stmoritz

ASKER

thanks guys!