Is it possible to use formulas to copy data from one sheet to another with an unknown range?

[x8xviperx6x]

Just as the title states. I've been searching for a while, having a hard time with my search keywords, couldn't find anything so far other than VBA codes. Unfortunately I'm trying my best to stay away from VBA codes.

What I am trying to do is copy data from one sheet to another, the problem is that the ranges for the rows and columns change daily...

For example, the data comes from extracted reports, whichgets delimited and pasted into a sheet1. An example range could be data from a1to ac2500 the first day then the next from a1 to ac10000+. The columns are theonly thing that doesn’t change.
On sheet2 I only need 5 columns to be extracted but I onlyneed the rows with data. Currently I’ve tried the simple “=” all the way downto row 10,000 but it will return zeros when sheet1 doesn't have data to 10,000 or data wont show if range is past 10,000.

From the Sheet2 I use that remaining data to create a pivottable. Would this copy from sheet1 to sheet 2 be possible without using any typeof VBA?

Thanks!

 

[yky]

What do you want to be returned if there is no data? Blank?

Try this:

=IF(ISBLANK(Sheet1!A1),"",Sheet1!A1)

 

[Michael M]

with varying amounts of data, VBA would be the best option....VBA can also create the pivot table at the same time !!!

 

[x8xviperx6x]

What do you want to be returned ifthere is no data? Blank?

Try this:

=IF(ISBLANK(Sheet1!A1),"",Sheet1!A1)

Unfortunately this isnt what im looking for because I still have to take thatformula and run them down to X. I would need a code that tells the fill to go downuntil it saw a blank space or something.

So for example the data with 100 lines on sheet1 fillssheet2 and stops at line 100 with same data, then when I paste on sheet1another type of data going to line 5000 sheet2 will do the same but stop atline 5000.

This is so I don’t have to drag down to 20, 30, or even50k lines which some of the reports have upwards to 50k+…

with varying amounts of data, VBA would be the bestoption....VBA can also create the pivot table at the same time !!!

I was afraid of this answer just because I am not very familiar with VBAcoding. It sounds very pleasing, although with the amount that the sheetschange I would have to ask every time we use a new report and new table.

Currently I run 6 reports that vary in length. Two ofwhich have Pivot tables. The pivot tables are fairly painless, but would benice if they were automated as the format we use never change.

 

[Michael M]

definitely sounds like a macro would suit this.
Have you looked at using the macro recorder for your PivotTables ?
If you don't want to do that, provide some more detailed information and someone may be able to write the code for you !

 

[yky]

Unfortunately this isnt what im looking for because I still have to take thatformula and run them down to X. I would need a code that tells the fill to go downuntil it saw a blank space or something.

So for example the data with 100 lines on sheet1 fillssheet2 and stops at line 100 with same data, then when I paste on sheet1another type of data going to line 5000 sheet2 will do the same but stop atline 5000.

This is so I don’t have to drag down to 20, 30, or even50k lines which some of the reports have upwards to 50k+…

If you want a formula, you have to paste the formula. I don't see how you can have a formula in those cells without pasting the formula to those cells. Even with VBA, you still have to paste the formula to those cells by code unless you paste the value directly.

In short, if you don't want to paste formula, you can't have a formula solution.

 

[x8xviperx6x]

definitely sounds like a macrowould suit this.
Have you looked at using the macro recorder for your PivotTables ?
If you don't want to do that, provide some more detailed information andsomeone may be able to write the code for you !

Yeah, I didn’t think about the macro recorder I may give this a shot, thank youfor your input!

If you want a formula, you have to paste the formula. Idon't see how you can have a formula in those cells without pasting the formulato those cells. Even with VBA, you still have to paste the formula to thosecells by code unless you paste the value directly.

In short, if you don't want to paste formula, you can't have a formulasolution.

This makes sense. I washoping for something like a formula that would change the ending of a range. Forexample instead of A:A I could do A:A(isblank) type deal but I suppose not.Thanks a lot.