auto copying few rows to another workbook

dharsana

Board Regular
Joined
Sep 7, 2002
Messages
58
hi,
I am working on a workbook where data for a particular week is entered.After the week or whenever I want, I want to transfer to transfer this data to another workbook which is a masterdata workbook.While adding this data the macro should find the empty row and then append the data because the master workbook is having data of previuos weeks.
how to do this with a macro?

thanking you

dharsana
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi this should get you started!!

Kind regards
Jack

|||||||||||||

Option Explicit
Option Base 1


Sub Copy_Data()
Dim stFil As String
Dim stPath As String
DIM stFiltyp As String
‘ Jack sent variable to XLS WkBk’s
DIM stFilArray() As String
Dim wsDataRange As Worksheet
Dim x As Long
DIM i As Long

Application.ScreenUpdating = False
‘ Jack turns off SCREENUPDATING!!

stPath = "c:Test"
‘Jacks WARNING!!!
'Here You will need change to Your #actual directory#

stFiltyp = "*.xls"
‘ Jack selects ONLY “EXCEL” documents
stFil = Dir(stPath & stFiltyp)
‘ Jack pulls dats from XLS WkBk’s in the required DIR to MASTER Document
‘ IE the one this VBA Script is located in.
‘ WARNING DO NOT HAVE THIS DOCUMENT IN THE SAME DIR!!!

Debug.Print stFil

Do Until stFil = ""
i = i + 1
ReDim Preserve stFilArray(i)
stFilArray(i) = stFil
stFil = Dir
Loop
Set wsDataRange = ThisWorkbook.Sheets("Sheet1")
For x = LBound(stFilArray) To UBound(stFilArray)

Workbooks.Open (stPath & stFilArray(x))
Worksheets("Blad1").Range("A1").Copy
wsDataRange.Cells(x, 1).PasteSpecial Paste:=xlValues
Workbooks(stFilArray(x)).Close Savechanges:=False

Next x
Application.ScreenUpdating = True
‘ Jack turns #ON# SCREENUPDATING!!

End Sub
 
Upvote 0
hi jack

seen your reply and thanks.I opened a workbook and created data and named the file and stored in c:.Then I opened master workbook and copied your macro suitably changing the path(c:"workbookname") and excuted the macro and it said syntax error
and showed your statement "jack sent varaibles to xls workbooks" in blue background and when I clicked ok ,the statement sub copy_data() in yellow color
I am not an expert in macro and I could not proceed further.Any suggestions

dharsana
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top