Macro to find specific text in column and copy certain cells in same row to different Workbook

[gjiaxin1989]

Hi,

I am trying to write a macro sub that searches for a certain string in column X and copy the values in column I and K on the same row onto another workbook. Values from I will be copied to cell B15 in the new workbook, and values from K will be pasted to cell D15. The next matching values will be pasted at B16, D16, and so on.... I have read jlevesquire's thread http://www.mrexcel.com/forum/excel-...y-certain-cells-same-row-different-sheet.html. I think what I'm looking for is very similar to what he wanted. However, after editing his code to meet mine, it is not working. Especially the Offset(1) part. It says that its missing a "=". Can someone please help me? Here is my code:

Sub CommandButton1_Click()


Dim wsSource As Worksheet
Dim wbSource As Workbook
Dim wsTarget As Worksheet
Dim wbTarget As Workbook
Dim findRange As Range
Dim newForm As Workbook
Dim J As Integer


Application.ScreenUpdating = False


Set wbSource = ThisWorkbook


Set wsSource = wbSource.Sheets(1)


Set wbTarget = Workbooks.Open("C:\FilePath")
Set wsTarget = wbTarget.Sheets(1)
wsTarget.Activate


Set findRange = wsTarget.Range("A1:H11")
findRange.Replace What:="xxx-Project.No", Replacement:=wsSource.Range("s5:w5").Value, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False


ActiveWorkbook.SaveCopyAs Filename:="C:\FilePath_NEW"
ActiveWorkbook.Close False
Set newForm = Workbooks.Open("C:\FilePath_New")


J = 1
Dim setrng As Range
Set setrng = wsSource.Columns("X:X")


setrng.AutoFilter Field:=1, Criteria1:="Y"
wsSource.Range(wsSource.Range("I9").Offset(1), wsSource.Range("I9").End(xlDown)).Copy_
newForm.Range("B" & Rows.Count).End(xlUp).Offset (1)
wsSource.Range(wsSource.Range("K9").Offset(1), wsSource.Range("K9").End(xlDown)).Copy_
newForm.Range("D" & Rows.Count).End(xlUp).Offset (1)


End Sub

And can someone show me how to upload my spreadsheet? I am new to this forum... Thanks a million

Natalie

 

[abdullahsikandar]

You say last column is BV and you want copies to BX, so the code below puts a blank column between the last and the copied columns.
If you want NO blank column, then change Cells(1, Lc + 2) to Cells(1, Lc + 1).

Regards,
Howard

Option Explicit

Sub adRange()
Dim Lc As Long

Lc = Cells.Find(what:="*", After:=[A1], _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious).Column
                
 Range("A:D").Copy Cells(1, Lc + 2)
End Sub

Perfect... You are the men Thanks bro ... you solved my problem .. just a quick question.... what can i do if i want to do the sum of column D. I mean to say ....There are some sort of amount in Column D and i need it when it copies that column over there so it should do addition of all the rows and shows the result in the last column.

Like

Column D
1
1
1
1
1
1
1
7

 

[L. Howard]

Maybe something like this.
Adjust the sheet names in red text to suit your sheet name.

Regards,
Howard

Option Explicit

Sub adRangeSum()
Dim Lc As Long
Dim lr As Long
Dim uRng As Range

lr = Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Row
Sheets("Sheet2").Range("D" & lr + 1) = "Total  " & WorksheetFunction.Sum(Sheets("Sheet2").Range("D1:D" & lr))

Lc = Cells.Find(What:="*", After:=[a1], _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious).Column
                
 Range("A:D").Copy Cells(1, Lc + 2)
 
End Sub

 

[abdullahsikandar]

Thanks bro u r the best :D you solved my problem thanks a lot

 

[abdullahsikandar]

Bro thanks Do you have any idea about how to make currency calculator?

 

[L. Howard]

I don't know what a currency calculator does, so I guess the answer is no.

Howard

 

[abdullahsikandar]

I don't know what a currency calculator does, so I guess the answer is no.

Howard

It will pull up the rates from internet and convert into the currency we want

 

[L. Howard]

Sorry, don't have any idea how to make that work.

I suggest you start a new thread with that as the title and see what it might get you.

Regards,
Howard

 

[dmhotep]

Hello Howard,

Your code that you shared in #2 was very close to what I was searching for, however, it only finds the first instance of the Findstring. How would I make it continue on and find all instances and paste results to the second sheet?

I have a manifest that, based on the Findstring, I can locate a record and copy/paste the contents I need to another page, for the most part. The string is in the middle of a 10-column by 6 or 5-row "record" and sometimes the relative data is below or above the row I found the Findstring. My goal is to find the Descriptive Findstring value, then, within the first column of that result, find the pertinent cell based on another Findstring.

Describing that made sense in my head, but here is what perhaps makes more sense:

Column B holds an order # and a shop number, on different rows, of course. They don't always align with Column J's row, where I am searching for the word "Revisit." It could be in either a row above or below where "Revisit" is located. How do I find the instance of "Revisit" and then pull the order number since I don't know which row it will reside in?

Regards,
Derek