Copy columns, but not hidden ones

[Duncs]

On my spreadsheet I have calculations and formatting of fields etc.--many thanks Claus for the assistance.

what I have is:

A B C D E F G H
010203NN 27.50 020203YY 56.75 525251NY 175.2 101020YN 1.75

The values in columns B, D, F & H are obtained from a VLOOKUP on another table.

What I want to do is...

Total the values in the numeric columns and store them in another cell..easy, done that.

However, what I also want to do is copy the data that is in the other cells, and paste the values into another spreadsheet. The problem I am having is that the copy / paste, is pasting in the values that are stored in B, D, F & H as well. I am then having to move each column left by one to get ridof the values.

I have thought of storing the value elsewhere, but can't think of somewhereto put it, without adding to the size of the file...it is already fairly large. Another thought I had was to add the values in the total cell, by simply inserting the VLOOKUP's there...however, there are 50 of them!

Suggestions, please.

Many thanks

Duncs

 

[Claus Busch]

Hi Duncan,

Am Fri, 14 Dec 2012 02:19:18 -0800 (PST) schrieb Duncs:

A B C D E F G H
010203NN 27.50 020203YY 56.75 525251NY 175.2 101020YN 1.75

However, what I also want to do is copy the data that is in the other cells, and paste the values into another spreadsheet. The problem I am having is that the copy / paste, is pasting in the values that are stored in B, D, F & H as well. I am then having to move each column left by one to get rid of the values.

try with a macro (modify to suit):

Sub copy()
Dim i As Integer
Dim j As Integer

For i = 1 To 7 Step 2
With Sheets("Sheet1")
j = j + 1
.Columns(i).copy _
Destination:=Sheets("Sheet2").Cells(1, j)
End With
Next
End Sub


Regards
Claus Busch

 

[Duncs]

Cheers Claus, once again.

I've put it into the spreadsheet and modified accordingly. I perhaps didn't explain fully in my original post and for that, I am sorry. I should have added that I won't be copying the entire column. Instead, I will only becopying a section of the text...e.g. the range A1...H1. Then, the next time with may be A10...H19.

The size of the area I am copying will vary each time I want to copy. I'velooked at selecting the required range, identifying this range, and copying the data from within this area. I then need to copy the values to a different location each time...e.g. the first paste may be at A1. The next may be at A17, then A23 etc.

I think the selected range method may be the best but as I say, I can't getthe method for it.

Can you assist, once again?

Duncs

 

[Claus Busch]

hi Duncan,

Am Fri, 14 Dec 2012 04:14:12 -0800 (PST) schrieb Duncs:

I've put it into the spreadsheet and modified accordingly. I perhaps didn't explain fully in my original post and for that, I am sorry. I should have added that I won't be copying the entire column. Instead, I will only be copying a section of the text...e.g. the range A1...H1. Then, the next time with may be A10...H19.

The size of the area I am copying will vary each time I want to copy. I've looked at selecting the required range, identifying this range, and copying the data from within this area. I then need to copy the values to a different location each time...e.g. the first paste may be at A1. The next may be at A17, then A23 etc.

I think the selected range method may be the best but as I say, I can't get the method for it.

if the copied ranges and the paste locations are so different, you can't
do it automatic. If the paste location is the same range as the copied
range but on another sheet, you could do it with a macro.


Regards
Claus Busch