Copy data to new sheet.. more tricky than I imagined

Closed
ElPolloDiablo Posts 2 Registration date Friday March 6, 2015 Status Member Last seen March 6, 2015 - Mar 6, 2015 at 02:52 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 7, 2015 at 12:21 AM
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: https://i.imgur.com/RJe44hQ.jpg new sheet: https://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 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 6, 2015 at 06:30 AM
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
0
ElPolloDiablo Posts 2 Registration date Friday March 6, 2015 Status Member Last seen March 6, 2015
Mar 6, 2015 at 06:45 AM
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?
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 7, 2015 at 12:21 AM
if it is nots oft repeated job it is not worth a macro
0