Reference groups of excel data

[SilverSN95]

Hi, I am having some trouble copying data from external workbooks.
The macro should merge several workbooks with the same format into one worksheet in the WB running the macro (wbCodeBook). The part I am having trouble with now is pasting the columns from the other worksheets into wbCodeBook. What I want it to do is copy a set of columns from the source WS and append them to whatever is already in wbCodeBook. I'm using cells.count to find the row to append to, because for now I'm assuming the count+1 is the index of the next row to paste to.

Specifically, my problem now is the way I am trying to copy/paste does not actually paste anything. No errors are thrown either.

Here is the portion where I am doing the copy/paste:


[vba]For lCount = 1 To .FoundFiles.Count 'Loop through all other xls files
NumEntries = wbCodeBook("Sheet1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).C ount
rwIndx = NumEntries + 1
If .FoundFiles(lCount) <> wbCodeBook.FullNameURLEncoded Then
'Open Workbook x and Set wbResults to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
wbResults.Sheets(1).Columns("A:I").Copy wbCodeBook("Sheet1").Range("A" & rwIndx & ":I" & rwIndx)
MsgBox "A" & rwIndx & ":I" & rwIndx

'wbResults.Close SaveChanges:=False
End If
Next lCount[/vba]

I'm guessing one of my problems is that I'm copying a column and trying to paste it to a range, but I'm not sure how to better move the data right now.

Thanks for the help.

[lucas]

You should select just one cell for the paste operation I think.

[SilverSN95]

Lucas, I've tried replacing
.Range("A" & rwIndx & ":I" & rwIndx)
with
.Range("A" & rwIndx)
and even hard coded
.Range("A5")

But still nothing gets pasted. If I break up the copy/paste commands, is there a way to see what is in the clipboard by stepping through the macro?

[lucas]

Maybe stop it after this:
[VBA] wbResults.Sheets(1).Columns("A:I").Copy[/VBA]

and manually paste......

[SilverSN95]

Ok, I just tried that, and it seems to confirm that the problem is due to trying copy columns to a range. What I'll need to do is figure out how to select the useful (non-empty) range from those columns before pasting.
Alternatively I could try just c/p row-by-row in a loop but I'm hoping there might be a more elegant solution.

[lucas]

Does that mean it's not copying?

[SilverSN95]

No it seems to copy fine, but it only pastes when I highlight the correct number of columns only, but not any form of range.

I'm going to mark as solved as I am going to go about this a different way, but feel free to add any other comments.

Thanks.

[SilverSN95]

Hello,

I have a worksheet template that a macro will be extracting data from. The worksheet is nicely grouped by rows already (rows are collapsible/expandible by clicking on different levels). It would make writing parts of the macro much easier if there were a way to reference these groupings in code (i.e. copy/paste by groups of data).
Is there a way to do this?

Thanks again!

[lucas]

by collapsible do you mean filtered?

[SilverSN95]

They are related as they are part of the same project, but I thought making a new thread would better as I really just need to know if/how to reference these groupings, and I should be able to do the copy/pasting and modifications from there.

I think they are just groupings, from the data>Group and Outline menu but there are + and - corresponding to groups of rows that can be expanded or collapsed.

[lucas]

threads merged

[lucas]

If you only want to copy the visible rows look up
[VBA]SpecialCells(xlCellTypeVisible)[/VBA]