PDA

View Full Version : Copy variable range and copy to another sheet



Riaaz66
06-12-2008, 12:21 AM
Hi,

I want to copy a range which number of rows can differ but the number of columns are set to 10. Now I want this range to copy to another sheet.
So, the code should determine the length itself.
How should that be coded in VBA?

Thanks in advance and regards,

Riaaz66

Ago
06-12-2008, 12:55 AM
how should it determine the length?
should it be from top to bottom?
from top to "some value"?
do you want inputboxes where you type in the first and last row?

what are the names of these sheets?

we need to know more to help you.

Riaaz66
06-12-2008, 01:12 AM
Hi Ago,

For example from sheet1 the range A2:Jxxx should be copied to sheet2 to range T2:ACxxx...so yes from top to bottom. Or from top to the last filled row. I don't want to have inputboxes.

You can take any sheetname..let's take Testsheet.xls in this example.

I hope this info will do.

Kind regards and thanks for your time.

LinkND
06-12-2008, 03:54 AM
Hi Ago,

For example from sheet1 the range A2:Jxxx should be copied to sheet2 to range T2:ACxxx...so yes from top to bottom. Or from top to the last filled row. I don't want to have inputboxes.

You can take any sheetname..let's take Testsheet.xls in this example.

I hope this info will do.

Kind regards and thanks for your time.

Hey =) *zou wel Nederlands kunnen praten, maar dan heeft de rest er niets aan ;)*

How about this? It's something very basic and easy, but I think you'll need to select columns and copy it to a new sheet).

However, there is a possibility you're looking for something more complicated. If so, it's indeed harder to accomplish. Do you want to copy data from all columns (10) until the code finds an empty cell? So there is a possibility that column A has 5 filled cells and column B 45? The code needs to loop through the columns until he finds an empty cell, if so, he copies that range and paste it in a second sheet starting from T2.

mdmackillop
06-12-2008, 04:50 AM
Sub testing()
Dim Rng As Range
Set Rng = ActiveSheet.Range(Cells(2, 1), Cells(Columns("A:J").SpecialCells(xlCellTypeLastCell).Row, 10))
Sheets.Add
Rng.Copy ActiveSheet.Range("T2")
End Sub

Riaaz66
06-12-2008, 06:22 AM
Hi LinkND,

Bedankt voor je tijd :)
Your code actually does what I want, but not exactly. I don't want to copy the complete columns, otherwise I would be able to do that myself. The range in columns will be for example A:J (10 columns) but the number rows would differ,like A2:Jxxx. The number of rows is depending on the data I receive. Reange A1:J1 contains the column headings.

:)

LinkND
06-12-2008, 06:47 AM
Hi LinkND,

Bedankt voor je tijd :)
Your code actually does what I want, but not exactly. I don't want to copy the complete columns, otherwise I would be able to do that myself. The range in columns will be for example A:J (10 columns) but the number rows would differ,like A2:Jxxx. The number of rows is depending on the data I receive. Reange A1:J1 contains the column headings.

:)

Kan je niet een excel-bestand toevoegen waarin de data staat en een tab hoe je het wilt hebben ;)

aavvijit
04-23-2010, 05:24 AM
Hi

If I want to copy along with column A to J + column M + column P, in your below mention example how should i write the VBA Code.

Thanks in advance.


Sub testing()
Dim Rng As Range
Set Rng = ActiveSheet.Range(Cells(2, 1), Cells(Columns("A:J").SpecialCells(xlCellTypeLastCell).Row, 10))
Sheets.Add
Rng.Copy ActiveSheet.Range("T2")
End Sub


i

mdmackillop
04-23-2010, 05:47 AM
This copies the whole of the columns.
Sub testing()
Dim Rng As Range
Set Rng = Range("A:J,M:M,P:P")
Rng.Select
Sheets.Add
Rng.Copy ActiveSheet.Range("A1")
End Sub

aavvijit
04-23-2010, 06:16 AM
Thanks,

It is working fine.