×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

HELP! VBA hangs up copying a range from one workbook to another
2

HELP! VBA hangs up copying a range from one workbook to another

HELP! VBA hangs up copying a range from one workbook to another

(OP)
I have 2 Excel workbooks and I am using Excel 2013. Can someone take a look a see what I am doing wrong. Thanks!

Private Sub CommandButton1_Click()
Dim Datafile, File, Folder As String
Dim RetVal, FirstRow As Integer
Datafile = "Ram Elements Data.xls"
File = "Ram Elements Bentley Launch-NO.xls"
Folder = ThisWorkbook.Worksheets("Launch").Range("A14").Value
'Application.DisplayAlerts = False
Workbooks.Open Filename:=Folder & "\" & Datafile
'Dim DLoop As Integer
FirstRow = Workbooks(Datafile).Worksheets("Data").Range("D1").Value
Workbooks(Datafile).Worksheets("Data").Cells(FirstRow, 1).Value = Now() & " " & Environ("username")
'Display 4 rows of "Data" on "Launch" sheet, rows 5-8
Workbooks(Datafile).Worksheets("Data").Range(Cells(FirstRow - 3, 1), Cells(FirstRow, 2)).Copy _
Destination:=ThisWorkbook.Worksheets("Launch").Range("A5")
'For DLoop = 1 To 4
'Worksheets("Launch").Cells(DLoop + 4, 1).Value = Worksheets("Data").Cells(FirstRow - 4 + DLoop, 1).Value
'Worksheets("Launch").Cells(DLoop + 4, 2).Value = Worksheets("Data").Cells(FirstRow - 4 + DLoop, 2).Value
'Next DLoop
Workbooks(Datafile).Close SaveChanges:=True
ThisWorkbook.Save
Dim FileNumber, fName As String
Kill Folder & "\Ram Elements EXIT.txt"
fName = Folder & "\Ram Elements IN USE.txt"
FileNumber = FreeFile ' Get unused file number.
Open fName For Output As #FileNumber ' Open file
Write #FileNumber, Now() & " " & Environ("username")
Close #FileNumber ' Close file.
' Program is now being used and file IN USE is written
' Run Ram Elements program
'RetVal = Shell("C:\RamElem.cmd", 1)
' Run Dummy Test program
RetVal = Shell("C:\Program Files (x86)\FreeAlarmClock\FreeAlarmClock.exe", 1)
End Sub

RE: HELP! VBA hangs up copying a range from one workbook to another

I haven't looked through your code in detail, but to simplify things I would recommend reading all the data into a VBA variant array in one operation, then create an array with your output data and write that to the second file in one operation. Something like:

DataArray = Range("input_data").Value

' Create output array, OutAtrray.

Range("output_data").Value = OutArray

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: HELP! VBA hangs up copying a range from one workbook to another

(OP)
A lot of the lines I posted are commented lines from an older version of the code or just to document what I am trying to do.

The "Range.Copy" line that hangs it up is shown here:
Workbooks(Datafile).Worksheets("Data").Range(Cells(FirstRow - 3, 1), Cells(FirstRow, 2)).Copy _
Destination:=ThisWorkbook.Worksheets("Launch").Range("A5")

RE: HELP! VBA hangs up copying a range from one workbook to another

Does the following command give you the original data you wanted to copy? I suspect not?

Debug.Print Workbooks(Datafile).Worksheets("Data").Range(Cells(FirstRow - 3, 1), Cells(FirstRow, 2))


RE: HELP! VBA hangs up copying a range from one workbook to another

(OP)
Agent666,
It is exactly what I want copied. "Firstrow" is a counter used to locate the next row of new data. The data consists of two time/user stamps in Column A (1) and B (2). So, I am copying the last 3 rows of old data and the current row of new data to my spreadsheet.

For example: If Firstrow = 114,
Then I would be copying A111:B114 from my data workbook to the workbook I am using.

RE: HELP! VBA hangs up copying a range from one workbook to another

If you run the same command from the intermediate window with both files open and all the correct paths, variables and names populated, does it run as expected?

I may be wrong, but I thought the destination might need to be the same size range, like A5:B8?

RE: HELP! VBA hangs up copying a range from one workbook to another

(OP)
Agent666,
I'm new to VBA. I just watched a youtube video to find out what the immediate window is and how to use it. I'll have to try it. Thanks!

RE: HELP! VBA hangs up copying a range from one workbook to another

(OP)
Agent666,
I found another window page called "Locals" that was helpful. I finally got the Range problem solved. It's working fine now, but I'm not sure why though. I found a similar "With" statement that makes it work.

Dim rngSource As Range
...
With Workbooks(Datafile).Worksheets("Data")
Set rngSource = .Range(.Cells(FirstRow - 3, 1), .Cells(FirstRow, 2))
End With
'Display 4 rows of "Data" on "Launch" sheet, rows 5-8
rngSource.Copy Destination:=ThisWorkbook.Worksheets("Launch").Range("A5")

RE: HELP! VBA hangs up copying a range from one workbook to another

Its the equivalent of doing this, note the .Cell vs Cell in the original, the Cell reference needed the workbook/sheet reference:-

CODE -->

Workbooks(Datafile).Worksheets("Data").Range(Workbooks(Datafile).Worksheets("Data").Cells(FirstRow - 3, 1), Workbooks(Datafile).Worksheets("Data").Cells(FirstRow, 2)) 

RE: HELP! VBA hangs up copying a range from one workbook to another

When you use Cells(), it will refer to the ActiveSheet. .Cells() refers to the Worksheet in the With nested object.

CODE

'
   With Workbooks(Datafile).Worksheets("Data")
      .Range(.Cells(FirstRow - 3, 1), .Cells(FirstRow, 2)) ThisWorkbook.Worksheets("Launch").Range("A5")
   End With 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

White Paper - The Criticality of the E/E Architecture
Modern vehicles are highly sophisticated systems incorporating electrical, electronic, software and mechanical components. Mechanical systems are giving way to advanced software and electronic devices, driving automakers to innovate and differentiate their vehicles via the electric and electronic (E/E) architecture. As the pace of change accelerates, automotive companies need to evolve their development processes to deliver and maximize the value of these architectures. Download Now
White Paper - Model Based Engineering for Wire Harness Manufacturing
Modern cars, trucks, and other vehicles feature an ever-increasing number of sophisticated electrical and electronic features, placing a larger burden on the wiring harness that enables these new features. As complexity rises, current harness manufacturing methods are struggling to keep pace due to manual data exchanges and the inability to capture tribal knowledge. A model-based wire harness manufacturing engineering flow automates data exchange and captures tribal knowledge through design rules to help harness manufacturers improve harness quality and boost efficiency. Download Now
White Paper - Modeling and Optimizing Wire Harness Costs for Variation Complexity
This paper will focus on the quantification of the complexity related costs in harness variations in order to model them, allowing automated algorithms to optimize for these costs. A number of real world examples will be provided as well. Since no two businesses are alike, it is the aim of this paper to provide the foundational knowledge and methodology so the reader can assess their own business to model how variation complexity costs affect their business. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close