• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

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
0
gmapdc
Asked:
gmapdc
  • 2
1 Solution
 
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

Featured Post

Managing Security & Risk at the Speed of Business

Gartner Research VP, Neil McDonald & AlgoSec CTO, Prof. Avishai Wool, discuss the business-driven approach to automated security policy management, its benefits and how to align security policy management with business processes to address today's security challenges.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now