[VBA] Copy data from Certain Cells In a Sheet with different titles, but layout is the same?

[Jimmers]

Hi all,

Wondering if you could help me on this as I'm really not sure where to go.

I am trying to copy data from a Sheet (A Project Charter) into a longform sheet that can then be used to update a tracker.

The cells the Macro will be copying from will always be consistent however the Title of the Project Charter Sheet will likely always be different.

It will likely be copying as follows.

[TABLE="width: 247"]
<tbody>[TR]
[TD]Project Charter Sheet[/TD]
[TD]Longform Sheet[/TD]
[/TR]
[TR]
[TD]Cell E3[/TD]
[TD]Cell A2[/TD]
[/TR]
[TR]
[TD]Cell A7[/TD]
[TD]Cell B2[/TD]
[/TR]
[TR]
[TD]Cell M7[/TD]
[TD]Cell C2[/TD]
[/TR]
[TR]
[TD]Cell M9[/TD]
[TD]Cell D2[/TD]
[/TR]
[TR]
[TD]Cell M11[/TD]
[TD]Cell E2[/TD]
[/TR]
[TR]
[TD]Cell M15[/TD]
[TD]Cell F2
[/TD]
[/TR]
</tbody>[/TABLE]

The above will never change.

Is this possible to run/Create?

 

[Jimmers]

The Sheet Title within the Project Charter is also called "Project Charter" as well.

 

[DanteAmor]

I did not understand.
Do you have several sheets or only 2 sheets?

You need something like this:

Sub test()
  With Sheets("Project Charter")
    .Range("E3").Copy Sheets("Longform").Range("A2")
    .Range("A7").Copy Sheets("Longform").Range("B2")
    .Range("M7").Copy Sheets("Longform").Range("C2")
    .Range("M9").Copy Sheets("Longform").Range("D2")
    .Range("M11").Copy Sheets("Longform").Range("E2")
    .Range("M15").Copy Sheets("Longform").Range("F2")
  End With
End Sub

 

[Jimmers]

Apologies Dante, I really have described this poorly.

I will have two workbook open;

One Workbook is titled "BPE Dashboard" and contains a sheet called Long_Form

One Workbook may have a differing title each time But would always start "Project Charter -"

Then we need to copy the data as above from the sheet "Project Charter" Within the Workbook titled "Project Charter-" into the Workbook "BPE Dashboard", Sheet "Long_Form"

Hope this makes sense?

 

[DanteAmor]

Try this

Sub Copy_data()
  Dim wb1 As Workbook, wb2 As Workbook, wb As Workbook
  Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
  Set wb2 = Workbooks("BPE Dashboard")
  Set sh2 = wb2.Sheets("Long_Form")
  For Each wb In Workbooks
    If LCase(Left(wb.Name, 15)) = LCase("Project Charter") Then
      Set wb1 = wb
      Exit For
    End If
  Next
  Set sh1 = wb1.Sheets("Project Charter")
  lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
  sh1.Range("E3").Copy sh2.Range("A" & lr)
  sh1.Range("A7").Copy sh2.Range("B" & lr)
  sh1.Range("M7").Copy sh2.Range("C" & lr)
  sh1.Range("M9").Copy sh2.Range("D" & lr)
  sh1.Range("M11").Copy sh2.Range("E" & lr)
  sh1.Range("M15").Copy sh2.Range("F" & lr)
End Sub