Copy/Paste macro from a web query

schelber

Board Regular
Joined
Oct 22, 2005
Messages
172
Hello, I have an easy one for someone , but i am looking for the most concise version.

I have a workbook with 30 worksheets and i import data into each one using a datarefresh of a web query.


I am looking to take the contents of H11:H206 from every worksheet in the book and copy into successive columns starting with 'B'.

The worksheets are named ( i.e. Sam,Wendy etc.....if that matters ).

Does anyone know what the simplest macro would look like to do that.

Regards , Rich
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Hi Rich

Does the name of the workbook change when you use datarefresh and is the location you're looking to copy to a master - if so what is it called


Dave
 

schelber

Board Regular
Joined
Oct 22, 2005
Messages
172
Hi Dave, no the name of the workbook itself does not change. Right now it is called Training.xls.

Basically I data imported into every worksheet once and from that point forward i just refresh the data and the client-server link is made.

The 'master' location is simply a 31st worksheet in the workbook called "Report" whereby these columns get written to.

Regards, Rich
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
This might be a little too simple for your needs but I reckon it can be done with out code.

I assume each column has the title with the name of the individual in question. If not it doesn't really matter.

In B2 (if there is a title B1 if not) Use formula

='Sam'!H11

You would go along each column changing Sam for whatever name is the next sheet.

Once all the columns are done simply copy them all highlight down to row 195 (I think) and paste. Evry time you refresh the data will automatically update.

If this is to simple and you do want code let me know

HTH


Dave
 

schelber

Board Regular
Joined
Oct 22, 2005
Messages
172

ADVERTISEMENT

ha, i see what you are saying. let me just go with that. thanks.\

Rich
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
While your trying it why not run the Macro Recorder, it'll spit out the code for you to edit and use at a later date should (for example) the workbook change is't name or you rebuild it.

Dave
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Code incase you need it for future use would go something like

Code:
Sub FillOutRanges()
With ActiveWorkbook.Sheets("Reports")

    .Range("B1").Value = "='Sam'!H11"
    .Range("C1").Value = "='Wendy'!H11"
' Fill out for all your names remember to change column letter as well
        
        .Range("B1:AE1").Copy
        .Range("B1:AE195").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    End With
End Sub

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,109,435
Messages
5,528,746
Members
409,833
Latest member
tdnhan

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top