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

Jimmers

New Member
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.

Project Charter SheetLongform Sheet
Cell E3Cell A2
Cell A7Cell B2
Cell M7Cell C2
Cell M9Cell D2
Cell M11Cell E2
Cell M15Cell F2

<tbody>
</tbody>

The above will never change.

Is this possible to run/Create?
 

DanteAmor

Well-known Member
I did not understand.
Do you have several sheets or only 2 sheets?

You need something like this:

Code:
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

New Member
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?
 
Last edited:

DanteAmor

Well-known Member
Try this

Code:
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
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top