My latest project

[m_in_spain]

Hello again,
My latest project involves opening another workbook (workbook B), getting the worksheet names, copying them to a list on a sheet ("Main") in workbook A, then creating the same worksheets in workbook A. I have managed to sort this out by scratching around on the internet and putting the following together:

Sub Everything()
Dim mySh As Worksheet, Ws As Worksheet
   Dim Wbk As Workbook
   Dim i As Long
   Dim x As Long
'get value from A7 to get workbook B name
   Set Wbk = Workbooks(ThisWorkbook.Sheets("Main").Range("A7").Value)
i = 4   'set at 4 so starts at 5 
   For Each Ws In Wbk.Worksheets
'get sheet names but not these specific names
   If Ws.Visible = xlSheetVisible And Ws.Name <> ("QPriceSheet") And Ws.Name <> ("TotalJobCost") And Ws.Name <> ("Break Out Prices") And Ws.Name <> ("Order Entry") Then
       i = i + 1
      ThisWorkbook.Sheets("Main").Range("C" & i).Value = Ws.Name
      Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = Ws.Name
     End If
     Next
ThisWorkbook.Worksheets("Main").Range("A:A,C:C").EntireColumn.AutoFit 'Columns A & C
End Sub

However, what i want to do is to copy data from the sheet in workbook B from cells A5 to A64 from the specific sheet, to the newly created sheet with the same name in workbook A, then move onto the next worksheet.

I have tried and tried and cannot manage to get this extra information.

As ever, any help is much appreciated.
Thanks

 

[NdNoviceHlp]

Hi m. You only want the range A5 to A64 from the sheets not the whole sheet? Dave

 

[m_in_spain]

Hi m. You only want the range A5 to A64 from the sheets not the whole sheet? Dave

Hi, Well i actually want to copy any data in A5 to A64 (actually A5 to last used cell before A64), i then want C5 to C64 (same, actually C5 to last used cell before C64) but i was thinking i might be able to add the second section once i understood how to do it!
Short answer to question is no, not all the sheet, just A5-A64 and C5-C64 A vould go into column A in WB A, C can go into column B in .
Thaks
M

 

[NdNoviceHlp]

Where goes the data in column A and B? Anything else I should know? Dave

 

[m_in_spain]

Where goes the data in column A and B? Anything else I should know? Dave

Once i get the data into Workbook A, the loop through the remainder of the worksheets continues, untill all the data is copied over. The workbook A will then be saved, and used in a seperate process later.
The whole process is as follows:
From WB A, list all excel files in same folder.
Select which other file to open (WB B)
Look in WB B get list of worksheets.
Make list of sheets in WB B into WB A on Sheet ("Main")
Make same named worksheets in WB A (I got this far)
Popluate correct work sheets in WB A from data in A5-A64, & C5-C64 from WB B (Using Paste Values to get the valuse rather than any sum that may be in the cells)
I do not need any other data from WB B (at the moment!)
Save file
I have other bits of vba code that will then do other work with the data

Thanks for any help!

 

[NdNoviceHlp]

"From WB A, list all excel files in same folder.
Select which other file to open (WB B)"
This is quite a bit different from where U started. How do you select the file that U want to transfer data from? What happens to the old data when U transfer data from another wb to the main wb? Dave

 

[m_in_spain]

"From WB A, list all excel files in same folder.
Select which other file to open (WB B)"
This is quite a bit different from where U started. How do you select the file that U want to transfer data from? What happens to the old data when U transfer data from another wb to the main wb? Dave

I have that in my first question:
the workbook to transfer data from, which i previously opened is determined from:

Set Wbk = Workbooks(ThisWorkbook.Sheets("Main").Range("A7").Value)

The Old data is merely copied, rather than transferred, WB B will remain intack and be closed after i have retrieved all the into

Thanks
M

 

[NdNoviceHlp]

Yes I understand all that. What is the value of listing all of your files if U don't plan on accessing them? It seems like it would be better to code for the possibility that U want to gather info from those other listed wbs at some point in the future. There's not much point in starting until U know where you're going. By the way your code doesn't do anything... U need to open the other wb to get info from it. You can trial this separate sub to list your files (adjust the folderpath to suit)...

Sub LoadFileNames()
Dim LastRow As Integer, Cnt As Integer, FolderName As String
Dim FSO As Object, FLdr As Object, FileNm As Object
'*****change Foldername to your entire folder path
FolderName = ThisWorkbook.Path & "\Datafiles"

With Sheets("Main")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
If LastRow <> 1 Then
Cnt = LastRow + 1
Else
Cnt = LastRow
End If
Set FSO = CreateObject("scripting.filesystemobject")
Set FLdr = FSO.GetFolder(FolderName)
For Each FileNm In FLdr.Files
If FileNm.Name Like "*.xlsm" Then
Sheets("Main").Range("A" & Cnt) = FileNm.Path
Cnt = Cnt + 1
End If
Next FileNm

Set FLdr = Nothing
Set FSO = Nothing
End Sub

Dave
edit: Should have mentioned this list your wbs in sheet Main in "A" not sure that's what U want

 

[m_in_spain]

In a former part of the program, i have previously opened the file i want to access (WB B)
I am trying to make an excel file that will open another excel WB that has lots of info that i do not need to access.
I have another progran which requires the data from A5-64 and C5-64.
Eventually this WB A will be used to fill this harvested data only into yet another WB, but that is some way ahead, and i already know how to do that bit as by then the WB names, and worksheet names are known.
All the data harvested will later be condensed onto one worksheet with all the A & C data on, but i though i should do it step by step. This is just one of the steps. Harventing data from variable workbook on variable worksheets

 

[NdNoviceHlp]

OK I will work on this limited scope solution. Dave