PLS HELP How can I fill the columns automatically from another excel sheet?

I don't wanna lose my time with copy-paste. I need to fill an excel sheet automatically from another one but the columns are different.

As you see the photos below, I need to fill the columns in first sheet with the same colour in second sheet. I used colours just to understand easy normally there are no colur in columns.

excel

Formulas and Functions

Help with Multiple Sheets

office 365

Philip West
Aug 03, 2018
Sub copy_paste()
Dim r As Integer
r = ThisWorkbook.Sheets("Sheet1").Range("A" & ThisWorkbook.Sheets("Sheet1").Rows.Count).End(xlUp).Row   'get the size of the data
    '=======================COPY/PASTE DATA INTO THE TABLE==========================
    ThisWorkbook.Sheets("Sheet1").Range("C2" & ":" & "C" & r).Copy
    ThisWorkbook.Sheets("Satirlar").Range("E2").PasteSpecial Paste:=xlPasteValues

    ThisWorkbook.Sheets("Sheet1").Range("H2" & ":" & "H" & r).Copy
    ThisWorkbook.Sheets("Satirlar").Range("A2").PasteSpecial Paste:=xlPasteValues

    ThisWorkbook.Sheets("Sheet1").Range("I2" & ":" & "I" & r).Copy
    ThisWorkbook.Sheets("Satirlar").Range("C2").PasteSpecial Paste:=xlPasteValues

    ThisWorkbook.Sheets("Sheet1").Range("J2" & ":" & "J" & r).Copy
    ThisWorkbook.Sheets("Satirlar").Range("D2").PasteSpecial Paste:=xlPasteValues

End Sub

This would do what you want I guess, there are probably neater ways, but if you just want to move everything over in one go then this will get the job done.

3 Replies

Philip West
Iron Contributor
Aug 03, 2018
Sub copy_paste()
Dim r As Integer
r = ThisWorkbook.Sheets("Sheet1").Range("A" & ThisWorkbook.Sheets("Sheet1").Rows.Count).End(xlUp).Row   'get the size of the data
    '=======================COPY/PASTE DATA INTO THE TABLE==========================
    ThisWorkbook.Sheets("Sheet1").Range("C2" & ":" & "C" & r).Copy
    ThisWorkbook.Sheets("Satirlar").Range("E2").PasteSpecial Paste:=xlPasteValues

    ThisWorkbook.Sheets("Sheet1").Range("H2" & ":" & "H" & r).Copy
    ThisWorkbook.Sheets("Satirlar").Range("A2").PasteSpecial Paste:=xlPasteValues

    ThisWorkbook.Sheets("Sheet1").Range("I2" & ":" & "I" & r).Copy
    ThisWorkbook.Sheets("Satirlar").Range("C2").PasteSpecial Paste:=xlPasteValues

    ThisWorkbook.Sheets("Sheet1").Range("J2" & ":" & "J" & r).Copy
    ThisWorkbook.Sheets("Satirlar").Range("D2").PasteSpecial Paste:=xlPasteValues

End Sub

This would do what you want I guess, there are probably neater ways, but if you just want to move everything over in one go then this will get the job done.
- Anıl Adaş
  Brass Contributor
  Aug 03, 2018
  Actually I am not so familiar with the macros, I thought there is gonna be a basic method using formulas.
  
  I tried the apply this macro but I recieved an error as ''subscript out of range''
  - Philip West
    Iron Contributor
    Aug 03, 2018
    Just check the name of the sheets matches the names in the macro.. I might have misspelt it.

Forum Discussion

PLS HELP How can I fill the columns automatically from another excel sheet?

3 Replies

Resources