Copy data to new sheet.. more tricky than I imagined [Closed]

Report
Posts
2
Registration date
Friday March 6, 2015
Status
Member
Last seen
March 6, 2015
-
venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hi all,

Could someone please help me out with this? I've tried looking at other similar questions and solutions but as an Excel beginner I couldn't quite figure it out.

So I have the following clumsily written code:


Sub Worksheet_Change(ByVal Target As Range)
Dim wsNew As Worksheet
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

On Error Resume Next

If Not Intersect(Target, Range("B46:B99")) Is Nothing Then

ThisWorkbook.Sheets("LT").Copy _
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

End If
End Sub


It opens a new sheet in the same workbook and I'd need to auto populate certain cells with data from the main sheet. Main sheet: http://i.imgur.com/RJe44hQ.jpg new sheet: http://i.imgur.com/eatbg6j.jpg . The cells I need copied are in red.

Would be truly greatful for any help! Been stuck with this task for too long now..




2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
Quote
auto populate certain cells
unquote
what is meant CERTAIN cells.

Suppose your main data is in sheet 1 and you want to copy all the data in sheet 2 try this

Sub test()
With Worksheets("sheet1")
.Cells.Copy
With Worksheets("sheet2")
.Range("A1").PasteSpecial
End With
End With
End Sub
ElPolloDiablo
Posts
2
Registration date
Friday March 6, 2015
Status
Member
Last seen
March 6, 2015

Thanks for your reply. With certain cells I meant the one's I've marked in the pictures.
And I'd need it to copy that data to an automatically created sheet. (that the code I posted creates) That's the tricky part. Any idea how that can be done?
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
if it is nots oft repeated job it is not worth a macro