Insert data from one worksheet into another

[Devotchka]

I have two worksheets Star and Atlas. These two worksheets get updated daily so the number of rows change, but all the columns stay the same. There is information in Star that starts in column P2 and ends at a different place depending on the day the data is imported. What I need is to insert from Star P2 to Atlas column Q first blank cell. I need it to keep repeating until all the information from column P (starting at P2) is inserted. Please please help! I would greatly appreciate it.

Thank you.

 

[VoG]

Try

Sub CopyP()
Dim LR As Long
With Sheets("Star")
    LR = .Range("P" & Rows.Count).End(xlUp).Row
    .Range("P2:P" & LR).Copy Destination:=Sheets("Atlas").Range("Q" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub

 

[Devotchka]

Thank you for such a quick reply! I tried opening a new blank sheet and typed in your code and it works perfect, but for some reason when I use it with my existing file it refuses to work. Nothing happens! Does the order of the tabs matter?

 

[VoG]

No, the order of the tabs doesn't matter.

 

[Devotchka]

Ok I got it to work. Thank you so much! You're a life savor!!! I really appreciate it!

 

[Devotchka]

I figured it out! thanks!

 

[VoG]

Assuming that columns P and Q have the same number of rows try

Sub CopyP()
Dim LR As Long
With Sheets("Star")
    LR = .Range("P" & Rows.Count).End(xlUp).Row
    .Range("P2:P" & LR).Copy Destination:=Sheets("Atlas").Range("Q" & Rows.Count).End(xlUp).Offset(1)
    .Range("Q2:Q" & LR).Copy Destination:=Sheets("Atlas").Range("O" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub

 

[Devotchka]

I'm sorry for asking so many questions, but I have one little problem. Some of the cells I'm "copying" from Star are VLOOKUPs and since they're formulas they show up in Atlas as "#N/A" How can I do a paste special as values instead?

 

[VoG]

Try this

Sub CopyP()
Dim LR As Long
With Sheets("Star")
    LR = .Range("P" & Rows.Count).End(xlUp).Row
    .Range("P2:P" & LR).Copy
    Sheets("Atlas").Range("Q" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    .Range("Q2:Q" & LR).Copy
    Sheets("Atlas").Range("O" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End With
End Sub

 

[Devotchka]

That worked perfectly! Thank you so much for your help today. You are amazing at VBA.