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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
I didn't notice that you were copying to row 3, so you need to add 1 to lastrow
VBA Code:
.Range("A2").AutoFill .Range("A2:A" & LastRow+1)
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
182
Office Version
  1. 365
Platform
  1. Windows
I didn't notice that you were copying to row 3, so you need to add 1 to lastrow
VBA Code:
.Range("A2").AutoFill .Range("A2:A" & LastRow+1)
I am not sure that count to 3 is correct, it was part of a marco I copied.
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
250
Office Version
  1. 2007
Platform
  1. Windows
One more try.

VBA Code:
Dim LastRow As Long
   
'optional - disable screen flickering
    Application.ScreenUpdating = False
   
' be carefull about selecting "LastRow" column
' if you pooling data from column "H" it is column 8
    LastRow = Cells(Rows.Count, 8).End(xlUp).Row
    Sheets("END_NO CHANGE LIST").Range("H2:H" & LastRow).Copy
    Sheets("END Operand Mode 1 Deliv&Supply").Range("B3").PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'but if you pooling data from column "I" it is column 9
    LastRow = Cells(Rows.Count, 8).End(xlUp).Row
    Sheets("END_NO CHANGE LIST").Range("I2:I" & LastRow).Copy
    Sheets("END Operand Mode 1 Deliv&Supply").Range("C3").PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
' optional
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
 

fvisions

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

ADVERTISEMENT

Sorry, but I don't understand what you are saying.
I don't know fully how this code works, I don't know what the #3 represents, if you want to break it down for me to educate me I would be grateful; LastRow = Cells(Rows.Count, 3).End(xlUp).Row

And thank you for fixing my code it works now.

I am getting ready to copy this code for another (two different worksheets in the same workbook) but now I need to set it up to either use the different criteria in a column or to some how only copy the data until the blank three rows. The worksheet as for an example 10 rows of information with header then 3 blank rows then a new set of data below without headers but is the same as the data above the 3 blank rows with the only difference in the column criteria.

The first set of data is by rate code MUSL the rest below is SOPSS. Not sure if this makes since. Do you want me to post a new thread for this?
Sub Mode2Delivery()
'
' Copy Selected columns to script ADD & END Operand Mode 2 Delivery tab


VBA Code:
Dim LastRow As Long
LastRow = Sheets("Installation wkg").Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False

With Sheets("ADD & END Operand Mode2 Delivry")
Sheets("Installation wkg").Range("H2:H" & LastRow).Copy Destination:=.Range("B3")
Sheets("Installation wkg").Range("I2:I" & LastRow).Copy Destination:=.Range("C3")
Sheets("Installation wkg").Range("K2:K" & LastRow).Copy Destination:=.Range("D3")
Sheets("Installation wkg").Range("W2:W" & LastRow).Copy Destination:=.Range("I3")
Sheets("Installation wkg").Range("AA2:AA" & LastRow).Copy Destination:=.Range("M3")
Sheets("Installation wkg").Range("X2:X" & LastRow).Copy Destination:=.Range("J3")

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
The 3 on this line
VBA Code:
LastRow = Cells(Rows.Count, 3).End(xlUp).Row
means col C
 

fvisions

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

ADVERTISEMENT

The 3 on this line
VBA Code:
LastRow = Cells(Rows.Count, 3).End(xlUp).Row
means col C
oh ok so the process is to copy until the end of column C....right? Thank you as always Fluff you helped me and educated me. Thank you !!!
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
250
Office Version
  1. 2007
Platform
  1. Windows
Sorry, This part of code is wrong

'but if you pooling data from column "I" it is column 9
VBA Code:
 LastRow = Cells(Rows.Count, 8).End(xlUp).Row,

It need to be
VBA Code:
 LastRow = Cells(Rows.Count, 9).End(xlUp).Row
 

Watch MrExcel Video

Forum statistics

Threads
1,119,004
Messages
5,575,519
Members
412,672
Latest member
Tupelo1984
Top