VBA to copy range from one worksheet to another 1

[SpeedThink]

Currently, I select the current region of a worksheet ("source worksheet") by manually pressing "Shift" and the right arrow to select the data across columns then I press the down arrow.

Finally, I copy the data to cell "B10" of another worksheet ("data worksheet").

The number of columns on my source worksheet never changes. However, the number of rows changes on a daily basis - from 200 rows on a Monday to approximately 500 to 600 on a Tuesday, etc.

Currently, I am using the record macro function but cannot get this to work. The resulting error indicates that the destination range and the source range are different sizes...

Any insight?

 

[Loomah]

Hi

The following should do the trick

worksheets("source").range("a1").currentregion.copy _
destination:=worksheets("destinatiion").range("b10")

A little tip for selecting the range manually is to use CTRL+*

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SpeedThink]

It worked perfectly!

Now, the only thing is that I have "automatic numbering" formulae on my destination sheet in columns "A" and "N" down to row 1000 similar to "=IF((B10<>""),COUNTA($B10:B$10), " ")" and "=IF((L10<>""),ROUND(I10/H10,2), " ")", respectively.

Also, there is a "Set Print Area Dynamically" macro that automatically selects only the used region of the spreadsheet for printing. However, I print out 23 pages instead of the pages that only contain visible data. Note, the formulae in columns A and N are not visible and the results of the formulae only appear if there is data in columns "B" and "L", respectively.

Therefore, are you aware of the vba to delete all rows after the last populated row in columns B through N so that the "Set Print Area Dynamically" macro can function properly.

Maybe, a macro that deletes all unused rows of the worksheet after the last entry in column B through N???

Thanks in advance.

 

[Loomah]

Hi
Summat like this could do the deleting trick. If you need to add your formulas back in at a later date you could have a look at the AutoFill method.

Range([b65536].End(xlUp).Row + 1 & ":" & _
    ActiveSheet.UsedRange.Rows.Count).EntireRow.Delete

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SpeedThink]

Works perfectly!

Thanks.