Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

loop thorugh a column and copy to matching workbook

  Asked By: Sean    Date: Aug 19    Category: MS Office    Views: 1248
  

would be very grateful if you could help me with this macro..

I have a worksheet with some entries in column A. These entries match
the names of the files in the folder. What i need is to loop down
this column and copy each entry in column A and paste it into a
specific cell in the matching workbook. (i.e entry = workbookname).

I already have a macro to open all workbooks in the folder and to
copy a range from a specific worksheet to these workbooks. I know how
to make it copy a cell instead of a range of cells but cant do the
loop..

I am including the macro to open all workooks in a folder and copy a
range from a specified book to all these workbooks. (note: I run the
macro from the workbook and not from personal.xls...)

Sub Macro()

Dim sFileName As String
Dim bWkb As Workbook
Dim Wkb As Workbook
Dim Wks As Worksheet
Dim sPath As String
Dim SourceRange As Range
Dim DestRange As Range

sPath = "C:\Documents and Settings\MICKY\Desktop\raja\"
sFileName = Dir(sPath & "*.xls")

Set bWkb = ThisWorkbook

Do While sFileName <> ""

Set Wkb = Workbooks.Open(sPath & sFileName)
Set SourceRange = bWkb.Worksheets("Sheet1").Range("e9:h16")
Set DestRange = Wkb.Worksheets("Sheet1").Range("a1:z25")

SourceRange.Copy destrange


sFileName = Dir
Wkb.Close SaveChanges:=True

Loop
End Sub

I am totally confused on where the for ..next and do while fits in
together...Please help!!!!!!!

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Latoya Murray     Answered On: Aug 19

So if I get this right.. "These entries  match the names  of the files
in the folder" and "entry = workbookname" ..you're looking to put the
workbook's own file name in a specific  cell?

add the line:
Wkb.Worksheets("Sheet1").Range("aa1") = sFileName

OR:
Wkb.Worksheets("Sheet1").Range("ab1") = Wkb.Name

after:
SourceRange.Copy DestRange

Of course, if the list in column  A is only a subset of the workbooks
you are copying ranges to then you might have to check you've got a
match before copying the single cell  across, in which case instead of
the single line above:

FileNamePresent = False
For Each cll In bWkb.Worksheets("Sheet1").Range("A1:A10")
If cll = sFileName Then FileNamePresent = True
Next cll
If FileNamePresent Then Wkb.Worksheets("Sheet1").Range("c3") =
sFileName

(note I've used A1:A10 above, your range  might be different)

 
Answer #2    Answered By: Shobhana R.     Answered On: Aug 19

Excellent! works like a treat.. thanks very much pascal. your help  is
much appreciated.Thank god for people like you life is much easier
for the rest of us!!!!

You are right the primary objective was to put the workbook's name in
the specific  cell!But then i also wanted to try inserting a list into
specific files...

 
Answer #3    Answered By: Carl Woods     Answered On: Aug 19

Is it possible to loop  through three columns at
the same time and copy  the cells  and pasteto another sheet..For
example

Copy A1,B1 and C1 to one sheet, the loop down to A2, b2,c2 copy to
another sheet, down to A3,b3,c3 and copy etc..


I have been trying the code below.. but i know this isnt right as i
have the cll fixed at A1:C1 while it should really be changing as i
go down the loop..


Dim cll As range

set cll As bWkb.Worksheets("Sheet1").Range("A1:C1")

For Each cll In bWkb.Worksheets("Sheet1").Range("A1:C100")

If cll <> "" Then Wkb.Worksheets("Sheet1").Range("c13:e13") =
bWkb.Worksheets("Sheet1").Range("A1:C1")

Next cll

 
Answer #4    Answered By: Adal Fischer     Answered On: Aug 19

Have you tried recording this? The recorded code could give you a big clue I
think.

 
Answer #5    Answered By: Devlan Jones     Answered On: Aug 19

Try using the Offset property. Below is a simple example that copies
3 colums to the same sheet with an offset of 4 columns. Hope it
helps.

Sub Copy3Cols()
For RowOffset = 0 To 100
For ColOffset = 0 To 2
Worksheets("Sheet1").Range("A1") _
.Offset(RowOffset, ColOffset + 4) = _
Worksheets("Sheet1").Range("A1") _
.Offset(RowOffset, ColOffset)
Next
Next
End Sub

 
Answer #6    Answered By: Heru Chalthoum     Answered On: Aug 19

I have been trying this

Sub Macro1()
Dim sFileName As String
Dim bWkb As Workbook
Dim Wkb As Workbook
Dim Wks As Worksheet
Dim sPath As String
sPath = "C:\Documents and Settings\MICKY\Desktop\raja\"
sFileName = Dir(sPath & "*.xls")
Set bWkb = ThisWorkbook
Do While sFileName <> ""
Set Wkb = Workbooks.Open(sPath & sFileName)
For RowOffset = 0 To 2
For ColOffset = 0 To 3
Wkb.Worksheets("Sheet1").Range("A1") _
.Offset(RowOffset, ColOffset + 4) = _
bWkb.Worksheets("Sheet1").Range("A1") _
.Offset(RowOffset, ColOffset)
Next
Next
sFileName = Dir
Wkb.Close SaveChanges:=True
Loop
End Sub

 
Answer #7    Answered By: Murad Bashara     Answered On: Aug 19

i need is to copy  A1 to C1 in the first workbook. Then save and close
it.. open  the second book  and copy A2:C2 in that book..save and close
it.. open the next copy a3:C3 into that etc...

 
Didn't find what you were looking for? Find more on loop thorugh a column and copy to matching workbook Or get search suggestion and latest updates.




Tagged: