Solved

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

Posted on 2014-11-02
4
115 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 85
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

728 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