Solved

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

Posted on 2010-11-25
7
409 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)
ID: 34217309
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
ID: 34218693
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
ID: 34219202
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 34220904
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
ID: 34233197
@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
ID: 34233268
Sorry, should be like this
Set srcBook = Workbooks.Open(Filename:= "c:\data\daily_figures.csv")
0
 

Author Closing Comment

by:stmoritz
ID: 34233485
thanks guys!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA: Conversion $ vs € and vise versa 14 41
Need to combine three formulas into one 5 32
Merging text files strings with filename 18 39
Converting time 4 35
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 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