Macro to Find a certain string and then copy

January 14, 2011 at 02:54:09
Specs: Windows XP
Hi,

I need to write a Macro wherein it finds a particular string ("Date") on a sheet and then copy the table that exists below it into the next sheet. The issue is the data doesn't remain on particular cell as the excel retrieve data from the web.

The below is a query i got but which only retrieves the Date field and not the entire column and row. I am quite new to Macros. Could you please help?

Sub copydate()
Dim rngCopyFrom As Range, rngCopyTo As Range, firstCopied As Range
Dim wksFrom As Worksheet, wksTo As Worksheet
Set wksFrom = ThisWorkbook.Sheets("Sheet1")
Set wksTo = ThisWorkbook.Sheets("Sheet2")
Set rngCopyFrom = wksFrom.Cells.Find("Date")
If rngCopyFrom Is Nothing Then
MsgBox "No 'Date' cells found"
End
End If
Set firstCopied = rngCopyFrom
Set rngCopyTo = wksTo.Range("A1")
rngCopyTo.EntireRow.ClearContents

rngCopyFrom.Copy
wksTo.paste rngCopyTo
Application.CutCopyMode = False

-Poo


See More: Macro to Find a certain string and then copy

Report •

#1
January 14, 2011 at 06:12:08
Your requireements are not clear to me.

First you said this:

then copy the table that exists below it into the next sheet

Then you said this:

which only retrieves the Date field and not the entire column and row.

Typically, a "table" does not consist of a single column and single row.

In other words, if "Date" is found in B14, you appear to be asking that Column B and Row 14 be copied.

That would not be a "table that exists below it".

Please clarify.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 14, 2011 at 06:42:24
Hi,

Apologies for not being clear. The code i have given is only retrieving the "Date" field, which is incorrect. I require a code to retrieve the table under the "Date" field. My sheet retrieves data from a website and on a daily basis the website refreshes.

The data from the website has a table as below :

DATE LT COMPOSITE (>10 yrs) DATE TREASURY 20-yr CMT EXTRAPOLATION FACTOR
1/3/2011 4.03 1/3/2011 4.18 N/A
1/4/2011 4.06 1/4/2011 4.21 N/A
1/5/2011 4.19 1/5/2011 4.34 N/A
1/6/2011 4.15 1/6/2011 4.31 N/A
1/7/2011 4.08 1/7/2011 4.25 N/A
1/10/2011 4.06 1/10/2011 4.23 N/A
1/11/2011 4.1 1/11/2011 4.26 N/A
1/12/2011 4.11 1/12/2011 4.28 N/A
1/13/2011 4.08 1/13/2011 4.24 N/A

This table does not come up on the same cell everyday when the website refreshes, hence i was looking for a code that would help me search for a string and then copy and paste only this table to the next sheet.

Thanks. :)


Report •

#3
January 14, 2011 at 07:07:22
I assume that you did not read the How To referenced in my signature line before posting your data. Please do so before posting any more data or code.

It's hard to tell how many columns you have. (5?)

Is it always (5?)?

Does the number of rows vary? Is there any data below the data you want to copy?

Finding "Date" will give VBA the starting address of the range we want to copy, but we need a way to either tell VBA, or have VBA calculate, the final cell address of the range.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 14, 2011 at 07:47:19
Hi,

I did not read the post below. Yes the table will always have 5 rows, however the columns will increase to show the current day's rates. I have posted the table below, however it doesn't seem like the table is getting correctly formatted. I will paste the link below that i will be using for the spreadsheet.

DATE	LT COMPOSITE (>10 yrs)	DATE	TREASURY 20-yr CMT	EXTRAPOLATION FACTOR
1/3/2011	4.03	1/3/2011	4.18	N/A
1/4/2011	4.06	1/4/2011	4.21	N/A
1/5/2011	4.19	1/5/2011	4.34	N/A
1/6/2011	4.15	1/6/2011	4.31	N/A
1/7/2011	4.08	1/7/2011	4.25	N/A
1/10/2011	4.06	1/10/2011	4.23	N/A
1/11/2011	4.1	1/11/2011	4.26	N/A
1/12/2011	4.11	1/12/2011	4.28	N/A
1/13/2011	4.08	1/13/2011	4.24	N/A
Thursday Jan 13, 2011, 4:53 PM				

The link:

http://www.treasury.gov/resource-ce...


Report •

#5
January 14, 2011 at 07:50:31
Hi,

I did not read the post below. Yes the table will always have 5 rows, however the columns will increase to show the current day's rates. I tried posted the table below, however it doesn't seem like the table is getting correctly formatted. I will paste the link below that i will be using for the spreadsheet.


The link:

http://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=longtermrate


Report •

