Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open book, copy and paste from sheet, and then close....
Guys,
The VB below is run from an open book (say Master), and it opens all books within a sub-directory and then: STEP a) from the just opened workbook selects worksheet ABC and copies columns 1,2,3 into a first sheet of MASTER STEP b) from the just opened workbook selects worksheet DEF and copies ONLY column 3 into the SECOND sheet of MASTER It then closes the book and repeats, by opening next book in subdirectory. The second time around tt sucessfully achieves step A above, (and pastes the next three columns next to the previous three columns) BUT PROBLEM IS when it does Step B it leaves a gap of two columns before pasting the once column of data. I want it to paste the one column next to the previous column,not a leave a gap of 2 columns (which I think it is getting from step A) Can anyone help........????? ( i get lost with defining all the i and the i + 1) Thanks D Sub consolidator Dim i As Long, sName As String, sh As Worksheet Dim dest As Range, bk As Workbook, bk1 As Workbook Dim sh1 As Worksheet Set bk1 = ThisWorkbook i = 1 sName = Dir("D:\...\Consolidation Test Files\*.xls") Do While sName < "" Set bk = Workbooks.Open("D:\...\Consolidation Test Files\" & sName) 'first do ABC (STEP A) Set sh = bk.Worksheets("ABC") Set dest = ThisWorkbook.Worksheets(1).Cells(1, i) i = i + 3 sh.Columns(2).Resize(, 3).Copy dest.PasteSpecial xlValues dest.PasteSpecial xlFormats ' write name of the workbook in row 1 dest.Value = sName ' Then do DEF (STEP B) Set sh = bk.Worksheets("DEF") Set dest = ThisWorkbook.Worksheets(2).Cells(1, i) i = i sh.Columns(3).Copy dest.PasteSpecial xlValues dest.PasteSpecial xlFormats ' write name of the workbook in row 1 dest.Value = sName bk.Close SaveChanges:=False sName = Dir() Loop End sub *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open book, copy and paste from sheet, and then close....
I'd do it this way: Sub consolidator Dim i As Long, sName As String, sh As Worksheet Dim dest As Range, bk As Workbook, bk1 As Workbook Dim sh1 As Worksheet Set bk1 = ThisWorkbook i = 1 sName = Dir("D:\...\Consolidation Test Files\*.xls") Do While sName < "" Set bk = Workbooks.Open("D:\...\Consolidation Test Files\" & sName) 'first do ABC (STEP A) Set sh = bk.Worksheets("ABC") Set dest = ThisWorkbook.Worksheets(1).Cells(1, ThisWorkbook.Worksheets(1).usedrange.columns.count ) 'i = i + 3 sh.Columns(2).Resize(, 3).Copy dest.PasteSpecial xlValues dest.PasteSpecial xlFormats ' write name of the workbook in row 1 dest.Value = sName ' Then do DEF (STEP B) Set sh = bk.Worksheets("DEF") Set dest = ThisWorkbook.Worksheets(2).Cells(1, ThisWorkbook.Worksheets(1).usedrange.columns.count ) 'i = i sh.Columns(3).Copy dest.PasteSpecial xlValues dest.PasteSpecial xlFormats ' write name of the workbook in row 1 dest.Value = sName So actually I'd change i to the present's worksheet's usedrange.columns.count And btw i = i doesn't do anything :) let me know if it works. Morris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open book, copy and paste from sheet, and then close....
Nope - doesnt work - now only pastes one column (instead of two) and puts that one column in column DG for some strange reason?? *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open book, copy and paste from sheet, and then close....
Darin Kramer wrote: Nope - doesnt work - now only pastes one column (instead of two) and puts that one column in column DG for some strange reason?? *** Sent via Developersdex http://www.developersdex.com *** Then create a seconder counter - j as integer and do step B like this: Then do DEF (STEP B) Set sh = bk.Worksheets("DEF") Set dest = ThisWorkbook.Worksheets(2).Cells(1, j) j = j +1 sh.Columns(3).Copy dest.PasteSpecial xlValues dest.PasteSpecial xlFormats ' write name of the workbook in row 1 dest.Value = sName dont forget to assign a start value for j on top of the code. j = 1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open book, copy and paste from sheet, and then close....
I get erro message duplicate declaraion in current scope - im no expert - I said Dim j as integer...? *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open book, copy and paste from sheet, and then close....
Darin Kramer wrote: I get erro message duplicate declaraion in current scope - im no expert - I said Dim j as integer...? *** Sent via Developersdex http://www.developersdex.com *** check the code for double declaration of: dim j as integer maybe you put it once on top of the Sub and second tim e somewhere in the step B text block? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy and paste to different work book | Excel Programming | |||
Create new book..paste data..save..close | Excel Programming | |||
Copy ata from several workbooks and paste into new work book | Excel Programming | |||
Open book, check for macros, close book | Excel Programming | |||
Copy and paste ranges from a close book using Validation | Excel Programming |