Forum Discussion

Samarth1508's avatar
Samarth1508
Copper Contributor
Mar 03, 2023
Solved

VBA code to get column data from another worksheet.

Hi All,

 

I am using below code to get column data from another worksheet, but the below code is working for full column, and I don't want to replace my Heder data of another sheet.

 

can someone help me with the rectification in code.

 

Sub Select_Data()

Dim lr As Long

lr = Sheet1.Range("B" & Rows.Count).End(xlUp).Row

If Not IsEmpty(Sheet1.Range("B1:B" & lr).Value) Then Sheet2.Range("B1:B" & lr).Value = Sheet1.Range("B1:B" & lr).Value

End Sub

  • FikturFox's avatar
    FikturFox
    Mar 03, 2023
    That's because they don't have the same range. You have to adjust your range in Sheet2.
    ie Sheet2.range("b8:b" & lr +7)

4 Replies

  • FikturFox's avatar
    FikturFox
    Brass Contributor
    Replace "B1:B" to "B2:B" in your code.
    B1, I assume is where the header is. So move 1 row down making it B2.
    • Samarth1508's avatar
      Samarth1508
      Copper Contributor
      Tried this one also but code is transferring data to same range only.

      Means suppose I have update Sheet1.Range("B2:B") & Sheet2.Range("B2:B") then code works fine, but If I Update Sheet1.Range("B2:B") & Sheet2.Range("B8:B") then code is not transferring data to same range.
      • FikturFox's avatar
        FikturFox
        Brass Contributor
        That's because they don't have the same range. You have to adjust your range in Sheet2.
        ie Sheet2.range("b8:b" & lr +7)

Resources