Macro that copies content of a cell

[FrankB]

Hi,

I have a list of items and a big file. I want to have a macro that extracts the relevant lines from the big file by going through the list of items. How do I instruct Excel to copy the content of a cell without hardcoding the actual content?

Is there a better way of solving this issue?

Thanks,

FrankB

 

[Juan Pablo González]

Frank, you can get the proper syntax by recording a macro while you copy (And then paste) a cell. If you have any doubts please don't hesitate to post back.

 

[Joe Was]

You may want to try something like this, the code below will copy rows from one sheet to another, if a row is flaged with a "X" in column "A." You can use any flag or indicator you want and you can change what is copied to where? JSW

Sub Priority()
'Find all the rows ("A:G") that have a "X" in column "A" copy
'that row to the next blank row on a different sheet.

Application.ScreenUpdating = False
Worksheets("Want_Full").Select
For Each r In Worksheets("Want_Full").UsedRange.Rows
n = r.Row
If Worksheets("Want_Full").Cells(n, 1) = "X" Then
Worksheets("Want_Full").Range(Cells(n, 2), Cells(n, 7)).Copy Destination:=Worksheets("Want_Now").Range("B65536").End(xlUp).Offset(1, -1)
Else
End If
Next r

Worksheets("Want_Full").Columns("A").Replace What:="X", Replacement:="*", SearchOrder:=xlByColumns, MatchCase:=True

ActiveWindow.ScrollRow = 22
ActiveWindow.SmallScroll Down:=19
Range("Vendor").Select
Selection.Copy

Sheets("Want_Full").Select
ActiveWindow.ScrollRow = 1
Range("A1").Select

Sheets("Want_Now").Select
Range("B65536").End(xlUp).Offset(2, -1).Select
ActiveSheet.Paste
ActiveSheet.Range("A1").Select
Application.ScreenUpdating = True

End Sub

Sub CopySlect()

'Copy current selection, cell or range.
Application.ScreenUpdating = False
Worksheets("Sheet1").Select

'Paste Sheet1 data to Sheet2, starting in column B down, add new data to bottom of list.
Selection.Copy Destination:=Worksheets("Sheet2").Range("B65536").End(xlUp).Offset(1, 0)

'Delete current selection.
Application.CutCopyMode = False
Selection.ClearContents
Application.ScreenUpdating = True

End Sub

 

[FrankB]

Joe Was,

Thanks so much, that macro is very helpful!

FrankB

 

[dennisdjones]

Hi Joe Was

Tried your code out, but can only get to work if I view code and run F5 from there. It does display a Run-time error - 1004
Application defined or object defined error.

Appreciate the help.

Dennis

 

[Joe Was]

I suspect that you failed to change one of the worksheets names to one of yours, the code works in several of my applications and it has worked for others on this site. Did you put the code in a Module?
Did you change the ranges to yours?
Did you remove the extra code that does not apply in your case?

 

[Joe Was]

Try this more generic version:

Sub Priority()
'Find all the rows ("A:G") that have a "X" in column "A" copy
'that row to the next blank row on a different sheet.

Application.ScreenUpdating = False
Worksheets("Sheet1").Select
For Each r In Worksheets("Sheet1").UsedRange.Rows
n = r.Row
If Worksheets("Sheet1").Cells(n, 1) = "X" Then
Worksheets("Sheet1").Range(Cells(n, 2), Cells(n, 7)).Copy Destination:=Worksheets("Sheet2").Range("B65536").End(xlUp).Offset(1, -1)
Else
End If
Next r

Worksheets("Sheet1").Columns("A").Replace What:="X", Replacement:="*", SearchOrder:=xlByColumns, MatchCase:=True

Sheets("Sheet1").Select
ActiveWindow.ScrollRow = 1
Range("A1").Select

Sheets("Sheet2").Select
Range("B65536").End(xlUp).Offset(2, -1).Select
ActiveSheet.Paste
ActiveSheet.Range("A1").Select
Application.ScreenUpdating = True

End Sub

 

[dennisdjones]

Changed the worksheet names, but do not know exactly what the Module is? It works when I go to view code and run it from there but it comes up with that error.

 

[lipin]

Can this code be altered to only copy cells that are linked, and then paste them to the same cells on a new sheet. I don't need the cells that aren't linked.

 

[dennisdjones]

Joe it still comes up with the Error (it works though) just trying to get rid of the error

When pasted code in module it is titled (General) and Priority. Is this correct? Maybe I haven't got it pasted in correctly.
It is in SHEET1 (NOT THE WORKBOOK)

Code placed in 1st worksheet labeled- SHEET1
Second Worksheet labeled- SHEET2

Understand A1 represents the entire spreadsheet, I did not type A1 in a cell though.

Thanks
Dennis