How to copy columns with headers from one worksheet to another in same workbook

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
182
Office Version
  1. 365
Platform
  1. Windows
I created a macro to copy from one worksheet to another in the same workbook and attempted to have the process repeated for each column needed. Both worksheets have headers but not the same ones. For instance I want to copy col H Installation Number starting at H2 to end of data, paste values to another worksheet but put in B3. This worked so I copied the marco and changed to column I but the results are not the same. The first screen shot is what I am try to copy, the second is the results.

The column headers in both worksheets will not change but the information will as well as the number of rows.

1605025914585.png
1605025926364.png

VBA Code:
Dim LastRow As Long
   LastRow = Cells(Rows.Count, 3).End(xlUp).Row

    Sheets("END_NO CHANGE LIST").Select
range("H2:H" & LastRow).Select
Selection.Copy
Sheets("END Operand Mode 1 Deliv&Supply").Select
range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

LastRow = Cells(Rows.Count, 3).End(xlUp).Row
Sheets("END_NO CHANGE LIST").Select
range("i2:i" & LastRow).Select
Selection.Copy
Sheets("END Operand Mode 1 Deliv&Supply").Select
range("c3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
250
Office Version
  1. 2007
Platform
  1. Windows
You trying to get rows count from wrong column.
Try this solution.
 

Attachments

  • Capture.JPG
    Capture.JPG
    51.4 KB · Views: 12

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
@EXCEL MAX
Could you please post your code to the board, rather than posting an image.
It's far more helpful. :)
 

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
182
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry, my mistake.
I am trying to copy the column from below header to end of data and then paste to row 3 in another column
 

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
182
Office Version
  1. 365
Platform
  1. Windows
@EXCEL MAX
Could you please post your code to the board, rather than posting an image.
It's far more helpful. :)

I tried another approach and it worked the first time but when I tried it again with a different number of rows in the worksheet I am copying from it didn't work. I know I am defiantly missing something but I don't know what.

VBA Code:
Sub Mode1DelivSupply()
'
' Copy Selected columns to script Delivery& Suppply Mode 1 tab

Dim LastRow As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Application.CutCopyMode = False
Application.ScreenUpdating = True

Sheets("END_NO CHANGE LIST").Range("H2:H" & LastRow).Copy Destination:=Sheets("END Operand Mode 1 Deliv&Supply").Range("B3")
Sheets("END_NO CHANGE LIST").Range("I2:I" & LastRow).Copy Destination:=Sheets("END Operand Mode 1 Deliv&Supply").Range("C3")
Sheets("END_NO CHANGE LIST").Range("K2:K" & LastRow).Copy Destination:=Sheets("END Operand Mode 1 Deliv&Supply").Range("D3")
Application.CutCopyMode = False
Application.ScreenUpdating = True

Columns("E:E").Select
Selection.NumberFormat = "mm/dd/yyyy"

Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & LastRow)
Range("A2:A" & LastRow).Select
Range("A2").Select

End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Which sheet is this part of your code meant to work on?
VBA Code:
Columns("E:E").Select
Selection.NumberFormat = "mm/dd/yyyy"

Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & LastRow)
Range("A2:A" & LastRow).Select
Range("A2").Select
 

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
182
Office Version
  1. 365
Platform
  1. Windows
Which sheet is this part of your code meant to work on?
VBA Code:
Columns("E:E").Select
Selection.NumberFormat = "mm/dd/yyyy"

Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & LastRow)
Range("A2:A" & LastRow).Select
Range("A2").Select
the target worksheet "END Operand Mode 1 Deliv&Supply"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub Mode1DelivSupply()
'
' Copy Selected columns to script Delivery& Suppply Mode 1 tab

Dim LastRow As Long
LastRow = Sheets("END_NO CHANGE LIST").Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False

With Sheets("END Operand Mode 1 Deliv&Supply")
   Sheets("END_NO CHANGE LIST").Range("H2:H" & LastRow).Copy Destination:=.Range("B3")
   Sheets("END_NO CHANGE LIST").Range("I2:I" & LastRow).Copy Destination:=.Range("C3")
   Sheets("END_NO CHANGE LIST").Range("K2:K" & LastRow).Copy Destination:=.Range("D3")

   .Columns("E:E").NumberFormat = "mm/dd/yyyy"
   .Range("A2").AutoFill .Range("A2:A" & LastRow)
End With
End Sub
 

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
182
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub Mode1DelivSupply()
'
' Copy Selected columns to script Delivery& Suppply Mode 1 tab

Dim LastRow As Long
LastRow = Sheets("END_NO CHANGE LIST").Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False

With Sheets("END Operand Mode 1 Deliv&Supply")
   Sheets("END_NO CHANGE LIST").Range("H2:H" & LastRow).Copy Destination:=.Range("B3")
   Sheets("END_NO CHANGE LIST").Range("I2:I" & LastRow).Copy Destination:=.Range("C3")
   Sheets("END_NO CHANGE LIST").Range("K2:K" & LastRow).Copy Destination:=.Range("D3")

   .Columns("E:E").NumberFormat = "mm/dd/yyyy"
   .Range("A2").AutoFill .Range("A2:A" & LastRow)
End With
End Sub
It works with one exception, the autofill is only going to the second to last row not the last row...odd?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,011
Messages
5,575,539
Members
412,674
Latest member
emeisee
Top