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

[SteveGregory]

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]

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]

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]

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]

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]

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]

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]

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]

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

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

 

[SkipVought]

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

'
   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!}