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 ?

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

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

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 InsertModule 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.

"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

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

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

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