Copying to list

[Arne-Olaf]

Hello!
I`m a newbe to VBA and enjoying first steps. I have some problems that I can`t solve regarding copy to lists in Excel. I want to copy a range from one sheet to another. I use lists in both sheets. Problem is that the target list don`t have enough rows to accept paste. The number of rows changes. Suggestion: VBA that count rows in the source range, store the value and resize the target list in the new sheet based on stored value. Then paste the range. Any suggestions appreciated.
Regards Arne-Olaf

[lucas]

Hello Arne,
If you could give us a small example file of what your trying to do I'm pretty sure there is an easier way to do this. Copy and paste may not be the best way to go.

[mdmackillop]

To post a sample, use Manage Attachments in the Go Advanced section.

[Arne-Olaf]

Here`s a small exampel of what I want.

Thanks in advance.

Arne-Olaf

[Charlize]

Sub copy_it()
Dim cell As Range
Dim sourcerng As Range
Dim destrng As Range
Set sourcerng = Sheets("Ark1").Range("B2:B" & _
Sheets("Ark1").Range("B" & Rows.Count).End(xlUp).Row)
Set destrng = Sheets("Ark2").Range("B2")
For Each cell In sourcerng
destrng.EntireRow.Insert
Next cell
Set destrng = Sheets("Ark2").Range("B2")
sourcerng.Copy destrng
End Sub

[mdmackillop]

To make Range2 dynamic, insert the following into the "Refers to" box
=OFFSET(Ark2!$A$1,1,0,IF(COUNTA(Ark2!$A:$A)=1,1,COUNTA(Ark2!$A:$A)-1),1)

To copy data into this range use
Sub Copies()
Range("Range1").Copy Range("Range2")(1)
End Sub

[Arne-Olaf]

Hi again,

Thanks for your reply. Your solution Charlize wasn`t exactely what I was looking for. Problem is that it copies all values in column b. Set b12 to 10, and you`ll see what I mean. I only want to copy Range1 - values within the list. Look in namebox. I tried to change it like this:


Sub copy_it()
Dim cell As Range
Dim sourcerng As Range
Dim destrng As Range
Set sourcerng = Sheets("Ark1").Range("Range1" & _
Sheets("Ark1").Range("Range1" & Rows.Count).End(xlUp).Row)
Set destrng = Sheets("Ark2").Range("B2")
For Each cell In sourcerng
destrng.EntireRow.Insert
Next cell
Set destrng = Sheets("Ark2").Range("B2")
sourcerng.Copy destrng
End Sub


No luck with this :(. I`m a newbie and missing something, or better a lot.


And to mdmackillop: I don`t think the dynamic range actually will change the size of the list.


Regards
Arne-Olaf

[mdmackillop]

I don`t think the dynamic range actually will change the size of the list.

Curious. I thought that's why it was called dynamic. Did you test it?

[Arne-Olaf]

Yes, I have tested. With dynamic range. The range expands, but sadly not the list. I got a error message when I pasted in your offset formula. The amount of rows will be max 500.

[mdmackillop]

Can you post a sample. I don't understand your use of the word "list"

[Arne-Olaf]

List refers to ListObjects. I have one in each sheet.

[lucas]

Malcolms code implemented into your workbook.....see attached
add items to the range in sheet one and hit the button.....add more items and hit the button again....etc.

[Arne-Olaf]

Thanks for your reply. A few issues: Any information beneath the ListObject in sheet 2 will be overwritten, and if the number of rows in sheet 1 decreases there is no update for theese "outdated" rows in sheet 2. Seems that this is a tricky one.

Arne-Olaf

[lucas]

You have not made it clear what you are talking about when you mention ListObject....Malcolm has already asked you what you mean and I don't understand either.

As far as updating you can simply clearcontents on range 2 before you do the copy:
Sub Copies()
Range("Range2").ClearContents
Range("Range1").Copy Range("Range2")(1)
End Sub

[Arne-Olaf]

Here`s a link to Excel lists: http://office.microsoft.com/en-us/excel/HP010044331033.aspx The blue line represents the border of the list. I appologize for my limited knowledge of terms related to this topic.

Arne-Olaf

[lucas]

So you have a sheet that you want a dynamic range on but you have data below and if the range explands it will overwrite your data below...

I think you will have to use a different approach to this.

[Arne-Olaf]

You`ve got it right.. and I am still struggeling.

[lucas]

Can you possibly change the order and have the dynamic range be below the existing data?

[Arne-Olaf]

No, sorry that`s not an option. I have sheets with several lists below one another with other information in between them. For reporting.

Arne-Olaf

[mdmackillop]

I confess I've never come across the "lists" before. As you've not posted a "real life" picture, I'm working in the dark when it comes to inserting cells etc. and the impact on your spreadsheet.
This seems to work with your posted example.
Option Explicit
Sub MessWithLists()
Dim Rws As Long, LstRws As Long, AddRws As Long
'Count rows in List 1
Rws = Range("Range1").Rows.Count
'Count rows in Sheet2 list
LstRws = Sheets("Ark2").ListObjects("Liste1").ListRows.Count
'Extra rows required
AddRws = Rws - LstRws
'Insert cells after list
Sheets("Ark2").ListObjects("Liste1").HeaderRowRange.Offset(LstRws).Resize(AddRws).Insert shift:=xlDown
'Copy data
Range("Range1").Copy Range("Range2")(1)
End Sub