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

Question

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

SteveGregory (Structural)

(OP)

7 Dec 19 21:57

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

IDS (Civil/Environmental) · Answer 1 · 2019-12-08 03:45:41

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/

SteveGregory (Structural) · Answer 2 · 2019-12-08 04:03:50

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")

Agent666 (Structural) · Answer 3 · 2019-12-08 06:49:58

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))

SteveGregory (Structural) · Answer 4 · 2019-12-08 12:25:58

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.

Agent666 (Structural) · Answer 5 · 2019-12-08 16:56:16

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?

SteveGregory (Structural) · Answer 6 · 2019-12-08 18:55:16

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!

SteveGregory (Structural) · Answer 7 · 2019-12-09 00:57:34

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")

Agent666 (Structural) · Answer 8 · 2019-12-10 05:56:56

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))

SkipVought (Aerospace) · Answer 9 · 2019-12-10 14:55:24

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,
_{Just traded in my OLD subtlety...

for a NUance!}

Come Join Us!

Posting Guidelines

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