Macro that copies content of a cell

FrankB

New Member
Joined
Sep 3, 2002
Messages
31
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

dennisdjones

New Member
Joined
Apr 13, 2002
Messages
19

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

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

New Member
Joined
Apr 13, 2002
Messages
19
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

New Member
Joined
Jul 10, 2002
Messages
27
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

New Member
Joined
Apr 13, 2002
Messages
19
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
 

Forum statistics

Threads
1,148,284
Messages
5,745,846
Members
423,981
Latest member
ph1l

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
Top