Solved

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

Posted on 2014-11-02
4
110 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

919 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now