copy data to another sheet

ashani

Board Regular
Joined
Mar 14, 2020
Messages
209
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm looking to copy data in the first blank row in my other spreadsheet. So this is what I'm looking for.

I would like to copy data from spreadsheet called "Source" to "Destination".

Source Sheet Cell B7 to Desitation Sheet Cell A4
Source Sheet Cell B14 to Desitation Sheet Cell B4
Source Sheet Cell D7 to Desitation Sheet Cell C4
Source Sheet Cell B10 to Desitation Sheet Cell D4

It should paste value in the first available row in the Destination sheet.

Thank you.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub ashani()
   With Sheets("Destination").Range("A" & Rows.Count).End(xlUp)
      .Offset(1, 0).Value = Sheets("Source").Range("B7").Value
      .Offset(1, 1).Value = Sheets("Source").Range("B14").Value
      .Offset(1, 2).Value = Sheets("Source").Range("D7").Value
      .Offset(1, 3).Value = Sheets("Source").Range("B10").Value
   End With
End Sub
 

ashani

Board Regular
Joined
Mar 14, 2020
Messages
209
Office Version
  1. 365
Platform
  1. Windows
Thanks @Fluff

Sorry i should have made it clear. (Source & Desitnation) are both seperate workbooks and saved on different locations.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
What are the actual workbook names & sheet names?
 

ashani

Board Regular
Joined
Mar 14, 2020
Messages
209
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The source workbook called Data Entry - Sheet name "DEA"
The destination workbook called User info - Sheet name "UIA"

The desitnatio workbook saved under C:\User\test\folder

Thank you
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
In that case try
VBA Code:
Sub ashani()
   With Workbooks("User Info.xlsx").Sheets("UIA").Range("A" & Rows.Count).End(xlUp)
      .Offset(1, 0).Value = Workbooks("Data Entry.xlsx").Sheets("DEA").Range("B7").Value
      .Offset(1, 1).Value = Workbooks("Data Entry.xlsx").Sheets("DEA").Range("B14").Value
      .Offset(1, 2).Value = Workbooks("Data Entry.xlsx").Sheets("DEA").Range("D7").Value
      .Offset(1, 3).Value = Workbooks("Data Entry.xlsx").Sheets("DEA").Range("B10").Value
   End With
End Sub
Both books will need t0 be open.
 

ashani

Board Regular
Joined
Mar 14, 2020
Messages
209
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

thanks a lot Fluff

any possiblity this can be done even when the workbook is closed and done in the background.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
Not very easily.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,070
Messages
5,704,112
Members
421,327
Latest member
Msh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top