Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 31st, 2011, 09:15 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default Loops with multiple lists

Hi

I have created a piece of code which loops througha folder and opens all the files and copies and pastes data fom a range into a certain location. However i need to change it so when it loops the file it checks the names of the file and if has certain text in paste it into different sheets.

I need it to do the following:

If it is "Graphing_MTH_Actual_Curr_Year" & "*.csv" paste data on the MTH Sheet
If it is "Graphing_MTH_Actual_Prev_Year" & "*.csv" paste data on the MTHPrevious Sheet

If it is "Graphing_YTD_Actual_Curr_Year" & "*.csv" paste data on the YTD Sheet
If it is "Graphing_YTD_Actual_Prev_Year" & "*.csv" paste data on the YTDPrevious Sheet

If it is "Graphing_R12_Actual_Curr_Year" & "*.csv" paste data on the R12 Sheet
If it is "Graphing_R12_Actual_Prev_Year" & "*.csv" paste data on the R12Previous Sheet

I have tried doing it with multiple if statements but it just crashes. I would try variables but i am unsure of how to use the.

Does anyone know how to change this loop so it checks the file name on the list above and pastes onto the correct sheet?


Code:
strFldr = "C:\Documents and Settings\SeymourJ\My Documents\Tasks\"
strFile = Dir(strFldr & "Graphing_MTH_Actual_Curr_Year" & "*.csv")
Application.Calculation = xlCalculationManual
lNextrow = 2
If Len(strFile) > 0 Then
Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("Book1Template.xlsx").Sheets("MTH")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("Book1Template.xlsx").Sheets("MTH").Cell s(lNextrow, 2).PasteSpecial

End With

lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile = Dir
Application.StatusBar = strFile
Loop Until Len(strFile) = 0
End IfThanks to anyone who can help.

Jeskit

Last edited by jeskit; April 1st, 2011 at 06:40 AM..





Similar Threads
Thread Thread Starter Forum Replies Last Post
Pasting into spreadsheets IainAL VB How-To 0 April 23rd, 2007 04:33 AM
Copy and Pasting jilly Beginning VB 6 1 March 8th, 2006 09:46 AM
easy pasting problem reverand Excel VBA 17 November 25th, 2005 01:11 AM
Copieng and pasting with VBA vorax187 Access VBA 2 October 13th, 2005 11:58 AM
Pasting information to a second database Brendan Bartley Access 1 September 27th, 2005 09:45 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.