Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to copy range vs Copy Entire Worksheet - can't figure it out
The following code copies entire worksheets. Can someone explain how
the code needs to be modified so that only cell values and formats are copied from the same range of cells ("A1:P33")in each worksheet rather than copying the entire worksheet. I visited Ron's site, http://www.rondebruin.nl/copy1.htm, and tried to figure out how to use his examples without success...I just can't seem to figure it out. Any help is greatly appreciated. Mike Taylor __________________________________________________ ___________________________ Sub GetRRRDheets() Dim i As Long Dim varr As Variant Dim wkbk As Workbook Dim sh As Object Dim mybook As Workbook Dim myExistingPath As String Dim myPathToRetrieve As String myExistingPath = CurDir 'myPathToRetrieve = "c:\data\datafiles\data" myPathToRetrieve = "v:\" ChDrive myPathToRetrieve ChDir myPathToRetrieve varr = Application.GetOpenFilename(filefilter:="Excel Files, *.xls", _ MultiSelect:=True) If IsArray(varr) Then For i = LBound(varr) To UBound(varr) Set wkbk = Workbooks.Open(varr(i)) With wkbk.Worksheets("Remittance Report 2004") On Error Resume Next Set mybook = Workbooks.Open(.FoundFiles(i)) For Each sh In mybook.Sheets ActiveWorkbook.Unprotect ("mbt") ActiveSheet.Unprotect ("mbt") Next sh mybook.Close SaveChanges:=False .UsedRange.Value = .UsedRange.Value .Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksh eets.Count) ActiveSheet.Name = Format(Range("d2").Value, "000") End With wkbk.Close SaveChanges:=False Next End If 'reset it back ChDrive myExistingPath ChDir myExistingPath End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to copy range vs Copy Entire Worksheet - can't figure it out
Hi mike
You mean this page I think?? http://www.rondebruin.nl/copy3.htm Try the first example -- Regards Ron de Bruin http://www.rondebruin.nl "Mike Taylor" wrote in message om... The following code copies entire worksheets. Can someone explain how the code needs to be modified so that only cell values and formats are copied from the same range of cells ("A1:P33")in each worksheet rather than copying the entire worksheet. I visited Ron's site, http://www.rondebruin.nl/copy1.htm, and tried to figure out how to use his examples without success...I just can't seem to figure it out. Any help is greatly appreciated. Mike Taylor __________________________________________________ ___________________________ Sub GetRRRDheets() Dim i As Long Dim varr As Variant Dim wkbk As Workbook Dim sh As Object Dim mybook As Workbook Dim myExistingPath As String Dim myPathToRetrieve As String myExistingPath = CurDir 'myPathToRetrieve = "c:\data\datafiles\data" myPathToRetrieve = "v:\" ChDrive myPathToRetrieve ChDir myPathToRetrieve varr = Application.GetOpenFilename(filefilter:="Excel Files, *.xls", _ MultiSelect:=True) If IsArray(varr) Then For i = LBound(varr) To UBound(varr) Set wkbk = Workbooks.Open(varr(i)) With wkbk.Worksheets("Remittance Report 2004") On Error Resume Next Set mybook = Workbooks.Open(.FoundFiles(i)) For Each sh In mybook.Sheets ActiveWorkbook.Unprotect ("mbt") ActiveSheet.Unprotect ("mbt") Next sh mybook.Close SaveChanges:=False .UsedRange.Value = .UsedRange.Value .Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksh eets.Count) ActiveSheet.Name = Format(Range("d2").Value, "000") End With wkbk.Close SaveChanges:=False Next End If 'reset it back ChDrive myExistingPath ChDir myExistingPath End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy entire row to different worksheet | Excel Worksheet Functions | |||
Copy automatically figure to another worksheet | Excel Discussion (Misc queries) | |||
Is there a function to copy an entire worksheet? | Excel Worksheet Functions | |||
Copy entire Worksheet how? | Excel Programming | |||
Copy information from a specific range; not the entire worksheet. | Excel Programming |