asked on

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

Scott McDaniel (EE MVE )

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.

Steve

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.

gmapdc

ASKER

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.

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial