Getting Path from FilePicker so as to copy and paste a sheet

[bigjdawg43]

Hi,

I think this is a fairly simple question, but I'm still new at this and so I don't know the answer. I have the below I'm using to enable the user to choose a file from a dialog box:

Please Login or Register  to view this content.

I'd like to take columns A:AW of the "Test" sheet on the workbook selected by the user and paste it into "This Workbook" same sheet and range. I'm attempting this using the below:

Please Login or Register  to view this content.

That clearly doesn't work. Is the problem here that while the user has chosen the portfolio, "SourcePortfolio", the user has actually NOT chosen the exact path, so my Workbooks code above is not working. If that's the case, can someone please advise how I can fix it?

Thanks!

[Mumps1]

Try:

Please Login or Register  to view this content.

[bigjdawg43]

Perfect! Thank you.

[Mumps1]

You are very welcome.

[bigjdawg43]

I'm sorry, but I have one additional question.

The sheet that I'm working with is effectively relies on a dynamic matrix whereby the columns and rows can change. So, as you can see, the first part of this process is taking a sheet that is holding said dynamic matrix and pasting it elsewhere. When I call up the code it seems that row 1 pastes accordingly. But if, for example, the dimensions of the matrix change, the remaining rows after 1 don't seem to adjust (the heading in column C might move correctly to column D but the remaining rows in column C DO NOT move to the appropriate portion of column D).

Any idea why that might be happening? Thanks again!

[bigjdawg43]

in fact, i'm wondering if it would be more effective/efficient to simply copy all cells on the sheet instead of that specific range...

[Mumps1]

Try replacing this line of code:

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

[bigjdawg43]

Ok that's much closer and actually, as I'm testing it, will probably work in 99% of cases. That said, it seems like because of the way UsedRange works, I do have one example sheet that isn't being copied correctly. What's the easiest way to choose all cells on the sheet and copy them (effectively the button above row 1 to the left of column A). Even though the above code works in almost all of my cases, I think simply copying and pasting all the cells might ensure that the code will always work.

[Mumps1]

This code

Please Login or Register  to view this content.

should copy all the cells. What is the difference between the one sheet where the data isn't being copied correctly and the other sheets?

[bigjdawg43]

it's mainly a function of column width. Typically most of the ranges i'm dealing with are A-AI (I'm mainly using a subset of this, but still the entire matrix width covers that range). Yet, the main difference in the one that acts a bit strange is that the range covers A-BD. So the range is much larger, but based on the code I'm not sure why that would matter.

[Mumps1]

"UsedRange" sometimes doesn't act as expected. Can you attach a file with a copy of the sheet that is giving you problems?

[bigjdawg43]

Sure. Keep in mind the numbers no longer make sense because they're all randomly generated to preserve confidentiality. But you can see this version has many more columns than the original used in the test case and for some reason I notice that the header (row 1) seems to copy over but nothing else does so everything becomes misaligned...

[bigjdawg43]

Attachment...

[Mumps1]

If you run this test macro:

Please Login or Register  to view this content.

and check the sheet, you will notice that the entire used range has been copied correctly so I don't see why it is giving you a problem.

[bigjdawg43]

Ok, not sure what the problem was, but it seems like it was on my end. I think it all works now. Thank you so much!

[Mumps1]

My pleasure.

[bigjdawg43]

Sorry to open this up again, but I think I found my issue (sort of).

I've attached an example of output after just this copy code was run. The paste should end in row 614, but as you can see part of the top half of the sheet was pasted below in the bottom half (see row 552). The bottom half, however, isn't even pasting directly; rather the paste almost seems in some ways almost random. Is this mistaken paste on the bottom somehow related to "UsedRange"?

Thanks again.

[bigjdawg43]

it looks like I need to insert ClearContents prior to the paste

[bigjdawg43]

Yup, got it. Thanks again

[Mumps1]

My pleasure.