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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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
 

dharsana

Board Regular
Joined
Sep 7, 2002
Messages
58
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
 

Forum statistics

Threads
1,144,050
Messages
5,722,229
Members
422,417
Latest member
Johhny

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
Top