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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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:
Upvote 0
"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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top