Copy only selected fields from 1 sheet to another (1 Viewer)

[dcavaiani]


VBA Code:
Public Sub CopyRows()
Sheets("Kaline").Select
' TRIED Sheets("Kaline").Range ("A7.c999, f7.f999")
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' TRIED Range ("A7:c999, f1:f999")
' TRIED .Cells.Select
Cells(x, 1).Resize(1, 33).Copy
' TRIED Cells.Copy
Sheets("sheet1").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
ActiveSheet.Paste
Sheets("kaline").Select
Next x
End Sub


 

[GPGeorge]


VBA Code:
Public Sub CopyRows()
Sheets("Kaline").Select
' TRIED Sheets("Kaline").Range ("A7.c999, f7.f999")
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' TRIED Range ("A7:c999, f1:f999")
' TRIED .Cells.Select
Cells(x, 1).Resize(1, 33).Copy
' TRIED Cells.Copy
Sheets("sheet1").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
ActiveSheet.Paste
Sheets("kaline").Select
Next x
End Sub


?

 

[dcavaiani]

?

The original copied code brings all the field for 1st sheet exactly into the 2nd sheet, but I only want a few of the fields (in the range) copied over ...

 

[GPGeorge]

The original copied code brings all the field for 1st sheet exactly into the 2nd sheet, but I only want a few of the fields (in the range) copied over ...

And perhaps even more specific details would help.

Which cells, for example, should be copied. You may get more focused help from a more focused description of the requirement.

 

[dcavaiani]

And perhaps even more specific details would help.

Which cells, for example, should be copied. You may get more focused help from a more focused description of the requirement.

Might be better in Excel forum (which I just noticed) as this is Excel VBA, if that matters? I want to copy columns 1,2,3 and 6 ONLY - from 1st to 2nd sheet - not copy the whole 1st sheet (which has Several columns) - as the copied posted code copies ALL of the many columns over.

 

[Gasman]

Why not just record a macro and look to see what that produces?
One of the benefits of Excel.

 

[dcavaiani]

Why not just record a macro and look to see what that produces?
One of the benefits of Excel.

That might work well, but I like the VBA option - assuming there is a way to paste over just the columns I want copied over ?

 

[GPGeorge]

That might work well, but I like the VBA option - assuming there is a way to paste over just the columns I want copied over ?

Recording a macro in Excel creates a VBA procedure which you can use, or refine, as appropriate.

 

[dcavaiani]

Recording a macro in Excel creates a VBA procedure which you can use, or refine, as appropriate.

Did not know that, but then again there should be a simple VBA current minor code adjustment which will work, so I don't have to go back to carefully record a macro?

 

[Gasman]

You said you wanted to copy columns?

VBA Copy / Paste Rows & Columns - Automate Excel

In this ArticleRows & Columns – Paste vs. InsertCopy & Paste Over Existing Row / ColumnCopy & Insert Row / ColumnCopy Entire RowCut and Paste RowsCopy Multiple RowsCopy Entire ColumnCut and Paste ColumnsCopy Multiple ColumnsCopy Rows or Columns to Another SheetCut Rows or Columns to Another...

www.automateexcel.com

 

[dcavaiani]

You said you wanted to copy columns?

VBA Copy / Paste Rows & Columns - Automate Excel

In this ArticleRows & Columns – Paste vs. InsertCopy & Paste Over Existing Row / ColumnCopy & Insert Row / ColumnCopy Entire RowCut and Paste RowsCopy Multiple RowsCopy Entire ColumnCut and Paste ColumnsCopy Multiple ColumnsCopy Rows or Columns to Another SheetCut Rows or Columns to Another...

www.automateexcel.com

