VBA Copy/Paste dynamic table into cells based on text reference

ArtofExcel

New Member
Joined
Jul 5, 2016
Messages
17
I'm trying to make a macro that will copy a table with what will be an unknown length and paste it into a new location based on a reference text cell. Cell D1 will contain the text Week X where X is the current week of the year and the table is 5 columns wide (the source being in A:E starting in row 3). The macro needs to be able to search all of row 1 for the matching week number and paste the table with the top left corner 2 rows (so starting in row 3) directly under the reference text. the struggles I'm running into with this so far are:

1) Copying the full table with the varying length, I do not know how big the input will be so it needs to be able to adjust on every use
2) Finding the reference text in VBA (I tried using .find but it did not seem to work in any appreciable way)
3) Pasting the cells based off the reference text range found when it will be in the form of a variable and not something I can just do something like "A" & i type of manipulation on

Any guidance would be much appreciated! Thank you in advance!
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,274
You said:
Cell D1 will contain the text Week X

Put in quotes exactly what will be in cell D1

Will it be "Week 12"
Or:
"12"

Or:
"Week12"

And your saying the exact text in "D1" Will be found in some other column in Row (1)
And in that column Row 3 we will paste your data from Columns "A:E

Not sure what this means:
Your quote:
"and paste the table with the top left corner 2 rows "
 
Last edited:

ArtofExcel

New Member
Joined
Jul 5, 2016
Messages
17
"Week 12" per your example but it will change each week to the appropriate week.

Yes the exact text in D1 will be found in 1 other location in row 1.

And yes the source will be in A:E starting in row 3 as well.

What I was trying to say with "paste the table with the top left corner 2 rows (so starting in row 3) directly under the reference text." Was that the top left cell of the table will be 2 rows directly under the reference cell which is in row 1. So if D1 (the cell that changes each week) says "Week 1" and I have "Week 1" in Cell G1 I want the Table to be in G:K starting in row 3.
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,274
Try this:

Code:
Sub Copy_Data()
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim Lastrow As Long
Dim LastColumn As Long
Dim ans As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For Each c In Range(Cells(1, 5), Cells(1, LastColumn))
        If c.Value = Range("D1").Value Then ans = c.Column
    Next

Range("A3:E" & Lastrow).Copy Destination:=Cells(3, ans)
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "No such Week Exist"

End Sub
 

Forum statistics

Threads
1,081,981
Messages
5,362,538
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top