Am having trouble getting data copied from workbook(sheet) to a history file

This is the code - when I run it with or without both the files open, it always goes to "debug" when it tries to find the data file.
The data file may have 1 to 50 records and each data file is to be copied to a "summary  called HistoryFileData"

Sub Macro50()
'
' Macro50 Macro
'
Dim wbkMaster As Workbook
Dim shtMaster As Worksheet
Dim rngMaster As Range
Dim wbkData As Workbook
Dim shtData As Worksheet
Dim rngData As Range
Dim rstFilename As Workbook

   
     ' change path and file name to suit
    Set wbkMaster = Workbooks.Open("C:\Users\pcrockett\Documents\BSM\Development\Jared\ScaleWts\HistoryFileData.xlsm")
    Set shtMaster = wbkMaster.Worksheets(1)
   
    'Set rstFilename = "Capturexx.xlsm"

     
     ' change path and file name to suit
    Set wbkData = Workbooks.Open("C:\Users\prockett\Documents\BSM\Development\Jared\ScaleWts\RAWWeightFiles\Capturexx.xlsx")
   
   
    Set shtData = wbkData.Worksheets(1)
     
     ' get end of master
    Set rngMaster = shtMaster.Range("A65536").End(xlUp)
     ' get all data cells
    Set rngData = shtData.UsedRange
     ' copy data across
    rngData.Copy rngMaster
     
    MsgBox "Appended " & rngData.Rows.Count & " rows of data to Master data", vbInformation
     
     ' simply close data
    wbkData.Close False
     ' save and close master
    wbkMaster.Close True
     
     '  release objects
    Set rngData = Nothing
    Set shtData = Nothing
    Set wbkData = Nothing
    Set rngMaster = Nothing
    Set shtMaster = Nothing
    Set wbkMaster = Nothing

   
   
End Sub
gmapdcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Do you get an error? If so, post the details of the error here.

Obviously, you must be sure the paths to those files are correct.
0
SteveCommented:
It seems that there is a discrepancy in the file names:

In particular using pcrockett in one and prockett in the other.

Sub Macro50()
 '
 ' Macro50 Macro
 '
 Dim wbkMaster As Workbook
 Dim shtMaster As Worksheet
 Dim rngMaster As Range
 Dim wbkData As Workbook
 Dim shtData As Worksheet
 Dim rngData As Range
 Dim rstFilename As Workbook

    
      ' change path and file name to suit
    Set wbkMaster = Workbooks.Open("C:\Users\pcrockett\Documents\BSM\Development\Jared\ScaleWts\HistoryFileData.xlsm")
    Set shtMaster = wbkMaster.Worksheets(1)
    
      ' change path and file name to suit
    Set wbkData = Workbooks.Open("C:\Users\pcrockett\Documents\BSM\Development\Jared\ScaleWts\RAWWeightFiles\Capturexx.xlsx")
    Set shtData = wbkData.Worksheets(1)
      
      ' get end of master
    Set rngMaster = shtMaster.Range("A65536").End(xlUp)
      ' get all data cells
    Set rngData = shtData.UsedRange
      ' copy data across
    rngData.Copy rngMaster
      
    MsgBox "Appended " & rngData.Rows.Count & " rows of data to Master data", vbInformation
      
      ' simply close data
    wbkData.Close False
      ' save and close master
    wbkMaster.Close True
      
      '  release objects
    Set rngData = Nothing
    Set shtData = Nothing
    Set wbkData = Nothing
    Set rngMaster = Nothing
    Set shtMaster = Nothing
    Set wbkMaster = Nothing
     
End Sub

Open in new window


I would also look to be more specific about the ranges you are copying...
You may be over writing using shtMaster.Range("A65536").End(xlUp) and may need a +1
Also UsedRange can be a tad volatile so could be improved.
0
gmapdcAuthor Commented:
Scott,

The error says that it cannot find the path/file.


Steve,

Please take a look at the example again - I see both names spelled the same?????

What can I do besides .UsedRange to get to the last cell.  I tried SelectionCells(xlCellTypeLastCell).Select but could not get that to work.  

Your help is needed and appreciated.
0
SteveCommented:
I must ask you to read the example you have posted...

The two file names do not have the same spelling for pcrockett... the second one is missing a c (prockett)
So I am guessing that one or the other is in error.

To get the last cell I would use the similar to shtMaster.Range("A65536").End(xlUp)

Something like the following to get A to G rows 2 to last row:
Set rngData = shtData.range("A2:G" & shtData.Range("A65536").End(xlUp).row)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.