This is incredibility simple and works! Now I need to know how to modify it and run it again with slight modifications, where I will select the "next up" column to the right of 1st sheet instead e.g.: Sheets("kaline").Range("A:C,G:G") rather than ... Range("A:C, F:F") - SO THAT the next iteration will *ADD* records to the end of the 1st iteration rather than overwriting. One last thing, can I paste in a CONSTANT VALUE to the end of the columns I select? Thank You!!! Oh, however, it did NOT copy over the actual cell Format formula from the 1st sheet cells, but rather it just copied over the cells VALUES.

 

[Gasman]

I would have thought you just need to calculate the column and starting row?

 

[dcavaiani]

I have this in 1st sheet: columns, a , b, c, d, e, F, G, H, and I want the copied sheet to break it down thusly: a, b, c, F, then ADD rows a, b, c, G, then ADD rows a, b, c, H, with the formula values of F, G, H included in the 2nd sheet.

 

[Gasman]

I would probably have a helper sheet, where I would store the source and destination. If the destination is empty, then insert at the top. If not empty, store at the next available row.

A
B
C
F
A A
B B
C C
F D
A A
B B
C C
G D
A A
B B
C C
H D

 

[dcavaiani]

I would probably have a helper sheet, where I would store the source and destination. If the destination is empty, then insert at the top. If not empty, store at the next available row.

A
B
C
F
A A
B B
C C
F D
A A
B B
C C
G D
A A
B B
C C
H D

Yikes, you know way more than I! There was some VB code posted before where it copied and added to the end of the 2nd sheet, and also pasted in the formulas, but I am pretty much lost now! ;-). The issue with that prior VB code is that I did not know how to select the RANGE of the 1st sheet columns which I wanted selected for each iteration, as it selected ALL of the 1st sheet rows over and again into the 2nd sheet.

 

[dcavaiani]

To break it down: 1st sheet contains baseball cards ROWS with a, b, c, d, e ,f, g, h columns named as: a=Team, b=Year, c=Card#, d=grade 1 value, e=grade 2 value, F=grade 3 value, G=grade 4 value, H=grade 5 value. The cards F, G, H column VALUES are garnered from FORMULAS/Averages of online Price Guides, which are found via other spreadsheet sheet formulas within the 1st sheet columns F, G, and H.

 

[Gasman]

You find the last row and set lngLastRow
Then the range is Range = ("A1:A" & lngLastRow)
You can replace the A with the value of the cell holding that information.
Note you also do not need to select to copy, and select to paste. It can be done just by references, as shown in the link I posted.

Break it down into small steps.

 

[dcavaiani]

You find the last row and set lngLastRow
Then the range is Range = ("A1:A" & lngLastRow)
You can replace the A with the value of the cell holding that information.
Note you also do not need to select to copy, and select to paste. It can be done just by references, as shown in the link I posted.

Break it down into small steps.

This would work if I could get it to add each time rather than overwriting.

Public Sub FinalCopyRows()
' Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Sheets("kaline").Range("A6:C39,f6:f39,o6:n39").Copy Sheets("sheet1").Range("A:m")
Sheets("kaline").Range("A6:C39,g6:g39,o6:n39").Copy Sheets("sheet1").Range("A:m")
Sheets("kaline").Range("A6:C39,h6:h39,o6:n39").Copy Sheets("sheet1").Range("A:m")
Sheets("kaline").Range("A6:C39,i6:i39,o6:n39").Copy Sheets("sheet1").Range("A:m")
Sheets("kaline").Range("A6:C39,j6:j39,o6:n39").Copy Sheets("sheet1").Range("A:m")
Sheets("kaline").Range("A6:C39,k6:k39,o6:n39").Copy Sheets("sheet1").Range("A:m")
Sheets("kaline").Range("A6:C39,l6:l39,o6:n39").Copy Sheets("sheet1").Range("A:m")
Sheets("kaline").Range("A6:C39,m6:m39,o6:n39").Copy Sheets("sheet1").Range("A:m")
End Sub

 

[Gasman]

You cannot do it like that, as the starting position moves.

 

[dcavaiani]

You cannot do it like that, as the starting position moves.

Guess I would calculate the next available starting end range position for each iteration, and that works!