Macro Needed to pull specific columns from worksheet

[Jennifer]

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 ?

 

[nadia.younus]

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

 

[Jennifer]

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

 

[JLGWhiz]

The following code goes into the standard code moduel. Press Alt + F11 to
open the VB editor. If you have not previously activated the code module,
use Insert>Module from the menu then paste this code into the window.

Sub pullSpecificCol()

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

This code will display two input boxes. The first one you type a column
letter into. The second one, use your mouse or keyboard controls to select
the destination sheet and a cell on the sheet where you want the copied
column to begin. Since you implied that the columns to be copied might not
be contiguous, this code only allows one column at a time to be copied, but
will continue until you click No when asked if you want to continue. Better
explanations of the parameters yields better solutions.

 

[JLGWhiz]

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]

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

 

[Jennifer]

Any ideas on this one ?

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]

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]

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.