VBA Excel code won't paste

[swtrader]

I have used this code previously and it worked. I think some kind Excel MVP originated it. It opens each workbook in a specified folder, goes to a cell, populates a variable with the value and then 'pastes' the variable value into the main, always-open workbook (on the next succeeding row). I put in MsgBox checks to see that the data is populating correctly and it seems to be. However, it does not 'paste'. Suggestions?

Sub CopyDataFromEaSpdshtInABCCoFolder()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim i As Long
Dim a As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Application.FileSearch
.NewSearch
.LookIn = "The Path is here"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
rnum = 1
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange = mybook.Worksheets(1).Range("C58")
a = sourceRange.Rows.Count
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, 3). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
mybook.Close
rnum = i * a + 1
Next i
End If
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[combo]

In this code always a=1, destrange = basebook.Worksheets(1).Cells(1, 3).Resize(1,1) (sourceRange is 1x1, so has one row and one column). Is this what you intended to get?

combo

 

[swtrader]

Admittedly, I don't understand sourcerange. However, I'm trying to pull the value from only one cell on each source worksheet....C58....and copy that value to successive rows in Column A of the target or basebook.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[SkipVought]

sw,

BTW, the word PASTE means something very specific in MS Applications. It refers to using the CLIPBOARD in a COPY 'n' PASTE operation, which is NOT what you are attempting to do. You are assigning a value from the sourcerange to the destrange.

Your problem is with destrange. It is referencing rows and columns from sourcerange. I'd use instead...

   Set destrange = ThisWorkbook.Worksheets(1).Cells(1, 3).End(xldown).offset(1)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[swtrader]

Noted on the 'paste' and understood re: the variable.

I've modified my code as you suggest, Skip. The variables are populated correctly (I check them with MsgBox) but no cells are being updated on my 'base' worksheet. Am I missing a line of code?

 

[SkipVought]

Put a BREAK & use a Watch Window...

faq707-4594

to observe the range references for

  destrange.Value = sourceRange.Value

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}