Solved

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

Posted on 2014-11-02
4
111 Views
Last Modified: 2014-11-19
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
Comment
Question by:gmapdc
  • 2
4 Comments
 
LVL 84
ID: 40419204
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
 
LVL 24

Expert Comment

by:Steve
ID: 40419317
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
 

Author Comment

by:gmapdc
ID: 40420151
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
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 40420201
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

786 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