Solved

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

Posted on 2010-11-25
7
375 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:stmoritz
  • 3
  • 2
  • 2
7 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
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
 

Author Comment

by:stmoritz
Comment Utility
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
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 125 total points
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
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
 

Author Comment

by:stmoritz
Comment Utility
@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
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 125 total points
Comment Utility
Sorry, should be like this
Set srcBook = Workbooks.Open(Filename:= "c:\data\daily_figures.csv")
0
 

Author Closing Comment

by:stmoritz
Comment Utility
thanks guys!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now