×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

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!

*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

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



News


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