none
Copy used range from one workbook to another, excluding top row RRS feed

  • Question

  • I am not really an Excel VBA person and am very new with copy and pasting ranges, and I've been assigned a difficult task.

    Our goal is to import prior data from one workbook (wbPrior) and prior worksheet (wsPrior) into the current workbook (wbCurrent) and current worksheet (wsCurrent) I do not have the benefit of seeing what the prior workbook looks like, so I have to make a lot of assumptions.

    There are many scenerios, for example we only want to copy data, not formulas, but this is not for now.

    Assuming I am working on a worksheet that has no formulas, and is a contiguous range of data...

    For the current worksheets we are clearing all the data, and leaving only a header row if it exists (not always row 1)

    Assuming the prior worksheet has the same structure as the current worksheet, I need to grab the used range from the prior worksheet, excluding the header row, and dump it under the header row on the current worksheet.

    I can do it on a worksheet to worksheet basis, so the same code doesn't have to catch every scenario.

    i.e. I will know the number of columns for each worksheet and the placement.

    A solution would be a big help! Thanks.

    Friday, March 16, 2018 4:20 PM

Answers

  • Hi James,

    This is my sample:

    [wbPrior - wsPrior]


    [wbCurrrent - wsCurrent] with button


    [code in button]

    Private Sub btn_GetData_Click()
        ' --- open "wbPrior.xlsx"
        Dim fileNameFullPath As String
        fileNameFullPath _
            = "R:\00 北窓舎\01 PCサポート\00 MSDN_TechNet\00 Excel\2018-03 James N San\" _
                & "wbPrior.xlsx"
        Workbooks.Open Filename:=fileNameFullPath, ReadOnly:=True
        ' ----- cf. after opening workbook, it becomes an activeworkbook
        Dim wbPrior As Workbook
        Set wbPrior = ActiveWorkbook
        ' --- get LastRow
        Dim PriorLastRow As Integer     ' -- wsPrior
        PriorLastRow = wbPrior.Sheets("wsPrior").Cells(Rows.Count, 1).End(xlUp).Row
        ' --- copy wsPrior to wsCurrent
        wbPrior.Sheets("wsPrior").Range("A4:F" & PriorLastRow) _
            .Copy Destination:=ThisWorkbook.Sheets("wsCurrent").Cells(4, 1)
        ' --- close wbPrior
        wbPrior.Close
    End Sub

    Regards,

    Ashidacchi >> http://hokusosha.com/

    • Marked as answer by James N San Sunday, March 18, 2018 6:36 PM
    Saturday, March 17, 2018 11:45 PM

All replies

  • Hi James,

    I'm afraid you need to specify
    (1) the number of header rows. Is it one row as you described "a header row"?
    (2) if a header row always exists. Is there a case that a header row does not exist?

    I hope you will insert a screenshot of "wsPrior".

    Regards,

    Ashidacchi >> http://hokusosha.com/

    Friday, March 16, 2018 11:04 PM
  • Ashidacchi,

    There might be cases where there is more than one row for the "header", but I will know what row the data will start in. I will also know the number of columns. What I will not know is how many rows the data takes up.

    Also I want to skip over columns that have formulas. I will know the columns that have formulas also.

    For example, I might have a spreadsheet whose first three rows are "header" information and it is for columns A through F. No formulas.

    In that case I want to copy everything from row 4 down from columns A through F from the prior worksheet and insert it into the current worksheet at row four column A through F.

    Another example might be there is a formula in column D.

    In that case I want to from the prior worksheet data from row four columns A through C, and row four columns E through F. I could do this in two separate calls. So it could be treated like example one above, just repeat the process twice.

    I see the current worksheet, but I do not see the prior worksheet, but I assume the structure of the two worksheets are the same. So I will know what row the data starts in, and what columns, but they can both vary. I can change the row number and column range accordingly. I just won't know the number of data rows.

    So I need to copy until I get to last row.

    Hope this helps.

    Thanks

    Saturday, March 17, 2018 7:19 AM
  • Hi James,

    Sorry, if I misunderstand your explanation.

    If you want to know the last row both of wsCurrent and wsPrior, this code will help you.
    Private Sub btn_GetData_Click()
        ' --- open "wbPrior.xlsx"
        Dim fileNameFullPath As String
        fileNameFullPath _
            = "R:\00 北窓舎\01 PCサポート\00 MSDN_TechNet\00 Excel\2018-03 James N San\" _
                & "wbPrior.xlsx"
        Workbooks.Open Filename:=fileNameFullPath, ReadOnly:=True
        ' ----- cf. after opening workbook, it becomes an activeworkbook
        Dim wbPrior As Workbook
        Set wbPrior = ActiveWorkbook
        ' --- get LastRow
        Dim myLastRow As Integer       ' -- wsCurrent
        Dim PriorLastRow As Integer     ' -- wsPrior
        myLastRow = ThisWorkbook.Sheets("wsCurrent").Cells(Rows.Count, 1).End(xlUp).Row
        PriorLastRow = wbPrior.Sheets("wsPrior").Cells(Rows.Count, 1).End(xlUp).Row
        MsgBox "myLastRow" & myLastRow & Chr(13) & "PriorLastRow=" & PriorLastRow
        ' --- do something
    	' --- ????
        ' --- close wbPrior
        Workbooks("wbPrior").Close
    End Sub
    Regards,

    Ashidacchi >> http://hokusosha.com/

    Saturday, March 17, 2018 8:47 AM
  • Ashidacchi,

    I think your missing what I am asking for.

    I am going to provide the user with the current workbook.

    the worksheet will be empty except for any headers or stuff above the headers (yellow and blue sections)

    the user is going to browse to their prior version and click on a button that will import their prior data into their current data. the button will be somewhere on the current worksheet.

    I can do the code to browse for a file and open the prior workbook.

    I need code that will copy the prior to the current and place it under the blue section.

    I don't know what the prior worksheet looks like so I have to get the last row for each of the columns

    I will attached a screen shot

    Thanks

    Saturday, March 17, 2018 8:14 PM
  • Hi James,

    This is my sample:

    [wbPrior - wsPrior]


    [wbCurrrent - wsCurrent] with button


    [code in button]

    Private Sub btn_GetData_Click()
        ' --- open "wbPrior.xlsx"
        Dim fileNameFullPath As String
        fileNameFullPath _
            = "R:\00 北窓舎\01 PCサポート\00 MSDN_TechNet\00 Excel\2018-03 James N San\" _
                & "wbPrior.xlsx"
        Workbooks.Open Filename:=fileNameFullPath, ReadOnly:=True
        ' ----- cf. after opening workbook, it becomes an activeworkbook
        Dim wbPrior As Workbook
        Set wbPrior = ActiveWorkbook
        ' --- get LastRow
        Dim PriorLastRow As Integer     ' -- wsPrior
        PriorLastRow = wbPrior.Sheets("wsPrior").Cells(Rows.Count, 1).End(xlUp).Row
        ' --- copy wsPrior to wsCurrent
        wbPrior.Sheets("wsPrior").Range("A4:F" & PriorLastRow) _
            .Copy Destination:=ThisWorkbook.Sheets("wsCurrent").Cells(4, 1)
        ' --- close wbPrior
        wbPrior.Close
    End Sub

    Regards,

    Ashidacchi >> http://hokusosha.com/

    • Marked as answer by James N San Sunday, March 18, 2018 6:36 PM
    Saturday, March 17, 2018 11:45 PM
  • Yes that works perfectly! Thanks!
    Sunday, March 18, 2018 6:36 PM