Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorry...I'm a terrible idiot and still can't do anything...:-(((
I'm sorry and feel like an idiot...still can't obtain what I'm trying to
do...:-((( I'm such a newbie with macro & excel... Trying to explain what I need...: In a new workboook I need to copy data in columns (I:J) from 4 different sheets and Every exixsting workbook has 4 sheets called "alfa", "beta", "gamma", "delta". I need to copy (in a new sheet of a new workbook) columns I3:J203 of every sheet for every existing workbook So in the new sheet, I've got From Workbook1 I:J columns from "alfa" in A3:B203 I:J columns from "beta" in D3:E203 I:J columns from "gamma" in G3:H203 I:J columns from "delta" in J3:K203 From workboook2 I:J columns from "alfa" in M3:N203 I:J columns from "beta" in P3:Q203 I:J columns from "gamma" in S3:T203 I:J columns from "delta" in V3:W203 ....and so on And, maybe I wish I could put in cell A1,M1... the names of the workbooks and in A2,B2,C2,D2....the names of the sheets.... really hope you can help me... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorry...I'm a terrible idiot and still can't do anything...:-(((
Here is a example
For all files in C:\Data Copy this macro in a workbook outside that folder and run it Sub Tester() Dim basebook As Workbook Dim mybook As Workbook Dim sourceRange As Range Dim destrange As Range Dim Colnum As Long Dim SourceCcount As Long Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String Dim sh As Worksheet SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Set basebook = ThisWorkbook basebook.Worksheets(1).Cells.Clear 'clear all cells on the first sheet Colnum = 1 Do While FNames < "" Set mybook = Workbooks.Open(FNames) For Each sh In mybook.Sheets(Array("alfa", "beta", "gamma", "delta")) Set sourceRange = sh.Range("I3:J203") SourceCcount = sourceRange.Columns.Count Set destrange = basebook.Worksheets(1).Cells(3, Colnum) sourceRange.Copy destrange basebook.Worksheets(1).Cells(1, Colnum).Value = mybook.Name Colnum = Colnum + SourceCcount Next sh mybook.Close False FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "uriel78" wrote in message ... I'm sorry and feel like an idiot...still can't obtain what I'm trying to do...:-((( I'm such a newbie with macro & excel... Trying to explain what I need...: In a new workboook I need to copy data in columns (I:J) from 4 different sheets and Every exixsting workbook has 4 sheets called "alfa", "beta", "gamma", "delta". I need to copy (in a new sheet of a new workbook) columns I3:J203 of every sheet for every existing workbook So in the new sheet, I've got From Workbook1 I:J columns from "alfa" in A3:B203 I:J columns from "beta" in D3:E203 I:J columns from "gamma" in G3:H203 I:J columns from "delta" in J3:K203 From workboook2 I:J columns from "alfa" in M3:N203 I:J columns from "beta" in P3:Q203 I:J columns from "gamma" in S3:T203 I:J columns from "delta" in V3:W203 ...and so on And, maybe I wish I could put in cell A1,M1... the names of the workbooks and in A2,B2,C2,D2....the names of the sheets.... really hope you can help me... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorry...I'm a terrible idiot and still can't do anything...:-(((
Oops, Change this
This line basebook.Worksheets(1).Cells(1, Colnum).Value = mybook.Name must be below this line Set mybook = Workbooks.Open(FNames) -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Here is a example For all files in C:\Data Copy this macro in a workbook outside that folder and run it Sub Tester() Dim basebook As Workbook Dim mybook As Workbook Dim sourceRange As Range Dim destrange As Range Dim Colnum As Long Dim SourceCcount As Long Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String Dim sh As Worksheet SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Set basebook = ThisWorkbook basebook.Worksheets(1).Cells.Clear 'clear all cells on the first sheet Colnum = 1 Do While FNames < "" Set mybook = Workbooks.Open(FNames) For Each sh In mybook.Sheets(Array("alfa", "beta", "gamma", "delta")) Set sourceRange = sh.Range("I3:J203") SourceCcount = sourceRange.Columns.Count Set destrange = basebook.Worksheets(1).Cells(3, Colnum) sourceRange.Copy destrange basebook.Worksheets(1).Cells(1, Colnum).Value = mybook.Name Colnum = Colnum + SourceCcount Next sh mybook.Close False FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "uriel78" wrote in message ... I'm sorry and feel like an idiot...still can't obtain what I'm trying to do...:-((( I'm such a newbie with macro & excel... Trying to explain what I need...: In a new workboook I need to copy data in columns (I:J) from 4 different sheets and Every exixsting workbook has 4 sheets called "alfa", "beta", "gamma", "delta". I need to copy (in a new sheet of a new workbook) columns I3:J203 of every sheet for every existing workbook So in the new sheet, I've got From Workbook1 I:J columns from "alfa" in A3:B203 I:J columns from "beta" in D3:E203 I:J columns from "gamma" in G3:H203 I:J columns from "delta" in J3:K203 From workboook2 I:J columns from "alfa" in M3:N203 I:J columns from "beta" in P3:Q203 I:J columns from "gamma" in S3:T203 I:J columns from "delta" in V3:W203 ...and so on And, maybe I wish I could put in cell A1,M1... the names of the workbooks and in A2,B2,C2,D2....the names of the sheets.... really hope you can help me... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorry...I'm a terrible idiot and still can't do anything...:-(((
Thank you very very much....:-) it runs and rulezzzz!!!!!!
Tomorrow morning I will work on it trying to be able to use getopenfilename instead of dir() and some other little modifications (pasting values only) in accord to my data, I will try to study from your sute and explanations!!!! "Ron de Bruin" ha scritto nel messaggio ... Oops, Change this This line basebook.Worksheets(1).Cells(1, Colnum).Value = mybook.Name must be below this line Set mybook = Workbooks.Open(FNames) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorry...I'm a terrible idiot and still can't do anything...:-(((
Hi
I will work on it trying to be able to use getopenfilename instead of dir() and some other little modifications (pasting values only) You can find the examples on this page http://www.rondebruin.nl/copy3.htm Try it first and if you need help post back. We will help you -- Regards Ron de Bruin http://www.rondebruin.nl "uriel78" wrote in message ... Thank you very very much....:-) it runs and rulezzzz!!!!!! Tomorrow morning I will work on it trying to be able to use getopenfilename instead of dir() and some other little modifications (pasting values only) in accord to my data, I will try to study from your sute and explanations!!!! "Ron de Bruin" ha scritto nel messaggio ... Oops, Change this This line basebook.Worksheets(1).Cells(1, Colnum).Value = mybook.Name must be below this line Set mybook = Workbooks.Open(FNames) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorry...I'm a terrible idiot and still can't do anything...:-(((
....I tried to substitute the dir() procedure with the
Application.GetOpenFilename as shown in Sub GetData_Example3() http://www.rondebruin.nl/ado. but without any results :-((((((... I tried to change the method of the input to avoid moving files that need to be in some specified folders (as they work together other workbooks). I believe that this is possible...but as the facts show I have too less experience with VBA to get it works... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I am an Idiot Woman | New Users to Excel | |||
I want 148:30 converted to 148.5 and I am an idiot | Excel Discussion (Misc queries) | |||
Terrible Excel Ruined My Chances of Graduation | Excel Discussion (Misc queries) | |||
terrible problem | Excel Discussion (Misc queries) | |||
I made a terrible mistake and need help | Excel Worksheet Functions |