The second macro worked as you had indicated, thank you. Is there a way to
indicate all columns I would like to paste at once and then select the
worksheet/column I would like the slected columns to go. This macro, although
very useful, could be very time consuming for the number of columns I may
have to move, if selecting a column at a time.

"JLGWhiz" wrote:

Hi, Jennifer. I realized that you might have a problem with the first code I
posted, so there is a second one there for you to use instead. Not in
addition to, but in place of the first one I posted.

It tested OK before posting. Remember to type a single column letter in the
first input box and then for the second input box, use the mouse to select
the sheet and the starting cell for where you want the copied column to paste.

"Jennifer" wrote:

Any ideas on this one ?

"Jennifer" wrote:

Thank you for the code, I am receiving an error on either code at the pint
where it indicates:
Worksheets("newSheet").Range (destCol)
With Nadias posting it did pull over one column, just trying to determining
how I list each column range in her macro ? Is it a separate line of code or
all in line of code ?

Example: Where it indicates ActiveSheet.Range("a1:ce200").Select 'cell
ranges , how would I list each column ?

Sub copycells()
ActiveWorkbook.Sheets("sheet1").Select 'source sheet
ActiveSheet.Range("a1:ce200").Select 'cell ranges
With Selection
.Copy
End With
ActiveWorkbook.Sheets("sheet2").Select 'output sheet
ActiveSheet.Range("a1").Select 'cell where the output should begin
With Selection
.PasteSpecial (xlValues) 'copies the values
.PasteSpecial (xlFormats) 'copies the formatting
End With
End Sub


"JLGWhiz" wrote:

This might work better in your circumstance. It measures each column length
rather than using column A length for all all copy ranges.

Sub pullSpecificCol()
Dim destCol As Range

REPEAT:
myCol = InputBox("Enter a Column letter. Must be Alpha character", _
"Column to Copy")
lastRow = ActiveSheet.Cells(Rows.Count, myCol).End(xlUp).Row
Set destCol = Application.InputBox("Select a cell for the column to
start", _
"Column to Paste", Type:=8)
ActiveSheet.Range(myCol & 1 & ":" & myCol & lastRow).Copy destCol
CutCopyMode = False
more = MsgBox("Is there another column to copy?", vbYesNo, "CONTINUE?")
If more = vbYes Then
GoTo REPEAT:
End If
End Sub

"Jennifer" wrote:

Thank You Nadia,
Can you explain what I would indicate in the macro, which columns I would
pull into sheet2?

" wrote:

On Apr 29, 4:17 pm, Jennifer
wrote:
Is there a way to pull specific columns that will vary in number of rows to a
new worksheet ? Sometimes the columns needed will change.
Currently I have one worksheet that has 59 columns and will only need. lets
say, 15 of them. Instead of manually removing and moving in the order needed
each time I run a report, I was wondering if there is a Macro I can use ?

Hi, you can use this but make sure you change the column ranges to
suit your spreadsheets:

Sub copycells()
ActiveWorkbook.Sheets("sheet1").Select 'source sheet
ActiveSheet.Range("a1:ce200").Select 'cell ranges
With Selection
.Copy
End With
ActiveWorkbook.Sheets("sheet2").Select 'output sheet
ActiveSheet.Range("a1").Select 'cell where the output should begin
With Selection
.PasteSpecial (xlValues) 'copies the values
.PasteSpecial (xlFormats) 'copies the formatting
End With
End Sub

Nadia