PDA

View Full Version : Copy values from another workbook



ForeverLearn
04-18-2012, 01:30 AM
Hi,

I want to copy values only from one workbook to another and some cells being copied are merged. I would like to copy only values to another workbook but end up with a error.
I have used

Dim wbSource as workbooks
Dim wbDestination as workbooks

wbSource.Worksheets("Sheetname").Usedrange.Copy wbDestination.Worksheets("SheetName").Range("A1").Paste xlPasteSpecial

Thanks

BrianMH
04-18-2012, 05:21 AM
You are not assigning any objects. Is this your whole code?
Also you have used workbooks instead of workbook (a collection instead of a single object).
Are both workbooks open?
Are you just trying to copy a single range?

Basically let us know specifically what you need and we can likely help.

ForeverLearn
04-18-2012, 11:25 PM
HI Brian,

I just whipped up the code of the top of my head how I did the copy worksheet from another book to another.

But I have a issue when copying, when source sheet has merged cells and destination has exact duplicate sheet but when copying values using Paste special values causes error or anything but pasteAll but that gives me formulas.

What is the correct way when copying from one sheet to another where both has merged cells

Thanks

BrianMH
04-18-2012, 11:55 PM
Can you post an example of your data and the spreadsheet you want the data to copy to. Are you looking to just copy a whole sheet to another workbook?

CatDaddy
04-19-2012, 11:11 AM
Dim wbSource As workbook
Dim wbDestination As workbook

set wbSource = Workbooks("someworkbookname.xlsx")
set wbDestination = Workbooks("someotherworkbookname.xlsx")

wbSource.Worksheets("Sheetname").Usedrange.Copy destination:=wbDestination.Worksheets("SheetName").Range("A1")

ForeverLearn
04-19-2012, 03:08 PM
Hi,

I have actually set to import 2 worksheets
What I have used is below

'Set Import Sheet1 WPS
strPath = Worksheets("Data").Range("PATH_WPS")
strFileName = Worksheets("Data").Range("WPS_GL1")
strType = Worksheets("Data").Range("WPS_GL_EXT") ' for Macro enabled 2007 format

'set sheet name to import
sSheet1 = Worksheets("Data").Range("WPS_GL1").Value
sSheet2 = Worksheets("Data").Range("WPS_GL2").Value


'Get sheet names to import and copy to correct location
Set wbSource = Workbooks.Open(Filename:=strPath & strFileName & strType, ReadOnly:=True)

wbSource.Sheets(sSheet1).UsedRange.Copy
wbCurrent.Sheets("WPS_GL1 WK1").Range("A1").PasteSpecial Paste:=xlPasteAll

wbSource.Sheets(sSheet2).UsedRange.Copy
wbCurrent.Sheets("WPS_GL2 WK2").Range("A1").PasteSpecial Paste:=xlPasteAll

This will import everything ok, but I only want values and no formula
if I use xlPasteValues or anything but xlPasteall I get and error

Thanks