#6
January 14, 2011 at 08:05:31
re: "Yes the table will always have 5 rows, however the columns will increase to show the current day's rates"

Now I'm even more confused.

The table you posted in Response 4 (and at the website) has more than 5 rows.

The table in your response has 5 columns, 2 of which have headings of "Date", but the website only has 4 columns and one heading of "Date".

Please explain.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
January 14, 2011 at 08:38:58
The Table will be as shown in the website.

Report •

#8
January 14, 2011 at 11:36:41
I modified yours and then wrote one of my own.

Different paths to the same place...

Sub copydate()
Dim rngCopyFrom As Range, rngCopyTo As Range
Dim wksFrom As Worksheet, wksTo As Worksheet
  Set wksFrom = ThisWorkbook.Sheets("Sheet1")
  Set wksTo = ThisWorkbook.Sheets("Sheet2")
  Set d = wksFrom.Cells.Find("Date")
'Check to see if string was found
'If not, tell the user
    If d Is Nothing Then
      MsgBox "No 'Date' cells found"
      End
    End If
'If it exists, build range and copy it to Sheet2!A1
'Determine last used row in table
   endRow = Cells(Rows.Count, d.Column).End(xlUp).Row
'Set range of entire table
  Set rngCopyFrom = Range(d.Address & ":" & Cells(endRow, d.Column + 3).Address)
  Set rngCopyTo = wksTo.Range("A1")
'***Why is this here? This will only clear Sheet 2 Row 1.
    rngCopyTo.EntireRow.ClearContents
'Copy Table to Sheet2!A1
    rngCopyFrom.Copy Destination:=rngCopyTo
    Application.CutCopyMode = False
End Sub


Sub MyCopyDate()
Dim d As Range, endRow As Integer
 With Sheets(1)
'Find "Date"
  Set d = .Cells.Find("Date")
'If it exists, build range and copy it to Sheet2!A1
   If Not d Is Nothing Then
    endRow = .Cells(.Rows.Count, d.Column).End(xlUp).Row
     Range(d.Address & ":" & Cells(endRow, d.Column + 3).Address).Copy _
      Destination:=Sheets(2).Range("A1")
'If not, tell the user
   Else: MsgBox "No 'Date' cells found"
   End If
 End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
January 15, 2011 at 05:22:42
Hi,

Thanks so much, this works perfectly fine.:). However i am now looking to eliminate the text that appears below the table. I want to display only the table on sheet 2. The rows increase on a daily basis until the end of the month. For example if the website is showing numbers for 14th today, this will increase on monday to show numbers for the 17th and so on. Is it possible to copy only the table to sheet 2?

Sorry for the trouble.


Report •

#10
January 15, 2011 at 11:59:45
re: "i am now looking to eliminate the text that appears below the table."

Keep in mind that we can't see your spreadsheet from where we're sitting, nor do we know what process you are using to bring the data into the spreadsheet.

For that reason we (well, at least I) don't know what "text" you have below the table.

You are going to have to be more specific, perhaps telling us if there is something that is always at the end of the table so that the code can determine where the table ends and the "text" begins.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
January 17, 2011 at 03:37:14
Hi,

Sorry for not being clear. Is there a way to attach the spreadsheet here? What i meant to say is, the code is working perfectly fine but it doesn't copy only the table. It copies the text below the table too. If you open the link, you will be able to see some text after the table. The code also retrieves the text below the table from the website. However i want only the table on my spreadsheet. Is it possible when the rows are not constant and increase on a day to day basis?


Report •

#12
January 17, 2011 at 15:27:51
If I look at the website today, I see that the table ends on 1/14/2011

Then I see Monday Jan 17, 2011, 7:04 AM off on the right hand side.

Then I see a paragraph that starts with:

Treasury Long-Term Average Rate and Extrapolation Factors.

I assume you do not want "Treasury Long-Term Average Rate and Extrapolation Factors" or anything after that.

Do you want Monday Jan 17, 2011, 7:04 AM or do you want to stop at 1/14/2011?

When you do the download, where does Monday Jan 17, 2011, 7:04 AM end up? i.e. In what Column?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
January 18, 2011 at 00:28:35
Hi,

"I assume you do not want "Treasury Long-Term Average Rate and Extrapolation Factors" or anything after that." - Yes that is right. That is the text i was looking to eliminate.

Yes i want to stop at 1/14/2011. When i do the download ,Monday Jan 17, 2011, 7:04 AM ends up in Merged Column A to E.

Today or tomorrow if you look at the website, you will find a row added below 1/14/2010 and will display 1/17/2010 or 1/18/2010. I am not sure of 1/17/2010 as it was a US holiday and it may not be posted on the website.


Report •

Ask Question